0

Can someone please help? I have a feeling that this type of issue is a simple one and has been answered many times but I can't seem to find a solution that works. Getting hit by error 9: subscript out of range.

A good part of the frustration is due to the simplicity of the code that works. I have a spreadsheet with tabs named like "Dist.12345-Store.67890". I have VBA code that will successfully open that tab if I use:

Sub test()
Worksheets("Dist.12345-Store.67890").visible = true
End sub

But rather than hard code this string in the code 31 times in 31 different subs I want to put a string together as a variable storeselect2, and refer to this string as the worksheet to appear. But I get that error 9...

Sub test()

Dim storeselect2 As String

storeselect2 = "Dist." & ActiveCell.Offset(0, -1).Value & "-Store." & ActiveCell.Value
` the storeselect2 is now = "Dist.12345-Store.67890" `

Sheets(storeselect2).Visible = True  `this is where error 9 occurs`

End Sub

How can I get an existing tab to come up when using a string to refer to the tab name in the code? Is it due to the string coming from one tab and trying to open another?

Thanks!!

Community
  • 1
  • 1
user2295535
  • 1
  • 3
  • Did you notice that in your working sample you used `Worksheets` but in the broken one use used `Sheets`? These do different things... – techturtle Apr 18 '13 at 16:10
  • 2
    The code is fine. Error 9 'Subscript out of range' simply means that sheet Dist.123445-Store.67890 was not found in the workbook...make sure the sheet you're trying to show actually exists – KacireeSoftware Apr 18 '13 at 16:13
  • @SiddharthRout: Using debug.print gives me exactly what I expected to see in the variable. I will try the trim steps. – user2295535 Apr 18 '13 at 16:14
  • 2
    If it matters, the sheet names are different between your working and non-working examples. – Tim Williams Apr 18 '13 at 16:15
  • I fixed the sheet name in working example code to match. – user2295535 Apr 18 '13 at 16:19
  • @Kaciree: although you mis-typed the sheet name, I know what you are getting at. However, that sheet is most definitely in the workbook, but hidden, which is why i'm using .visible = true – user2295535 Apr 18 '13 at 16:21
  • thanks, Siddharth, didn't realize: Still not working. and trim() did not work. – user2295535 Apr 18 '13 at 16:23
  • Msgbox also revealed that my name was correct. I even tried _storeselect2 = "Dist.12345-Store.67890"_ because I know that works when hardcoding that reference into _[worksheets().visible]_ but whenever I try _worksheets(storeselect2)_ i get the error 9. – user2295535 Apr 18 '13 at 16:27
  • 2
    Do you have multiple workbooks open? Is it possible another workbook is active when the code is run? `Sheets()` by itself will always refer to the ActiveWorkbook. – Tim Williams Apr 18 '13 at 16:28
  • Good point, mr. williams. I will try with just this workbook open. – user2295535 Apr 18 '13 at 16:31
  • I agree with @TimWilliams...could you modify the code to use this format?: Workbooks("YourWBName").Worksheets(storeselect2).Visible=True – KacireeSoftware Apr 18 '13 at 16:33
  • Nope, it's still a problem even if this is the only workbook open. still not working. – user2295535 Apr 18 '13 at 16:33
  • I tried naming the workbooks as you suggested Kaciree but still not working. – user2295535 Apr 18 '13 at 16:37
  • well, I copied your code, named a sheet 'Dist.12345-Store.67890', hid that sheet, then ran the code...It worked fine. I also added Sheet(storeselect2).activate...It worked too...Using Excel 2007 – KacireeSoftware Apr 18 '13 at 16:38
  • I'm using 2010 but as a .xls spreadsheet. Kaciree, Are you using that code within a module or within the code for that sheet? No matter cuz I can't get it to work either way. – user2295535 Apr 18 '13 at 16:44
  • I just put the code in Thisworkbook to test it. I can't figure why it isn't working for you :/ – KacireeSoftware Apr 18 '13 at 16:50
  • does your sheetname (the actual sheet) have a white space at the end of its name? – Sorceri Apr 18 '13 at 16:54
  • I tested also in an .xls workbook...no problems worked fine – KacireeSoftware Apr 18 '13 at 16:54
  • Ha! I bet @Sorceri nailed it... – KacireeSoftware Apr 18 '13 at 16:56
  • No sorceri, the last character in the actual tab name is '0'. I just did a record macro to confirm that my _sheets().visible = true_ was picking up the correct name and it is for sure "Dist.12345-Store.67890". I even tried this on an excel 2003 PC with same error 9. I also added extra quotes at both ends to be sure that I was getting my variable to represent "Dist.12345-Store.67890". Still not working. – user2295535 Apr 18 '13 at 17:07
  • if you create a new workbook, DO NOT COPY OUT OF OLD, and name the tab Dist.12345-Store.67890 then run the code. Do you still get the error? How did you tab get its name, did you enter it in by hand or do a copy and paste or enter it in some other way? You have some type of hidden character because your code is fine. The issue is with the name of the sheet, IE the sheet. do google search for macro clean hidden characters excel get the macro and run it against the tab names. – Sorceri Apr 18 '13 at 17:15
  • Siddarth, I get 22 characters. Also, to be sure I'm using the exact name as the tab name I'm now just skipping that whole formula and going with _storeselect2 = "Dist.12345-Store.67890"_. Still not working, getting error 9 at _Sheets(storeselect2).visible = true_. – user2295535 Apr 18 '13 at 17:18
  • Sorceri you might be on to something.. I tried a test worksheet named "AAAAA" and was able to get that to work as the storeselect2 string. maybe the hyphen in the name is the issue. Still trying. – user2295535 Apr 18 '13 at 17:27
  • By removing the hyphen from the name I was able to get it to work in 1 instance. I'll revisit if I get snagged later. But for now, working. Thanks for all the help. – user2295535 Apr 18 '13 at 17:32
  • No, that didn't work.. It works in this case _storeselect2 = "Dist.12345 Store.67890"_ but not in this case _storeselect2 = "Dist." & ActiveCell.Offset(0, -1).Value & " Store." & ActiveCell.Value_ even though the strings are identical in immediate window. Still not working. – user2295535 Apr 18 '13 at 17:43
  • OK, I got it to work now (shocked!). It helps if you type the variable word correctly. Thanks again everybody. – user2295535 Apr 18 '13 at 17:58

2 Answers2

0

This what you need to write

 Dim storeselect2 As String
 Dim pig, dog, cat, bird As String
 pig = "Dist."
 dog = ActiveCell.Offset(0, -1).Value
 cat = "-Store."
 bird = ActiveCell.Value
 storeselect2 = pig & dog & cat & bird
 Sheets(storeselect2).Visible = False
john manivong
  • 1
0

For those arriving at this question (from Google?) seeking the straight-forward answer for how to activate a tab by name using a variable string:

Sheets(23).Activate

or

Worksheets(1).Select

or

Sheets("mytabname").Activate

It is not necessary to activate a worksheet in order to manipulate it programmatically, you can also just select it:

Sheets("mytabname").Select

This answer explains that it is not necessary to either select or activate a worksheet before manipulating it. Doing either one slows down the code. However, I (and others) have run into situations where first selecting or activating the sheet we want to manipulate seems to be necessary to avoid an error.

References:

https://www.automateexcel.com/vba/activate-select-sheet/

Excel Select vs Activate

cssyphus
  • 37,875
  • 18
  • 96
  • 111