ShowDropButtonWhen ActiveX combobox Excel

[drrocket5292]

Does anyone know how to set the showdropbuttonwhen property for an activeX combobox embedded on a worksheet? I was messing around by trying to mimic the activeX combobox properties box and tried:

with combobox1
.showdropbuttonwhen = 1
end with

and it shut down my excel. there's a showdropbuttonwhen property in the properties box but i'm guessing there isn't one in VBA. I have hundreds of saved excel spreadsheets that I'm trying to put a combobox on whenever I reopen them and I don't want to print the dropdownbutton when I print them. I've got the code working to put the combobox on the worksheet but I can't figure out how to not print the button.

 

[ThomasLafferty]

Try selecting the Object and click Format>Control>Properties. Remove the check from Print Object...

Tom

Live once die twice; live twice die once.

 

[drrocket5292]

Tom,
Thanks for the suggestion. Unfortunately my problem needs to be tackled with VBA. There are literally hundreds of these worksheets and to manually change the format of the combobox on each one would be a tedious process. do you know of any way to do this in VBA?

 

[ThomasLafferty]

Hmmm... Do the controls all have the same name and are they all located in the same spot in each book?

If so, you could try creating a workbook and saving it as Control. Then add the following module:

Sub FormatCtrl()
'Open a block of books before running this code.
'Switch to your Control book and run the code.
On Error GoTo Err_FormatCtrl

For Each w In Workbooks
    If w.Name <> ThisWorkbook.Name Then
        w.Activate
        ActiveSheet.Shapes("[!]ComboBox1[/!]").Select
        ActiveSheet.Shapes("[!]ComboBox1[/!]").PrintObject = False
        w.Close savechanges:=True
    End If
Next w
Exit_FormatCtrl:
    Exit Sub
    
Err_FormatCtrl:
    MsgBox Err.Description
    Resume Exit_FormatCtrl
End Sub

Open a block of workbooks (say 10 at a time or so), then return to your control book and run the macro.

Incidentally, this will need some customizing (ie replacing the items in red with the name of your active x control). Also, the syntax for selecting your control may be a bit different. Turn on your macro recorder and select a control, then click format object and remove the check from Print Object then turn off your macro recorder to get the correct syntax.

Hope this helps.

Tom

Live once die twice; live twice die once.