Combo Box - Hide Combo Box w/Check Box

Combo Box - Hide Combo Box w/Check Box

Post by UGF1b » Fri, 03 Dec 2004 05:07:05


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
 
 
 

Combo Box - Hide Combo Box w/Check Box

Post by Dave Peter » Fri, 03 Dec 2004 11:57:08

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.Combobox.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.





--

Dave Peterson

 
 
 

Combo Box - Hide Combo Box w/Check Box

Post by UGF1b » Sat, 04 Dec 2004 08:19:06

Firstly, thanks for the assistance.

The Combo Box code worked. However, the checkbox code indicates nvalid use
of Me keyword What missing?

Thanks
Paul




>> 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.Combobox.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. >> >> >>
>>>> >>>> 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 >>
 
 
 

Combo Box - Hide Combo Box w/Check Box

Post by Dave Peter » Sat, 04 Dec 2004 12:30:48

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?


>> >>>> 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.Combobox.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. >>>> >>>> >>>>
>>>>>> >>>>>> 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
 
 
 

Combo Box - Hide Combo Box w/Check Box

Post by UGF1b » Sun, 05 Dec 2004 00:41:02

irstly, 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.
I 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:

 
 
 

Combo Box - Hide Combo Box w/Check Box

Post by Dave Peter » Sun, 05 Dec 2004 07:58:41

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:

--

Dave Peterson