Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box - Hide Combo Box w/Check Box
Hi All,
I am using the following code to create a Combo Box. I need to add Hide the Combo Box using a Check Box. Will someone identify the code to hide the Combo Box when the Check Box is clicked? Here is the Combo Box code Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add (ClassType:="Forms.Combobox.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.ListFillRange = "A1:A10" End Sub Sub UserComboBox_Hide() 'Click to hide Combo Box Add hide code here End Sub Thanks Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box - Hide Combo Box w/Check Box
First, I'd give that combobox a name when I added it. Then I could be sure I
was working with the correct one: Option Explicit Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo box.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.Name = "MyCombobox1" oOLE.ListFillRange = "A1:A10" End Sub Then I used a checkbox from the control toolbox toolbar on the same worksheet: Option Explicit Private Sub CheckBox1_Click() Me.OLEObjects("mycombobox1").Visible = Me.CheckBox1.Value 'or Me.OLEObjects("mycombobox1").Visible = Not Me.CheckBox1.Value End Sub This second procedure goes behind the worksheet that owns the combobox/checkbox. Paul wrote: Hi All, I am using the following code to create a Combo Box. I need to add Hide the Combo Box using a Check Box. Will someone identify the code to hide the Combo Box when the Check Box is clicked? Here is the Combo Box code Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add (ClassType:="Forms.Combobox.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.ListFillRange = "A1:A10" End Sub Sub UserComboBox_Hide() 'Click to hide Combo Box Add hide code here End Sub Thanks Paul -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box - Hide Combo Box w/Check Box
Firstly, thanks for the assistance.
The Combo Box code worked. However, the checkbox code indicates €˜Invalid use of Me keyword. What missing? Thanks Paul "Dave Peterson" wrote: First, I'd give that combobox a name when I added it. Then I could be sure I was working with the correct one: Option Explicit Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo box.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.Name = "MyCombobox1" oOLE.ListFillRange = "A1:A10" End Sub Then I used a checkbox from the control toolbox toolbar on the same worksheet: Option Explicit Private Sub CheckBox1_Click() Me.OLEObjects("mycombobox1").Visible = Me.CheckBox1.Value 'or Me.OLEObjects("mycombobox1").Visible = Not Me.CheckBox1.Value End Sub This second procedure goes behind the worksheet that owns the combobox/checkbox. Paul wrote: Hi All, I am using the following code to create a Combo Box. I need to add Hide the Combo Box using a Check Box. Will someone identify the code to hide the Combo Box when the Check Box is clicked? Here is the Combo Box code Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add (ClassType:="Forms.Combobox.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.ListFillRange = "A1:A10" End Sub Sub UserComboBox_Hide() 'Click to hide Combo Box Add hide code here End Sub Thanks Paul -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box - Hide Combo Box w/Check Box
I added a checkbox from the control toolbox toolbar on a worksheet. I double
clicked on that checkbox (while in design mode) and pasted the code into that sheet's code window. The Me. keyword means that the next object (me.oleobjects(...)) belongs to the thing that holds the code--in this case that worksheet. Did you use a checkbox from the control toolbar toolbox? Did you put the code in that worksheet's code window? Paul wrote: Firstly, thanks for the assistance. The Combo Box code worked. However, the checkbox code indicates €˜Invalid use of Me keyword. What missing? Thanks Paul "Dave Peterson" wrote: First, I'd give that combobox a name when I added it. Then I could be sure I was working with the correct one: Option Explicit Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo box.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.Name = "MyCombobox1" oOLE.ListFillRange = "A1:A10" End Sub Then I used a checkbox from the control toolbox toolbar on the same worksheet: Option Explicit Private Sub CheckBox1_Click() Me.OLEObjects("mycombobox1").Visible = Me.CheckBox1.Value 'or Me.OLEObjects("mycombobox1").Visible = Not Me.CheckBox1.Value End Sub This second procedure goes behind the worksheet that owns the combobox/checkbox. Paul wrote: Hi All, I am using the following code to create a Combo Box. I need to add Hide the Combo Box using a Check Box. Will someone identify the code to hide the Combo Box when the Check Box is clicked? Here is the Combo Box code Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add (ClassType:="Forms.Combobox.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.ListFillRange = "A1:A10" End Sub Sub UserComboBox_Hide() 'Click to hide Combo Box Add hide code here End Sub Thanks Paul -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box - Hide Combo Box w/Check Box
Firstly, thanks for the assistance.
I used the Tool Bar Options in Excel to add a check box to MS Excel Book1 Sheet1. I place the check box onto the Worksheet. When I double clicked on the checkbox it returned the Format Control tool, not a sheet code window. Im using a Worksheet, not a Form. Both the Combo Box code and Check Box are contained in a WORKSHEET, not a Form. Thus, the code must reference the worksheet. The Combo box code is function correctly. How do I change the check box code to reference the worksheet and SHOW the combo box when checked? The check box code returns a Compile error: invalid use of Me keyword. Thanks Paul "Dave Peterson" wrote: I added a checkbox from the control toolbox toolbar on a worksheet. I double clicked on that checkbox (while in design mode) and pasted the code into that sheet's code window. The Me. keyword means that the next object (me.oleobjects(...)) belongs to the thing that holds the code--in this case that worksheet. Did you use a checkbox from the control toolbar toolbox? Did you put the code in that worksheet's code window? Paul wrote: Firstly, thanks for the assistance. The Combo Box code worked. However, the checkbox code indicates €˜Invalid use of Me keyword€„¢. What missing? Thanks Paul "Dave Peterson" wrote: First, I'd give that combobox a name when I added it. Then I could be sure I was working with the correct one: Option Explicit Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo box.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.Name = "MyCombobox1" oOLE.ListFillRange = "A1:A10" End Sub Then I used a checkbox from the control toolbox toolbar on the same worksheet: Option Explicit Private Sub CheckBox1_Click() Me.OLEObjects("mycombobox1").Visible = Me.CheckBox1.Value 'or Me.OLEObjects("mycombobox1").Visible = Not Me.CheckBox1.Value End Sub This second procedure goes behind the worksheet that owns the combobox/checkbox. Paul wrote: Hi All, I am using the following code to create a Combo Box. I need to add Hide the Combo Box using a Check Box. Will someone identify the code to hide the Combo Box when the Check Box is clicked? Here is the Combo Box code Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add (ClassType:="Forms.Combobox.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.ListFillRange = "A1:A10" End Sub Sub UserComboBox_Hide() 'Click to hide Combo Box Add hide code here End Sub Thanks Paul -- Dave Peterson -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Combo Box - Hide Combo Box w/Check Box
I think you used the checkbox from the Forms toolbar. You could use different
code with the checkbox from this toolbar, but I think it would be easier to just delete that existing checkbox. Then show that control toolbox toolbar and use the checkbox from there. Then try double clicking on the checkbox and pasting the code in. (I don't see an overwhelming need to mix controls--checkbox from the Forms toolbar, but the combobox from the control toolbox toolbar.) Paul wrote: Firstly, thanks for the assistance. I used the Tool Bar Options in Excel to add a check box to MS Excel Book1 Sheet1. I place the check box onto the Worksheet. When I double clicked on the checkbox it returned the Format Control tool, not a sheet code window. Im using a Worksheet, not a Form. Both the Combo Box code and Check Box are contained in a WORKSHEET, not a Form. Thus, the code must reference the worksheet. The Combo box code is function correctly. How do I change the check box code to reference the worksheet and SHOW the combo box when checked? The check box code returns a Compile error: invalid use of Me keyword. Thanks Paul "Dave Peterson" wrote: I added a checkbox from the control toolbox toolbar on a worksheet. I double clicked on that checkbox (while in design mode) and pasted the code into that sheet's code window. The Me. keyword means that the next object (me.oleobjects(...)) belongs to the thing that holds the code--in this case that worksheet. Did you use a checkbox from the control toolbar toolbox? Did you put the code in that worksheet's code window? Paul wrote: Firstly, thanks for the assistance. The Combo Box code worked. However, the checkbox code indicates €˜Invalid use of Me keyword€„¢. What missing? Thanks Paul "Dave Peterson" wrote: First, I'd give that combobox a name when I added it. Then I could be sure I was working with the correct one: Option Explicit Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo box.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.Name = "MyCombobox1" oOLE.ListFillRange = "A1:A10" End Sub Then I used a checkbox from the control toolbox toolbar on the same worksheet: Option Explicit Private Sub CheckBox1_Click() Me.OLEObjects("mycombobox1").Visible = Me.CheckBox1.Value 'or Me.OLEObjects("mycombobox1").Visible = Not Me.CheckBox1.Value End Sub This second procedure goes behind the worksheet that owns the combobox/checkbox. Paul wrote: Hi All, I am using the following code to create a Combo Box. I need to add Hide the Combo Box using a Check Box. Will someone identify the code to hide the Combo Box when the Check Box is clicked? Here is the Combo Box code Sub CreateComboBox() Dim oWs As Worksheet Dim oOLE As OLEObject Set oWs = ActiveSheet Set oOLE = ActiveSheet.OLEObjects.Add (ClassType:="Forms.Combobox.1", _ Left:=150, Top:=100, Width:=80, Height:=32) oOLE.ListFillRange = "A1:A10" End Sub Sub UserComboBox_Hide() 'Click to hide Combo Box Add hide code here End Sub Thanks Paul -- Dave Peterson -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
hide combo box | Excel Discussion (Misc queries) | |||
Combo/List Box and Check Box | Excel Discussion (Misc queries) | |||
Hide Combo Boxes | Excel Worksheet Functions | |||
Hide my combo boxes! | Excel Discussion (Misc queries) | |||
"Combo Box - getting control combo box to stick in place in worksh | Excel Discussion (Misc queries) |