Sheets

J

John

Hello,

I have a macro that opens workbooks on demand using vba
code.

I would like to be able to create an error if a particular
worksheet is not in the Excel file. I would like the error
to display a message if the sheet is not in the file. How
can I prepare my if statement to determine if a particular
worksheet is in the file or not?

Also, I would like to do this using vba code.

Thank You,
 
D

Don Guillett

Here is one I use to activate,if open, or open if not. It's from a typed
name in a cell using a double click event.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As
Boolean)
Application.DisplayAlerts = False
Dim WantedSheet As String
WantedSheet = Trim(ActiveCell.Value)
If WantedSheet = "" Then Exit Sub
On Error Resume Next
If Sheets(ActiveCell.Value) Is Nothing Then
GetWorkbook ' calls another macro to do that
Else
Sheets(ActiveCell.Value).Select
ActiveSheet.Range("a4").Select
End If
Application.DisplayAlerts = True
End Sub
 
J

Jason Morin

You can list the sheet name in A1 and use the formula:

=IF(ISERROR(INDIRECT(A1&"!A1")),"Sheet not found","Sheet
found")

HTH
Jason
Atlanta, GA
 
J

Jason Morin

Sorry - this is better:

=IF(ISERROR(INDIRECT("'"&A1&"'!A1")),"Sheet not
found","Sheet found")

Jason
 
Top