PDA

View Full Version : Workbook Partial Name Match



DeanP
02-18-2019, 10:28 AM
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
02-18-2019, 10:35 AM
At first glance, that bit looks OK

Maybe it's in the other code?

DeanP
02-18-2019, 06:25 PM
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
02-18-2019, 06:41 PM
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
02-18-2019, 09:40 PM
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
02-19-2019, 10:20 AM
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
02-20-2019, 05:31 AM
Ah right, my bad. If it has AddIn in the name then I want to sub to continue not exit.

Paul_Hossler
02-20-2019, 07:37 AM
If you're checking to see if the macro is running from an add in ( .XLAM) then you can use




If ThisworkBook.IsAdd Then

...