Userform Interaction w/ BeforeSave Event 1

[jproj]

I have an excel spreadsheet in which I would like to restrict the saving of any changes (make it password protected when save or saveas is activated). I have no problem with the code when using an inputbox as the user interface for the password, except that the password is visable when one types. Thus I have created a userform to mimic the inputbox (thereby concealing the password with the "PasswordChar" property).

The problem I have is transfer of information between my userform and the BeforeSave workbook event sub procedure. If the password is incorrect, or if the user clicks "Cancel", I don't want the spreadsheet to be saved. Does anyone know how I can reference the result of my userform activities in my event sub procedure?

Sorry if this is confusing. I'll be glad to clarify if needed. Thanks in advance!!

Best regards,
jproj

 

[cummings54]

define a module level boolean variable in the workbook code pane. Call it "fSaveOK", for example. In the workbook open event procedure set fSaveOK=FALSE. Then in your password form set fSaveOK=TRUE if the password is right. Finally in the BeforeSave event procedure use fSaveOK as follows:

Cancel = Not fSaveOK

 

[jproj]

For some reason it is still not working... here's a simplified version, any ideas what my problem is?

In the Microsoft Excel Objects folder under "ThisWorkbook", I have...

Public Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
UserForm1.Show
Cancel = Not Entry
End Sub

In the Forms folder under "UserForm1", I have...

Public Sub CommandButton1_Click()
Select Case TextBox1.Value
Case Is = "MyPassword" 'Correct entry...
Entry = True
Unload UserForm1
Case Else 'Incorrect entry
Entry = False
Unload UserForm1
End Select
End Sub

I tried Public Entry as Boolean, and Dim Entry as Boolean in the (General) section of both the "ThisWorkbook" and "Forms" code panes. I also created a module a tried it in that code pane, but it didn't work. Am I missing something else?

Thanks for your help!

jproj

 

[cowski]

Try making the Entry variable public in the forms general section then access it like this: UserForm1.Entry in your beforesave event.

If you want a truly global variable I think it has to go in a .bas code module and declare it as global there. Any variables declared in a form's code module are not global because forms (and their related data) can get loaded and unloaded (go out of scope).