Combo Box programming

[Neil]

Hi
I have a Combo Box which is populated from another excel file, that works
fine.
What code do I need for;
1) The Combo Box to be visible only when the active cell is in a range ie:
"C4:C100"?
2) Make the linked cell the active cell?

Thanks in advance to anyone who can help.

Neil

 

[Neil]

Hi
Ive added the following code which seems to work OK But!
When I try to copy and paste cells now the copied cells deselect when I
select the cell to paste them into.
Is there a default function that will run the normal code for cells so that
cut and past can continue normally?


Private Sub Combobox1_Click()
ActiveCell.Value = ComboBox1.Value

End Sub

Private Sub ComboBox1_GotFocus()
ActiveCell.Value = ComboBox1.Value
End Sub

Private Sub Worksheet_Activate()

End Sub

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As
Boolean)

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 3 And ActiveCell.Row > 3 Then
ComboBox1.Visible = True
ComboBox1.ListFillRange = "labour.xla!Db"
Else
ComboBox1.Visible = False
End If

End Sub

 

[Dave Peterson]

The bad news is that lots of macros kill the clipboard. Your
worksheet_selectionchange is one of those.

One workaround would be to make that combobox always visible (put it in row 1
and freeze row 1). But only put the value in the activecell if it's in your
range (c4:c65536).

Another workaround might be to provide a macro that asks for the range to copy
and the destination cell. And does the copy|paste in that macro.

(I like the simplicity of the first option.)

 

[Neil]

Thanks Dave
There was a function I think it was in Paradox programming (showing my age
now) that was
DoDefault() that would allow the normal code to execute depending on where
you placed it before or after your added code.
I think I'll work on some code that checks if the combobox is visible so
that the focus doesn't change?

 

[Dave Peterson]

Maybe you can check to see if something is copied or cut:

If Application.CutCopyMode <> False Then
MsgBox "a range is copied" 'some message here???
Exit Sub
End If

When I used this code, the cutcopymode wasn't lost. But the combobox didn't
change visibility.

 

[Neil]

Dave
I've added the following code and that allows cut and paste in all cells
other than the ComboBox target which I don't need copy and paste.
Thanks again for your help.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Column = 3 And ActiveCell.Row > 3 Then
ComboBox1.Visible = True
ComboBox1.ListFillRange = "labour.xls!DB"
Else
If ComboBox1.Visible = False Then
Else
ComboBox1.Visible = False
End If
End If


End Sub