Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
linking a form combo box... results from the combo box to another | Excel Discussion (Misc queries) | |||
FRUSTRATED!!! COMBO BOX AND PROGRAMMING IT | Excel Discussion (Misc queries) | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) | |||
combo Box programming | Excel Programming |