I'm trying to write a macro that will loop a process.

i.e. a text box pops up and asks "What sheet do you want to pull data
from?" and if the name is incorrect, it'll show that name is incorrect
and loops back to the beginning askin what sheet they want to pull the
data from again. If the sheet entered matches, it will go directly to that
sheet in the workbook.

I was thinking maybe it's better to use a SELECT Case and have it evaluate
whether or not the text entered equals any of the sheet names in the workbook.

Any ideas?

Thanks in advance,
Matt

I would advise against the Select Case to deal with this. The problem is
that if you (or anyone else) ever changes a sheet name, adds or deletes a
sheet, whatever, you would need to revise the code. Better would be a For
Each... loop:

Dim ThisSheet as Worksheet, SheetFound as Boolean

SheetFound = False
While Not(SheetFound)
UserInputName = InputBox ("Which sheet?")
For Each ThisSheet in ThisWorkbook.Worksheets
SheetFound = SheetFound Or (ThisSheet.Name = UserInputName)
Next ThisSheet
If Not(SheetFound) Then MsgBox "Not found!",vbExclamation
WEnd



"Matt" wrote:

I'm trying to write a macro that will loop a process.

i.e. a text box pops up and asks "What sheet do you want to pull data
from?" and if the name is incorrect, it'll show that name is incorrect
and loops back to the beginning askin what sheet they want to pull the
data from again. If the sheet entered matches, it will go directly to that
sheet in the workbook.

I was thinking maybe it's better to use a SELECT Case and have it evaluate
whether or not the text entered equals any of the sheet names in the workbook.

Any ideas?

Thanks in advance,
Matt

Simpler is:

Option Compare Text

or

Dim ThisSheet As Worksheet
Dim sh As Worksheet
Dim UserInputName As String

Do
UserInputName = InputBox("Which sheet?")
On Error Resume Next
Set sh = Worksheets(UserInputName)
If sh Is Nothing Then
MsgBox UserInputName & " does not exist, re-try"
End If
Loop Until Not sh Is Nothing
On Error GoTo 0

Worksheets(UserInputName).Activate

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Charlie" wrote in message
...
Simpler is:

Option Compare Text

.
.
.

Dim ThisSheet As Worksheet

Do
UserInputName = InputBox("Which sheet?")
If UserInputName = "" Then Exit Sub
For Each ThisSheet In ThisWorkbook.Worksheets
If ThisSheet.Name = UserInputName Then
ThisSheet.Activate
Exit Sub
End If
Next
Loop


"K Dales" wrote:

I would advise against the Select Case to deal with this. The problem
is
that if you (or anyone else) ever changes a sheet name, adds or deletes
a
sheet, whatever, you would need to revise the code. Better would be a
For
Each... loop:

Dim ThisSheet as Worksheet, SheetFound as Boolean

SheetFound = False
While Not(SheetFound)
UserInputName = InputBox ("Which sheet?")
For Each ThisSheet in ThisWorkbook.Worksheets
SheetFound = SheetFound Or (ThisSheet.Name = UserInputName)
Next ThisSheet
If Not(SheetFound) Then MsgBox "Not found!",vbExclamation
WEnd



"Matt" wrote:

I'm trying to write a macro that will loop a process.

i.e. a text box pops up and asks "What sheet do you want to pull data
from?" and if the name is incorrect, it'll show that name is incorrect
and loops back to the beginning askin what sheet they want to pull the
data from again. If the sheet entered matches, it will go directly to
that
sheet in the workbook.

I was thinking maybe it's better to use a SELECT Case and have it
evaluate
whether or not the text entered equals any of the sheet names in the
workbook.

Any ideas?

Thanks in advance,
Matt

Thanks for your help guys. Appreciate it.

Both ways were helpful.

Matt

"Charlie" wrote:

Simpler is:

Option Compare Text

.
.
.

Dim ThisSheet As Worksheet

Do
UserInputName = InputBox("Which sheet?")
If UserInputName = "" Then Exit Sub
For Each ThisSheet In ThisWorkbook.Worksheets
If ThisSheet.Name = UserInputName Then
ThisSheet.Activate
Exit Sub
End If
Next
Loop


"K Dales" wrote:

I would advise against the Select Case to deal with this. The problem is
that if you (or anyone else) ever changes a sheet name, adds or deletes a
sheet, whatever, you would need to revise the code. Better would be a For
Each... loop:

Dim ThisSheet as Worksheet, SheetFound as Boolean

SheetFound = False
While Not(SheetFound)
UserInputName = InputBox ("Which sheet?")
For Each ThisSheet in ThisWorkbook.Worksheets
SheetFound = SheetFound Or (ThisSheet.Name = UserInputName)
Next ThisSheet
If Not(SheetFound) Then MsgBox "Not found!",vbExclamation
WEnd



"Matt" wrote:

I'm trying to write a macro that will loop a process.

i.e. a text box pops up and asks "What sheet do you want to pull data
from?" and if the name is incorrect, it'll show that name is incorrect
and loops back to the beginning askin what sheet they want to pull the
data from again. If the sheet entered matches, it will go directly to that
sheet in the workbook.

I was thinking maybe it's better to use a SELECT Case and have it evaluate
whether or not the text entered equals any of the sheet names in the workbook.

Any ideas?

Thanks in advance,
Matt