Userform Interaction w/ BeforeSave Event
Userform Interaction w/ BeforeSave Event
(OP)
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
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





RE: Userform Interaction w/ BeforeSave Event
Cancel = Not fSaveOK
RE: Userform Interaction w/ BeforeSave Event
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
RE: Userform Interaction w/ 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).