0 votes
in VBA by Beginner (36 points)

Hi everyone! I hope everyone is safe and well.

I have a quick question, and was hoping someone could help with.

I was able to work how how to test if a particular worksheet existed within an active workbook with the following code.

Sub ShExists()
    Dim mySheet As String
    Dim mySheetTest As String
    mySheet = "Smith"
    On Error Resume Next
    mySheetName = Worksheets(mySheet).Name
    If Err.Number = 0 Then
        MsgBox "Sheet exists."
    Else
        Err.Clear
        MsgBox "Sheet missing."
    End If
End Sub

Would it be possible to modify this to check more than the one sheet? I need the macro to check for 5 sheets. It needs to error if any is missing (would be great to know which is missing too but not critical).

Thanks in advance!

ago by Skilled (741 points)
Did you try the answer I provided 8 days ago? Did it work for you?

1 Answer

+1 vote
by Skilled (741 points)

Try this:

Sub ShExists()
    Dim mySheetName As String, msg As String
    Dim sh As Object ' worksheet or chart sheet
    Dim vA As Variant
    Dim n As Integer
    vA = Array("Smith", "Jones", "Doe", "Biden", "Trump")
    msg = ""
    On Error Resume Next
        For n = LBound(vA) To UBound(vA)
            mySheetName = vA(n)
            Set sh = Sheets(mySheetName)
            If Err <> 0 Then
                msg = msg & vbNewLine & mySheetName
                Err.Clear
            End If
        Next n
    On Error GoTo 0
    If msg <> "" Then
        msg = "The following sheets are missing:" & msg
        n = 513 ' 513 to 65535 available for user-defined
        Err.Raise n, , msg
    End If
End Sub

BTW, you might also be interested in My Excel Toolbox.

ago by Beginner (36 points)

Hi JWoolley 

That's perfect!! It worked perfectly! I'm so sorry for the tardy reply. I didn't receive any notification that this had a response.

I have also downloaded the Toolbox and will try and poke about in there.

Thanks again, and please stay safe and well.

Welcome to wellsr Q&A
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
Register
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.

Register

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.

...