A little refresher please...
I'm trying to make the user choose which sheet to enter data on by
selecting a 1 2 or 3.
In case someone renames or reorders the sheets, I don't want to refer
to those things. I do want to refer to the sheet in the format
Sheet4, Sheet7, or Sheet8.
Most of what I have here works fine until you get to the variable
assignment at the end.
Thanks in advance for what I'm sure will be a small syntax error on my
part.
Rob

Sub whichsheet()
Dim ws As Worksheet
Dim x As Integer, y As Integer
x = InputBox("1 2 or 3")
Select Case x
Case 1
y = 4
Case 2
y = 7
Case 3
y = 8
Case Else
End Select
ws = Sheet y '<--Here is where I get lost... How do I make the "y"
reference Sheet4?
End Sub

Sub picksheet()
n = Application.InputBox(prompt:="select a sheet:", Type:=1)
Sheets(n).Activate
End Sub
--
Gary''s Student - gsnu2007c


"okrob" wrote:

A little refresher please...
I'm trying to make the user choose which sheet to enter data on by
selecting a 1 2 or 3.
In case someone renames or reorders the sheets, I don't want to refer
to those things. I do want to refer to the sheet in the format
Sheet4, Sheet7, or Sheet8.
Most of what I have here works fine until you get to the variable
assignment at the end.
Thanks in advance for what I'm sure will be a small syntax error on my
part.
Rob

Sub whichsheet()
Dim ws As Worksheet
Dim x As Integer, y As Integer
x = InputBox("1 2 or 3")
Select Case x
Case 1
y = 4
Case 2
y = 7
Case 3
y = 8
Case Else
End Select
ws = Sheet y '<--Here is where I get lost... How do I make the "y"
reference Sheet4?
End Sub

Your code seems a little round about... How about this...

Sub whichsheet()
Dim ws As Worksheet
Dim x As Integer, y As Integer
x = InputBox("1 2 or 3")
Select Case x
Case 1
set ws = sheet4
Case 2
set ws = sheet7
Case 3
set ws = sheet8
Case Else
End Select

End Sub

note that the code is referencing the code name of the sheet and not the tab
name... if you want tab name then...

Sub whichsheet()
Dim ws As Worksheet
Dim x As Integer, y As Integer
x = InputBox("1 2 or 3")
Select Case x
Case 1
set ws = sheets("sheet4")
Case 2
set ws = sheets("sheet7")
Case 3
set ws = sheets("sheet8")
Case Else
End Select

End Sub
--
HTH...

Jim Thomlinson


"okrob" wrote:

A little refresher please...
I'm trying to make the user choose which sheet to enter data on by
selecting a 1 2 or 3.
In case someone renames or reorders the sheets, I don't want to refer
to those things. I do want to refer to the sheet in the format
Sheet4, Sheet7, or Sheet8.
Most of what I have here works fine until you get to the variable
assignment at the end.
Thanks in advance for what I'm sure will be a small syntax error on my
part.
Rob

Sub whichsheet()
Dim ws As Worksheet
Dim x As Integer, y As Integer
x = InputBox("1 2 or 3")
Select Case x
Case 1
y = 4
Case 2
y = 7
Case 3
y = 8
Case Else
End Select
ws = Sheet y '<--Here is where I get lost... How do I make the "y"
reference Sheet4?
End Sub