Workbook Partial Name Match

[DeanP]

All I want to do is the check that the workbook contains the words AddIn in it's name. If it does activate it, if not end the sub. I can't get it right. Latest attempt doesn't work.

If wb.Name Like "*AddIn*" Then
        wb.Activate
            Exit Sub
                End If

Error = "End if without block if".


Any help appreciated

[Paul_Hossler]

At first glance, that bit looks OK

Maybe it's in the other code?

[DeanP]

The complete code:

Sub StartFunctional()
Dim wb As Workbook
Dim Answer As VbMsgBoxResult
Dim wsJournal As Worksheet

    With ThisWorkbook
    
    If wb.Name Like "*AddIn*" Then
        wb.Activate
            Exit Sub
                End If
    End With
    
    Set wsJournal = Worksheets("Journal")
    
        With wsJournal
        .Cells(4, 6).NumberFormat = "@"
        End With

    Answer = MsgBox("Existing data will be cleared. Are you sure?", vbYesNo, "Create Journal Template")
        If Answer = vbYes Then
            UserForm1.Show
        End If
End Sub

[Paul_Hossler]

1. You're not Set-ing wb

2. The "With ThisWorkbook" is unnecessary since you use wb.Activate, and not something like just .Activate to default to the With ThisWorkbook

Don't know - all your With's, If's, and End's seem to pair up

Option Explicit

Sub StartFunctional()

    Dim wb As Workbook
    Dim Answer As VbMsgBoxResult
    Dim wsJournal As Worksheet

    With ThisWorkbook
        If .Name Like "*AddIn*" Then
            .Activate
            Exit Sub
        End If
    End With
    
    Set wsJournal = Worksheets("Journal")
    
    With wsJournal
        .Cells(4, 6).NumberFormat = "@"
    End With

    Answer = MsgBox("Existing data will be cleared. Are you sure?", vbYesNo, "Create Journal Template")
    If Answer = vbYes Then
        UserForm1.Show
    End If
End Sub

Maybe some one else can see something

[DeanP]

Thanks! That seems to work, except that it's exposed another problem in that the macro doesn't run even though the workbook name
has <AddIn> in it's name, the procedure does not execute. If I take this piece of code out, then it does.

[Paul_Hossler]

Confused

The macro HAS to run in order to test if "AddIn" is in the workbook's name

If it is, then the sub exits

[DeanP]

Ah right, my bad. If it has AddIn in the name then I want to sub to continue not exit.

[Paul_Hossler]

If you're checking to see if the macro is running from an add in ( .XLAM) then you can use

If ThisworkBook.IsAdd Then

...