If sheet not created show a message

[Guest]

Hi all

I have a workbook with multiple sheets including one that is generated by
the user (via a button/macro).

I need a code that look for a sheet that "contains" MBA in it's name (it may
contain other words in the sheet name).

If the worksheet is not found then the message "Create MBA first" must
appear, stopping the macro. The user then should click on an OK button, and
fix the problem before re-running the macro.

Help with this would be greatly appreciated.

 

[Fadi Chalouhi]

Here's the code to loop through the sheets to check whether the name
exists or not.
Sub CheckIfSheetNameExists(ByVal strCrit As String)
'
Dim CurSheet As Worksheet
Dim NameExists As Boolean

NameExists = False
For Each CurSheet In Sheets
If InStr(1, CurSheet.Name, strCrit) Then
NameExists = True
End If
Next CurSheet
If Not NameExists Then
MsgBox "Name does not exist. please fix"
Exit Sub
End If
End Sub

here's the code to test the procedure :
Sub test()
CheckIfSheetNameExists ("ExcelPragma.com")
End Sub

HTH

Fadi
www.chalouhis.com/XLBLOG

 

[Bob Phillips]

Why not just create it if it doesn't exist?

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi Bob
I wouldn't ask the question if it was that simple....

The user has to follow a process and they forget to do steps - therefore I
have to build in checks.

They work on the spreadsheet, then create a second worksheet called MBA and
then they run an extraction macro.... They forget to create the MBA and they
get debugging errors - hence my question on how I can incorporate a check and
message to see if they're done it and remind them to do so automatically.

A Big thanks to Fadi for your help.
BeSmart