Excel 2003 - vba - Test to see if worksheet present

[Craig Brandt]

HI All:

I have this working in one workbook, but when I transplant it into another,
it fails to function as expected.

Issue: I would like to check my current workbook to ensure that a sheet does
not exist, before trying to create it.

In a UserForm, I allow the user to input a sheet name that they would like
to import. Upon their depressing the "AddListBut" on the userform (ListForm)
it checks to ensure the user put something in the Listbox "NewListName" then
check to see if the worksheet already exists.

Public ShtName as String
Private Sub AddListBut_Click()
If ListForm.NewListName = "" Then MsgBox ("Missing ""New List Name"""): Exit
Sub
' Check to see if requested sheet name is already present
ShtName = ListForm.NewListName
On Error Resume Next
Set SH = Worksheets(ShtName)
On Error GoTo 0
If Not SH Is Nothing Then MsgBox ("Sheet Already Exists. "): Exit Sub
' Sheet not there, Continue

..
..
..
..
If the user adds new sheets, everything works correctly. the first time the
user types in a name that already exists, it gives them the " Sheet already
exists" message, but when they go back in and types a new name, it
consistantly informs them that the sheet already exists, even if it is
untrue.

Any ideas?

Craig

 

[Dave Peterson]

I'm betting that SH was declared as a global (or module level) variable. That
means that it doesn't get reset to nothing (or empty or "" or 0 or false
(depending on its declaration type). You have to reinitialize it when you want:


Set SH = nothing '<--added
On Error Resume Next
Set SH = Worksheets(ShtName)

 

[Craig Brandt]

Dave:

You were right and you solved my problem.

Thanks. I don't know what I would do without you guys.

Craig

 

[Dave Peterson]

If you have these kinds of variables in your code, you may want to add some code
that reinitializes the variables (that should be) each time the code runs.

You may find that some number is coming out incorrectly. And debugging the
problem can be difficult since these variables will be reset each time you hit
the Reset button in the VBE.