UserForm_Initialize, how to exit sub based on condition?

[Rick S.]

IO hope I can explain this, I have a userform that will open on a button
click from a worksheet. First thing I am doing is testing the file name to
ensure the user won't overwrite the master file.
I can do that easily enough, the trouble I have is if the condition is true
(they are using the master file) I prompt with a msgbox and then wish to exit
the sub but the userform loads anyhow.
'=======
Private Sub UserForm_Initialize()
'other code removed
If Left(ActiveWorkbook.Name, 12) = "Template DIP" Then
MsgBox "Please do not edit This file! Use ""Save as"" prior to editing!"
End If
End Sub
'=======
No matter what I try I can not stop the userform from loading if the above
condition is true.

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Bob Phillips]

Private Sub UserForm_Initialize()
'other code removed
If Left(ActiveWorkbook.Name, 12) = "Template DIP" Then
MsgBox "Please do not edit This file! Use ""Save as"" prior to editing!"
Unload Me
End If
End Sub

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick S.]

Thanks Bob! I new I tried that, I get an error:
Runtime Error 91
Object variable or With block variable not set"

The error brings me back to this code.
'=======
Sub DipCreatorRun()
Dim i As Variant
Dim xDate As String

xDate = Range("I28").Value
Sheets("Sheet1").Select

Set WB = ActiveWorkbook
For i = 1 To Worksheets.Count
Sheets(i).Activate
Next
If Worksheets.Count <= 1 Then
DipCreatorForm.Show <<<<ERROR DEBUGS TO THIS LINE
Else
MsgBox "This workbook has been previously created on this date: " &
vbNewLine & xDate & vbNewLine & "You can not rerun ""DIP Creator"" on an
existing DIP!"""
End If
Sheets("Sheet1").Select
End Sub
'=======
The above Sub is what starts the entire process from a command button on a
worksheet.

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Bob Phillips]

So it does, you can't unload it presumably because it isn't yet loaded.

Try this variation

Private fExit As Boolean

Private Sub UserForm_Activate()
If fExit Then Unload Me
End Sub

Private Sub UserForm_Initialize()
'other code removed
If Left(ActiveWorkbook.Name, 12) = "Template DIP" Then
MsgBox "Please do not edit This file! Use ""Save as"" prior to
editing!"
fExit = True
End If
End Sub

''your form code

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Rick S.]

I don't fully understand how you did it (setting a True condition), but you
did!
Testing appears to work just fine.
Thank you and have a great Holiday Weekend!

--
Regards

VBA.Noob.Confused
XP Pro
Office 2007

 

[Bob Phillips]

What is happening Rick is that we have a module variable, fExit, which will
default to not being set. In the form Initialize event, we do your test and
set that variable to True if the test succeeds. This variable is then tested
in the Activate event, and if True, unloads the form. This works because the
Activate event fires after the Initialize event, when the form is happily
loaded into memory.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)