Combobox Form Control: Select Case Error

[linchits]

Hello,

I am working with a piece of code found here: http://www.mrexcel.com/forum/excel-questions/249841-hide-unhide-rows-selecting-letter-combobox.html.
My version of the code is aimed at hiding certain columns based on a the selection in the combobox. The combobox object was initially added via the gui and modified as follows

Private Sub DropDown42_Change()
  Select Case DropDown42.Value
      Case "Load"
         Range("AB:AN,AP:Ap,aR:AR,AV:AV").EntireColumn.Hidden = True
      Case "order"
        Range("AB:AN,AP:Ap,aR:AR,AV:AV").EntireColumn.Hidden = False
     
        
  End Select
End Sub

This code object sits in ThisWorkbook->Modules->"module1"

Whenever I select a value from the combobox however, I get ""Error 424, Object Required". The Debugger highlights the Select Case statement.

I think the code is fine but, being new to VBA, I might have omitted some initial crucial step. Please help.

Thanks

 

[RoryA]

I suspect you have a Forms dropdown so you need:

Select Case activesheet.dropdowns(application.caller).Value

but the value returned is an index into the source range, not the text of the dropdown so you need to test for that.

 

[nuked]

I'm not entirely clear where your code sits, so you might also have to reference the ranges more specifically:

Activesheet.Range("AB:AN,AP:Ap,aR:AR,AV:AV").EntireColumn.Hidden = TRUE

or

sheets("Your Sheet").Range("AB:AN,AP:Ap,aR:AR,AV:AV").EntireColumn.Hidden = TRUE

 

[linchits]

I suspect you have a Forms dropdown so you need:

Select Case activesheet.dropdowns(application.caller).Value

but the value returned is an index into the source range, not the text of the dropdown so you need to test for that.

changing the Select Case Statement thusly produces error 1004 - unbale to get the dropdowns property of worksheet class.

To clarify, i do have a multi-sheet workbook. The module that contains my code is in VBAProject(something.xlsm)\Modules.

My process in creating the CB ran as follows:
1) Insert Combobox (FormControls) into Sheet 1 add values to it 2)Select this combobox and hit "View Code".
3) Add the "Select Case" statement to code.
4) Error

I can easily invoke macros in the module with the help of buttons. However I cannot get a combobox to initiate something as simple as "hide these columns"

 

[RoryA]

If you do step 1 as you say, there should only be an Assign Macro option in step 2, not View Code. Also, in step 1 how did you add values to it?

 

[linchits]

If you do step 1 as you say, there should only be an Assign Macro option in step 2, not View Code. Also, in step 1 how did you add values to it?

Just tried to assign the same code via "assign macro" - Same error.

Added values under object properties (gui again): Chose a range of 2 cells, giving me a combobox with two values in the drop down.

 

[RoryA]

Let's start at the beginning: which version of Excel are you using?

 

[linchits]

2010

Thanks for the continued help!

 

[linchits]

So how would one create, from scratch, a combobox to hide/unhide columns in a multi-sheet document. I am only interested in hiding/unhiding specific columns in a single sheet.

 

[RoryA]

Assuming a 2 item combobox on the same sheet as the columns you want hidden, add this code:

Sub DropDown_Change()  Select Case ActiveSheet.DropDowns(Application.Caller).Value
      Case 1
         Range("AB:AN,AP:AP,AR:AR,AV:AV").EntireColumn.Hidden = True
      Case 2
        Range("AB:AN,AP:Ap,aR:AR,AV:AV").EntireColumn.Hidden = False
  End Select
End Sub

then add a Forms (not ActiveX) combobox from the developer tab, assign its two items using a range, then assign the above macro to it.