VBA help - Check sht exists if not exit sub

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
863
Office Version
  1. 2010
Platform
  1. Windows
Hi Team,

I am checking sheetname "Main" in Thisworkbook.
if sheetName not found... Macro to throw error MsgBox "Sheet doesn't exist"

But macro is giving SubScript out of Range for below code in Function. on error resume not working
ShtExists = (LCase(wbk.Sheets(ShtName).Name) = LCase(ShtName))



VBA Code:
Option Explicit
Sub testSheedName()

Dim wbk As Workbook
Set wbk = ThisWorkbook

   If Not ShtExists("Main", wbk) Then
      MsgBox "Sheet doesn't exist"
      Exit Sub
   End If
End Sub


Public Function ShtExists(ShtName As String, Optional wbk As Workbook) As Boolean
    If wbk Is Nothing Then Set wbk = ActiveWorkbook
    On Error Resume Next
    ShtExists = (LCase(wbk.Sheets(ShtName).Name) = LCase(ShtName))
    On Error GoTo 0
End Function


Thanks
mg
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,172
Office Version
  1. 365
Platform
  1. Windows
VBA Code:
Sub Test_WorkSheetExists()
  MsgBox "WorksheetExists? " & WorkSheetExists("Sheet1"), _
    vbInformation, "ActiveWorkbook.ActiveSheet"
    
   MsgBox "WorksheetExists? " & WorkSheetExists("ken", "ken.xlsm"), _
    vbInformation
End Sub

 'WorkSheetExists in a workbook:
Function WorkSheetExists(sWorkSheet As String, Optional sWorkbook As String = "") As Boolean
    Dim ws As Worksheet, wb As Workbook
    On Error GoTo notExists
    If sWorkbook = "" Then
      Set wb = ActiveWorkbook
      Else
      Set wb = Workbooks(sWorkbook) 'sWorkbook must be open already.  e.g. ken.xlsm, not x:\ken.xlsm.
    End If
    Set ws = wb.Worksheets(sWorkSheet)
    WorkSheetExists = True
    Exit Function
notExists:
    WorkSheetExists = False
End Function
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
863
Office Version
  1. 2010
Platform
  1. Windows
Hi Ken,

Tested , if matching sheetName found in Thisworkbook or Activeworkbook macro will not throw an error,

if I pass incorrect sheet name to get boolean value, I get subscript out of range, On error resume next not working.



Thanks
mg
 

Kenneth Hobson

Well-known Member
Joined
Feb 6, 2007
Messages
3,172
Office Version
  1. 365
Platform
  1. Windows
In VBE, be sure to select Tools > Options > General > Break on Unhandled Errors
 

Mallesh23

Well-known Member
Joined
Feb 4, 2009
Messages
863
Office Version
  1. 2010
Platform
  1. Windows
Hi Kenn,

Perfect ! thanks a lot its working. (y) 🕺



Thanks
mg
 

Watch MrExcel Video

Forum statistics

Threads
1,129,735
Messages
5,638,057
Members
417,001
Latest member
MSteel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top