Worksheet name as a string variable?

[sjh]

Hi,
I tried the following code, but I get <Subscript out of range> error. Is this not possible? I want the sheetName to be dynamically changed depending on what user selected in the combo box cboReports.

Dim sheetName As String
sheetName = cboReports.Value & &quot; Reports&quot;
Sheets(sheetName).Select


Thank you!

 

[SkipVought]

Hi,

Try this...

    ActiveSheet.Name = cboReports.Value & " Reports"

or

    Sheets("Sheet1").Name = cboReports.Value & " Reports"

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[SkipVought]

Also, check out Bowers74 FAQ.

faq707-4090 Refer to Worksheets more effecively in a Procedure



Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[sjh]

Hi Skip,
I think you misunderstood my question.
I wanted my code to open up a worksheet whose name is selected in the combo box cboReports. I hope this makes sense. Thank you~

Private Sub buttonGoReports_Click()
Dim sheetName As String
sheetName = cboReports.Value & &quot; Reports&quot;
Sheets(sheetName).Select
End sub

 

[marklenel]

I'm guessing that you're getting the <Subscript out of range> error because you don't have a worksheet with the name specified in the sheetName variable. You need to

a) check that the options available in the cboReports ComboBox match up with existing spreadsheets
b) check that the ComboBox doesn't allow new entries (.MatchRequired = True)

Hope this helps!

 

[sjh]

Hi there,

It's a silly question, but what is the naming convention for referring to worksheet reference name in VBA code?


Thanks!