Excel VBA compile error?

Question

I have a workbook with five sheets named A, B, C, and D & E respectively. ‘A’ is the home sheet where I have a button placed which is called ‘btn’. I have assigned a macro called ‘btn_click’ to it. The user will have to mention the name of the sheet on cell ‘B1’ of this ‘A’ sheet. After mentioning the sheet name when the user clicks on the button, the macro will direct the user to that sheet. Something like this:

Dim sheet_name
sheet_name = Range("B1").Value
Sheets(sheet_name).Select

But this VB code is not working and I am not able to navigate to that sheet. It shows a compile error on the third line. Any suggestion will be co-heartedly thanked.

Answer 1

Try



Sheets.item(Sheet_name).select



Also declare sheet_name as a string. Without specifying a datatype it will default to a variant datatype. The item method is expecting a string datatype.

Answer 2

I totally agree with Garbo. Your code is fine. It's good practice to define the variable type and it would be good to define sheet_name as a string, but it should still work.



Your problem sounds like the user is typing in a name in B1 that is not a worksheet in your workbook. Excel then searches for that worksheet and can't find it and returns an error, likely an out of range error like Garbo said. The simplest method to fix your problem would be to use Excel's data validation to ensure that they enter a worksheet name that is in the workbook in cell B1. The problem with having the user type in the name is that if the name isn't typed in exactly then you'll get an error.



When I do something like this, I normally create a combo box then load the combo box with the list of worksheet names. You can do this with the row source property and use a range that has the names. Or you can loop through the worksheets with a for next loop and add them to the list one by one. This is the best method because it grabs the current names and grabs worksheets that have been added, etc. Someone could possibly change a worksheet name or add a new sheet. Then you can use the combo box's list index property to make sure somebody grabs a name from the list. As long as the list index is not -1, that means they grabbed a value from your combo box's list.

Answer 3

Edit: Okay.... just to prove that 'The code snippet appears to be appropriate', I created simple macro:



Sub x()

Dim sheet_name

sheet_name = Range("B1").Value

Sheets(sheet_name).Select

End Sub



Then, I entered 'Sheet3' (no quotes) in cell B1 of Sheet1. Upon calling the macro from Sheet1, guess what? Sheet3 became the active sheet.



Thus, I would infer that if Excel is throwing a 'Subscript out of Range' error, that there is no sheet in the workbook named exactly as the string in B1. If the string in B1 appears to match the string in the sheete Tab, then most likely, there is a 'space' character before/after the value in B1, or before/after the string in the sheet Tab.



================



Are you getting a Compile error or a Run-Time error? The code snippet appears to be appropriate, but without seeing the entire macro it is difficult to see what is causing Excel to cough up a hairball.



As far as dimming a variable. Any variable that is not 'type' dimmed automatically defaults to type 'Variant', which means it will adjust to the required type at run time. The downside to using variant types is that is does slow Excel down a tad. However, in most Excel applications the run time difference is not truly noticeable.

Answer 4

The only thing I can think of is that there might be a syntax error somewhere like a missing punctuation mark. If for some reason it still doesn't work, consider just putting it into a while loop instead and seeing if that works.

Answer 5

my only guess would be a subscript error if the range "b1" is not entered, or an invalid response is entered. You could try using:



on error resume next



to avoid that scenario.

Answer 6

When you Dim a variable, you have to specify what sort of variable it is, so if the value of Range("B1") is a text string, then your statement should say



Dim sheet_name as String