Assign variable to a worksheet ?

[MAS]

Hi all,

How do I assign a variable to a worksheet ?

I have a form into which I want to enter a number such as 2467 and when I
click on a command button in that form I want the number "2467" to become
the variable "myclasssheet" to work with the following command.

Workbooks("Toolbox.xls").Worksheets("myclasssheet").Activate

Any help please.

 

[Guest]

You can always assign the Sheet a Name and then use a string variable:


Dim myclasssheet as String
Sheets("Sheet1").Name = "MAS"
myclasssheet = Sheets("Sheet1").Name

and then

Workbooks("Toolbox.xls").Worksheets(myclasssheet).Activate
instead of
Workbooks("Toolbox.xls").Worksheets("MAS").Activate

 

[MAS]

Thanks Gary but I am not explaining myself very well sorry.

I have a workbook with 20 worksheets numbered say 1101 to 1120 and I want to
be able to just enter the worksheet number into a textbox in a form and
display that one sheet when i click on the command button in the form.

I.e. Enter 1115 in the form, click on the command button and sheet 1115 is
displayed on screen.

All the other sheets will be hidden you see.

Mark

 

[Guest]

Well O.K.


Sub gsnu()
Dim ws As Worksheet
s = InputBox("enter sheet number:")
s = "Sheet" & s
For Each ws In Worksheets
If ws.Name = s Then
ws.Activate
Exit Sub
End If
Next
MsgBox ("can't find " & s)
End Sub

The user is asked to supply a number. Say 1234. If Sheet1234 exists, it
will be activated. If Sheet1234 does not exist, an error message is output.