×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Userform Interaction w/ BeforeSave Event

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

RE: Userform Interaction w/ BeforeSave Event

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

RE: Userform Interaction w/ BeforeSave Event

(OP)
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

RE: Userform Interaction w/ BeforeSave Event

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).

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources