ActiveX ComboBox to drive workbook presentation

[MM977]

Hi all,


I would like to use an an ActiveX ComboBox to drive the formatting of the Excel workbook.

Particulars of relevance:
- ComboBox name: ComboBox1
- ComboBox options: Total, Products, Services


What I have thus far:

Sub ComboBox1_Change()

Select Case ComboBox1.Value
Case Total
MsgBox "Total"
Case Products
MsgBox "Products"
Case Services
MsgBox "Services"
End Select

End Sub

However selecting the drop-down options in the ComboBox does absolutely nothing pertaining to the Macro. No errors, nothing. My formulas work as expected but nothing from the Macro.

Any thoughts? I've spent two full days on it now and I really should do some proper work.

 

[MM977]

Forgot to mention the MsgBox rows are there as proxies just while I get it working. They will then be overriden with row hiding, etc.

 

[My Aswer Is This]

Try this:

Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
Case "Total"
MsgBox "Total"
Case "Products"
MsgBox "Products"
Case "Services"
MsgBox "Services"
End Select
End Sub

You needed quotes

 

[MM977]

Try this:

Private Sub ComboBox1_Change()
Select Case ComboBox1.Value
Case "Total"
MsgBox "Total"
Case "Products"
MsgBox "Products"
Case "Services"
MsgBox "Services"
End Select
End Sub

You needed quotes

No, still nothing.

Two questions:
1.) I notice you've made the macro private. I know this hides it from the macro list but are there any other benefits?
2.) I feel as though the ComboBox isn't linking to the macro. Is there a way to verify this? When I say "Select Case ComboBox1.Value" is this the name of the ComboBox or the first ComboBox in the spreadsheet? More often than not they'll be the same but I'd like to confirm.

 

[MM977]

Oh and thanks for your help

 

[MM977]

BINGO!!!!! Solved.

The macro was in only in Module1. When I copied to Sheet1 it worked a treat.

Thanks so much for your help!

 

[My Aswer Is This]

Well if it's a Activex Combobox on your sheet it's always shown as a Private.
Right click on the sheet tab and select view code and you will see the code

Yes Combobox1 is the name of the Combobox

That is the name you had in your script.

Did the script post the Message Box?

I can not have it do something else unless you tell me what you want it to do.
And a change only occurs when you select another value in the combobox not the current one selected.


You said:

I feel as though the ComboBox isn't linking to the macro

What macro?

The message box popping up is the macro
​

 

[MM977]

Yes, it's working perfectly now. Thanks for your help.

This is the current product:

Sub ComboBox1_Change()

Select Case ComboBox1.Value
Case "Total"
Rows("24:35").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Case "-----------"
Rows("24:35").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
Case "Products"
Rows("24:35").Select
Selection.EntireRow.Hidden = True
Range("A1").Select
Case "Services"
Rows("24:35").Select
Selection.EntireRow.Hidden = True
Range("A1").Select
Case "Licensing"
Rows("24:35").Select
Selection.EntireRow.Hidden = False
Range("A1").Select
End Select

End Sub

 

[My Aswer Is This]

Glad to see you have things working:
You could write it like this if you wanted:

Case "Total"
Rows("24:35").Hidden = True: Range("A1").Select

 

[MM977]

Yes that’s a great suggestion.

I didn’t like the fact you could visibly see the rows being selected first. This will eliminate that thereby masking the macro workings from the user.