Disable a userform after first use?

[Bishop]

I have a MASTER spreadsheet that 10 people on the team open. I created a
userform and set it to open when the spreadsheet opens.

Private Sub Workbook_Open()
NotSoFast.Show
End Sub

I was under the impression that the NAME of the spreadsheet is what
triggered the userform. However, even after I change the name of the
spreadsheet it still opens my userform. What I need is for the userform to
come up ONLY when the MASTER spreadsheet is opened. Once the members of the
team "save as..." I don't want it to pop everytime they open their copy. How
can I make this happen?

 

[Rick Rothstein]

The name of the UserForm doesn't change when the workbook name changes, so
your Show shows it. Assuming your MASTER workbook is named "MASTER", then I
think this code should do what you want, assuming your users do not save
their copy of it under the name MASTER...

Private Sub Workbook_Open()
If Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".", , _
vbTextCompare)) = "MASTER" Then NotSoFast.Show
End Sub

 

[Bishop]

Ok, I copy/pasted the code right into my VBE. Saved, closed, reopened. No
userform. So I read up on the components of your code (still a bit over my
head). The actual name of my spread sheet is "Master Tally Sheet" so I
changed your code to

Private Sub Workbook_Open()
'NotSoFast.Show
If Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".", , _
vbTextCompare)) = "MASTER Tally Sheet" Then NotSoFast.Show

End Sub

Saved, closed, reopened. Still no userform. What am I doing wrong?

 

[Harald Staff]

The instrrev function goes a character too far for this, so the dot before
file prefix is included:
= "MASTER Tally Sheet."

I think I'd simplify it to
If ThisWorkbook.Name Like "Master*" Then

HTH. Best wishes Harald

 

[Bishop]

Thanks. That worked!

The instrrev function goes a character too far for this, so the dot before
file prefix is included:
= "MASTER Tally Sheet."

I think I'd simplify it to
If ThisWorkbook.Name Like "Master*" Then

HTH. Best wishes Harald

 

[Rick Rothstein]

Sorry, I actually had a couple of errors in that statement (I made a last
minute change in functions I used and screwed up the change over). Try it
this way and see if it works...

If StrComp(Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1), _
"Mas.ter", vbTextCompare) = 0 Then NotSoFast.Show

 

[Rick Rothstein]

The only problem with what Harald suggested is if your user names his
workbook **anything** starting with the word Master, then the code won't
work as you wanted. For example, if they named it MasterCopy, or Master
Store, or even Mastering, the code would think it had the "MASTER Tally
Sheet".

 

[Rick Rothstein]

Of course, it would have helped if i actually used the name you gave your
master workbook. Try this one instead...

If StrComp(Left(ThisWorkbook.Name, InStrRev(ThisWorkbook.Name, ".") - 1), _
"Master Tally Sheet", vbTextCompare) = 0 Then NotSoFast.Show

 

[Harald Staff]

You're absolutely right, Rick. My "I think I'd.." usually need more thinking
<g>
Anyway, detecting a template for proper action is pretty tricky. I often
want it to behave differently when found on a shared network drive, on a
SharePoint server, as a local copy or as my development master.

Best wishes Harald

 

[Bishop]

That makes sense. The team has a specific file naming protocol they must use
so "Master" will never be in name of their copy. I should've been more clear
about that in my initial question. I saved the code just in case I need it
later though Thanks again.

 

[Dave Peterson]

I'm not quite sure what you're doing, but if you saved your workbook as a
template (*.xlt in xl2003 and earlier), then the users would use it to create a
new workbook--not to open, edit and save as a new name.

Then you can check the path of that file:

Option Explicit
Private Sub Workbook_Open()
if me.path = "" then
'never been saved, so it's just been created
notsofast.show
end if
end sub


Double clicking on a .xlt file in windows explorer should create a new workbook.