Combo Box - Hide Combo Box w/Check Box

[Paul]

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]

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.