Worksheet name as a variable

[rkbengineer]

Hi,

I'm relatively new to VBA, however I have done a fair bit of Basic & QuickBasic progamming several years ago. I have a problem that I haven't been able to find in a book or any forum.

I have a workbook with 6 worksheets w/ unique names assigned to them. I have a userform that requires several inputs. One input (a TextBox) is a number from 1 to 6. Based on the input, a worksheet should be made active. For example, if a "1" is entered, then the worksheet named "1st" would be activated, "2" for "2nd", etc. My problem is that I don't know how to assign a variable to the worksheet name. I can assign a name from a cell entry, a direct entry or the default, but not from a variable input.

As an afterthought, since there are only 6 possibilities, would it be easier to select from 6 OptionButtons? I might could manage that easier...

Any help would be appreciated. I'm sure I'll have more questions as I go along.

Thanks.

[Norie]

Why do the sheets need to be activated?

[mdmackillop]

Hi RBK,
Welcome to VBAX
Your textbox will work fine.
Try

Private Sub TextBox1_AfterUpdate()
Select Case TextBox1
Case "1"
Sheets("1st").Activate
Case "2"
Sheets("2nd").Activate
Case "3"
Sheets("3rd").Activate
'etc.
End Select
End Sub

[rkbengineer]

Thanks for the quick replies.

Norie, I don't suppose the sheets need to be activated. The remaining data from the userform inputs will be placed into the appropriate worksheet based on the input from the initial Post.

md, I'll give it a try as soon as I get a few spare moments.

Thanks.

[mdmackillop]

You can still use the textbox to assign the sheet names to a variable used to insert the userform input

Dim WS As Worksheet
Private Sub TextBox1_AfterUpdate()
Select Case TextBox1
Case "1"
Set WS = Sheets("1st")
Case "2"
Set WS = Sheets("2nd")
Case "3"
Set WS = Sheets("3rd")
'etc.
End Select
End Sub
Private Sub CommandButton1_Click()
WS.[A1] = TextBox2.Text
End Sub

[Cyberdude]

If you have several macros that need this same kind of conversion, you might want to consider writing a short function to do the job. I have to convert a chart number (1 to 16) as it shows on the worksheet by each chart into an Excel chart number (1436, 901, etc.) so I can use it in VBA statements, so I wrote such a function to be referenced by whatever macro needs it.

I also allow the user to just click on any cell below the chart, then I use a function to determine what the chart number is by asking what range of columns the selected column falls within. The ranges of columns are those covered by the charts. That saves prompting for the chart number.

Just some things to think about.