Friday, September 23, 2011

VBA Check if Sheet exists

In certain Excel templates, we use a sheet for specifying some Settings. Generally, this sheet is named as SETTINGS.

However, at times you may forget to include the Settings sheet. Here's the macro code to check for the Settings sheet and display a message if it is NOT found.
'Check if Sheet exists
 '=====================
 Dim sh As Worksheet, flg As Boolean
 For Each sh In Worksheets
  If sh.Name Like "Settings*" Then flg = True: Exit For
 Next
 If flg = True Then
  'MsgBox "Found!"
 Else
  MsgBox "Error: Settings Sheet NOT Found! " +chr(13)+chr(13)+"A sheet named SETTINGS must exist in Excel file"+chr(13)+"Terminating application"
  Application.Quit
 End If

No comments: