Searching for worksheet

[Mark]

I am using Excel 97.

Can anyone assist me please?

I am wanting some code which with the use of an input box
searches all the worksheets in a workbook for a sheet
with that value.

A user can enter either a name of a numerical value and
the worksheets are checked, if no worksheet is found then
a new sheet is created with the name of the value of the
input box.

I have something similar in place at the moment but it
doesn't always work correctly as some of the worksheets
have similar names!

Any offers?

Thanks


Mark

 

[pauluk]

mark copy the code into this post so that we can have a look it may just
be simple case of change part of the code,

Save all of us a bit of time

 

[Jeff Standen]

blnExists = False
For a = 1 To Sheets.Count
If Sheets(a).Name = strInputName Then blnExists = True
Next
If blnExists = False Then
Sheets.Add
Sheets(Sheets.Count).Name = strInputName
Else
Sheets(strInputName).Select
End If

Cheers,

Jeff

 

[Jeff Standen]

Not sure if it's clear or not that strInputName is what your InputBox
returns.

Jeff

 

[Guest]

Here's my code:

Sub GoToBadge()

NameNumber = InputBox(prompt:="Enter Name or Number")
On Error GoTo NewName
' this on error causes a fail to find to go to create a
new record
Worksheets(NameNumber).Activate
' this find goes to first empty cell in leave record
FindEmptyCell
Exit Sub

NewName:
On Error GoTo FINISH
If DialogSheets("GetNumDialog").Show Then
EmpType
Newsheet = ActiveSheet.Name
Worksheets(Newsheet).Name = NameNumber
Cells(4, "C").Value = NameNumber
OfficerName = InputBox(prompt:="Enter Name")
Cells(4, 1).Value = FullName
StartDate = InputBox(prompt:="Enter Date Joining,
DD/MM/YYYY")
Cells(2, 2).Value = CDate(StartDate)
Resume
Else
End If
FINISH:
End Sub