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
...
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.