Delete a sheet...VB

[katie1071]

Hi,

Need help with an IF statement in VB. I need to check if a Sheet has been deleted before switching to it and processing.

For example, a user could delete a worksheet called out in a macro. If so, the module will fail.

In the code, each sheet is called out by Sheet#.Select. I just want to verify it exists before moving forward.

Thanks,
Katie

 

[pgc01]

Hi Katie

I may be wrong but i don't think there is a property or a method that tests the existence of a worksheet. However you can trap the error of accessing a property, like in this simple example:

Sub WshExists()
Dim sS As String

On Error Resume Next
sS = Worksheets("Crrent Crosstab").Name
If Err Then
    MsgBox "Doesn't exist"
Else
    MsgBox "Exists"
End If
End Sub

Hope this helps
PGC

 

[katie1071]

Thanks, but I should have mentioned the tab name can be changed by the user. Therefore, I need to check by the Sheet #.

 

[katie1071]

Anyone else have any ideas how I can specify a sheet# rather than the name of the sheet? thx

 

[Gary_E]

Try the sheet index number....

in the Project = VBAProject dialog
to access use the module process.

 

[katie1071]

Gary, I don't understand what you said...I'm quite the novice. I record macros & edit

 

[katie1071]

Here is a high level example of how I am calling out the sheets. If sheet6 does not exist because the user deleted it before running the macro, the procedure bombs out.

Sheet6.Select
Cells.Select
Selection.EntireColumn.Hidden = False

Sheet7.Select
Cells.Select
Selection.EntireColumn.Hidden = False

 

[jindon]

katie
try

Sub test()
Dim i As Integer
For i = 1 To Sheets.Count
   MsgBox Sheets(i).CodeName
Next
End Sub

 

[pgc01]

Hi again

Now that you posted your example I don't see why you don't use the error trapping I've posted before.

In your case

On Error Resume Next

....... code for other sheets

Sheet6.Select
If Err.Number = 0 Then
    Cells.Select
    Selection.EntireColumn.Hidden = False
Else
    Err.Clear
End If

Sheet7.Select
If Err.Number = 0 Then
    Cells.Select
    Selection.EntireColumn.Hidden = False
Else
    Err.Clear
End If

......

Hope this helps
PGC

 

[katie1071]

By using the code provided, will this prevent other errors from occuring? Meaning if other code is called between Sheet6 & Sheet7 below, will the errors still be captured?

On Error Resume Next
....... code for other sheets

Sheet6.Select
If Err.Number = 0 Then
Cells.Select
Selection.EntireColumn.Hidden = False
Else
Err.Clear
End If
*****************************other code
Sheet7.Select
If Err.Number = 0 Then
Cells.Select
Selection.EntireColumn.Hidden = False
Else
Err.Clear
End If