Combo Box programming
 

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

Combo Box programming
 

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

"Neil" wrote in message
...
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

Combo Box programming
 

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 wrote:

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

"Neil" wrote in message
...
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


--

Dave Peterson

Combo Box programming
 

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" wrote in message
...
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 wrote:

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

"Neil" wrote in message
...
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


--

Dave Peterson

Combo Box programming
 

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 wrote:

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" wrote in message
...
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 wrote:

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

"Neil" wrote in message
...
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


--

Dave Peterson

--

Dave Peterson

Combo Box programming
 

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
"Dave Peterson" wrote in message
...
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 wrote:

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" wrote in message
...
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 wrote:

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

"Neil" wrote in message
...
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


--

Dave Peterson

--

Dave Peterson