Combobox Form Control: Select Case Error

linchits

New Member
Joined
Apr 4, 2013
Messages
5
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

Code:
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
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
I suspect you have a Forms dropdown so you need:
Rich (BB code):
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.
 
Upvote 0
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
 
Upvote 0
I suspect you have a Forms dropdown so you need:
Rich (BB code):
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"
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
Let's start at the beginning: which version of Excel are you using?
 
Upvote 0
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.
 
Upvote 0
Assuming a 2 item combobox on the same sheet as the columns you want hidden, add this code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,529
Messages
6,114,155
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top