Command Buttons - VBA
Posted by Joe on December 08, 2000 9:45 AM
I have command buttons that go to certain worksheets and I want to Error proof them. Is there a VBA code that will check to see if that worksheet exists and if not return a message that it doesn't. This is what I have at this point:
Private Sub CommandButton1_Click()
Dim y As String
Dim x As Object
y = "Item Master"
Set x = ActiveWorkbook.Sheets(y)
If x <> "" Then
Worksheets("Item Master").Activate
Else
MsgBox ("Worsheet Item Master does not exist. Please run macro on Summary Engine worksheet.")
Exit Sub
End If
End Sub
Posted by Ivan Moala on December 08, 2000 11:23 AM
If you have a number of commandbuttons that goto
certain sheets then it would pay to have ONE error
function that you call to check if the sheet exists.
Then you call this function from your commandbuttons.
EG.
Private Sub CommandButton1_Click()
Dim y As String
y = "Item Master"
If SheetExists(y) Then
Worksheets(y).Activate
Else
MsgBox ("Worksheet " & y & " does not exist. Please run macro on Summary Engine worksheet.")
Exit Sub
End If
End Sub
Function SheetExists(ShName As String) As Boolean
Dim Sh As Worksheet
On Error Resume Next
Set Sh = ActiveWorkbook.Sheets(ShName)
If Err <> 0 Then
SheetExists = False
Else
SheetExists = True
End If
On Error GoTo 0
End Function
Ivan
