"Object required" error in combo box/macro combination

**chemeng1T3** · 07-05-2012, 06:50 AM

Hi,

I'm trying to set up a command button that executes an action based on the combo box selection. The command button runs a plotting macro that is always the same, however, the combo box is used to select the worksheet which provides the data the plotting macro will apply to. All the data worksheets are laid out the same.

The macro is set up so the data plotted is from the ActiveSheet, which is referenced to by the string strSheetName. I was trying to use the combobox selection to set strSheetName equal to the worksheet chosen, and then activate this worksheet before running the plotting macro.

My problem is that I keep getting a compilation error: object required. The error shows up first in the line Set strSheetName As String. I tried initializing strSheetName as Public or Global, but Public had the same problem and Global wouldn't work for the string...this combo box code is in sheet20, whereas the macro is in a module (i hope that is okay).

Here's my code so far:

Formula:

Please Login or Register  to view this content.

Any help is greatly appreciated!

Dan

**smuzoen** · 07-05-2012, 06:54 AM

Please Login or Register  to view this content.

Don't dimension worksheets as strings. As well at the end of the macro

Please Login or Register  to view this content.

**chemeng1T3** · 07-05-2012, 07:10 AM

Thanks for the help, the code now gets passed the Object Required error. Unfortunately it gets caught up in a Runtime Error: Type mismatch at

Formula:

Please Login or Register  to view this content.

Any suggestions?

Cheers

**chemeng1T3** · 07-05-2012, 07:18 AM

Do I reference strSheetName differently if it's a Worksheet rather than a string (noobie)?

**smuzoen** · 07-05-2012, 07:23 AM

Sorry missed that one - should be

Please Login or Register  to view this content.

**smuzoen** · 07-05-2012, 07:34 AM

Do I reference strSheetName differently if it's a Worksheet rather than a string (noobie)?

That is right. If you want to make coding easier (and it is good programming practice) then you can dimension worksheets and refer to them more easily.

Please Login or Register  to view this content.

Here you dimension the variable wsK as a worksheet and then you use the Set command to set the worksheet value. So the code above will set wsK to the worksheet named Results and select it.
If you want you can use strings

Please Login or Register  to view this content.

Here the variable shName is equal to "Results" - then you can select the worksheet by using the string name to decide which sheet to show.
If you are new to programming VBA you should have a read about dimensioning variables - there are some links in the Sticky on the first page of the forum http://www.excelforum.com/excel-prog...materials.html
This is also a good link when starting out about the different types of variables you can dimension - http://www.ozgrid.com/VBA/variables.htm
PS: Just so there is no confusion strSheetName is NOT a Keyword i.e. a VBA function/programming code - it is just a made up variable - it is in the form of what we call Camel Case and it is trying to be descriptive - it is good to use variable names that describe the type of data they might contain to make reading code easier - strSheetName - with Camel case the first letter is lower case then the next part of the variable name (Sheet) is capitalised and so is next part of variable name (Name) - strSheetName - it is just a variable that has been made up trying to show that the variable probably refers to a worksheet. It could have just as easily have been mySheetNameAsString - you can make the variable name anything you like however some Keywords are reserved - for example you could not use Set = 6 - Set is a keyword and cannot be used as a variable.

**chemeng1T3** · 07-06-2012, 05:29 AM

Thanks, after changing that I was getting a runtime error "object variable or runtime variable not set". And the error occurred at:

Formula:

Please Login or Register  to view this content.

at the bottom of all my IF/End if statements. Originally, I was going to call a macro in a module from this combo box code (which is in a sheet as a Microsoft Excel Object), but I tried just putting the macro in the bottom of the code as shown below. This doesn't work - nothing happens. When I go through line by line, there are no error messages, but it doesn't do anything. I'm wondering if the SheetName variable is not setting to a worksheet correctly, or if there's a variable types problem.

Formula:

Please Login or Register  to view this content.

If you have any more thoughts as to why either of these problems are occurring please send them along!

Thanks again,

Dan

**chemeng1T3** · 07-06-2012, 05:40 AM

Okay, above nothing was happening because of a stupid mistake:

I had Private Sub ComboBox1_Click()

instead of Private Sub CommandButton1_Click()

After the change, I click the button and it goes to the worksheet selected from the drop down menu of the combobox, but no graphs are plotted

**JosephP** · 07-06-2012, 07:23 AM

I reckon

Please Login or Register  to view this content.

**chemeng1T3** · 07-09-2012, 10:32 AM

Thanks for the code JP, I actually managed to figure out my mistake, but yours looks quite similar (I didn't test it though).

The problem was that "SheetName" is a variable that represents a worksheet and isn't a worksheet. So while i have to reference my worksheets by writing "Worksheets("FB1").Range...." I have to write "SheetName.Range..." when referencing the variable.

Thanks for all the help