vba code to hide a drop down box

[andrewb90]

hi,

Can somebody help me figure out what the correct vba is to hide a drop down box? I am running a macro that hides particular cells, but I can't seem to hide the drop downs.

 

[riddenstudios]

Is it a drop down box, as in, pick a cell, right click, pick from dropdown. Or
Linked cell combobox? If combo box is it form or active X control?

 

[andrewb90]

It is a Linked Cell Combo box. It is a form, not Active X.

 

[LEE355]

Hi andrewb90,

Right-click on the dropdown and look at the name box up by the formula bar to see the dropdown name.

Sub HideCombo()
Dim ws As Worksheet
Set ws = Worksheets("Sheets1") 'Change name to suit you.
With ws.DropDowns("Drop Down 1") 'Change name to suit you.
.Visible = False
End With
End Sub

Sub ShowCombo()
  Dim ws As Worksheet
  Set ws = Worksheets(1)
  With ws.DropDowns(("Drop Down 1")
    .Visible = True
  End With
End Sub

You can run the following subs to get the name or index property of a particular dropdown. 

[Code]Sub GetDropDownName()
Dim ws As Worksheet
Set ws = Worksheets(1)
MsgBox ws.DropDowns(1).Name

Sub GetDropDownIndexNumber()
Dim ws As Worksheet
Set ws = Worksheets(1)
MsgBox ws.DropDowns("Drop Down 1").Index
End Sub

 

[LEE355]

Properly formatted:

Sub HideCombo()
  Dim ws As Worksheet
  Set ws = Worksheets("Sheets1") 'Change name to suit you.
    With ws.DropDowns("Drop Down 1") 'Change name to suit you.
      .Visible = False
    End With
End Sub

Sub ShowCombo()
  Dim ws As Worksheet
  Set ws = Worksheets(1)
  With ws.DropDowns(("Drop Down 1")
    .Visible = True
  End With
End Sub

Sub GetDropDownName()
  Dim ws As Worksheet
  Set ws = Worksheets(1)
  MsgBox ws.DropDowns(1).Name
End Sub

Sub GetDropDownIndexNumber()
  Dim ws As Worksheet
  Set ws = Worksheets(1)
  MsgBox ws.DropDowns("Drop Down 1").Index
End Sub

 

[riddenstudios]

Will the combobox.visible=false work? Of course use your combobox name instead of combobox. The .visble is actually a property of the combobox. This won't make it not be there...you just won't beable to see it.

 

[riddenstudios]

Oops I realized once it was too late I should have broken that up for you better sorry. Here use this:

combobox.visible=false

This will not delete your combobox it will still be there. You will not be able to see it. In fact it's one of your properties for your combobox. Go to properties, the alphabetical order tab, look under visible. All your doing is changing this property.

 

[LEE355]

Hi,
This code works just fine. I just started with a new workbook and added two CommandButtons and one ComboBox
from the forms Toolbar.

First you write the subs and then right-click one CommandButtton and click Assign macro
and choose the HideCombo procedure to that button.

Then do the same to the other button but assign the ShowCombo procedure to this button.
Works perfect every time. It took me less than five minutes to set the whole thing up.

It seems like the DropDown doesn't have a .Visible property in the Object Browser. But it does have a .Visible
property; its just hidden.

Type "dropdown" in the search box, click the binoculars and then right-click anywhere in the browser and select
Show Hidden Members. The first line that shows under Library is the Excel library and DropDown as a class
of its own.

Scroll down in the "Members of DropDown" pane and you'll see a Visible property.

I don't know why Excel hides certain Hidden Members in the browser but there's a lot that can be learned by viewing
these hidden members.

I don't understand this part of your post:
"This won't make it not be there...you just won't be able to see it."

The OP asked about hiding the dropdown, not cutting or deleting it.
Hope this clears everthing up. Have a nice day.

 

[andrewb90]

Hi Lee355,

Is there anyway to incorportate that into my existing code? I am using a toggle code to hide/unhide and would like to have the boxes do the same.

Sub comments1()    Rows("179:185").Hidden = Not Rows("179:185").Hidden
End Sub

 

[Jerry Sullivan]

Hi Andrew,

Lee was offline for a while and might have missed your follow up question.

If it's still useful to you, try this code which combines Lee's macro with yours to toggle the visibility of both rows and the combobox.

Sub comments1()
    
  With Worksheets(1)
    With .Rows("179:185")
        .Hidden = Not .Hidden
    End With

    With .DropDowns("Drop Down 1")
        .Visible = Not .Visible
    End With
  End With
End Sub