Combo Box - Hide Combo Box w/Check Box

[Guest]

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.

 

[Guest]

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]

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?

 

[Guest]

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.
I’m 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]

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

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.
I’m 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