Closing Excel when UserForm closed (only if not an admin)

[Adjud]

Hello,

I have an excel document setup for a user to input information into, when the user opens the document, a userform covers the excel document (this is where the information is to be submitted) when the user is finished with said document this person will close the userform, on this happening the userform is to close the document as well.

while this has been acheived, i want it to have a fail safe, so incase i need to edit or view the information stored inside of the document, i want to have a key press tell the program that the user is either an admin or not when closing the userform, if they are an admin the userform closes and the document stays open for editing, if not then it all closes.

Thanks for any help!



This is what i have come up with so far, it seems to just close everything no matter what i pressed... so i removed the Application.Quit function to keep my document from being in an endless loop.


Dim Admin As Integer

Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Application.OnKey "~", "Admin = 1"
End Sub

Private Sub userform_terminate()
If Admin > 0 Then
MsgBox "Welcome Admin"
Stop
Else
MsgBox "You are not Worthy"
ActiveWorkbook.Save
'Application.Quit
End If
End Sub

[ashleyuk1984]

Perhaps make use of

Application.UserName

This will return the username of the user.

You could say something like:

If Application.username = "YOUR NAME" Then 
'Continue doing whatever,
Else
Application.Username <> "YOUR NAME" Then
Msgbox "You are not worthy"
End if

Something along the lines of that.

[Adjud]

I'm not using users in this application, just 1 key press to enable or disable the worthy or not function.

Does the keypress function test actively while a user form is open? when i press any key it seems to affect the textbox's and not the userform... I added a message box after the function to set admin to 1 to see if it would send me a message when i pressed caps lock and it did nothing....

Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Application.OnKey "{CAPSLOCK}", Admin = 1
MsgBox "Hello"
End Sub

not sure what I am doing wrong...

[Adjud]

I fixed the issue with the onkey not responding but the value for Admin won't change to "1" no matter how many times I press "A"
It seems to return the message "No" every time.

Private Sub Submit_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Application.OnKey ("A"), Admin = 1
If Admin = 1 Then
MsgBox "Hello"
End If
If Admin = 0 Then
MsgBox "No"
End If
End Sub

[Jomathr]

Hi!,

Use the break debugging function (F9 key in your code) or you can use msgbox admin as well to see what is in it and it will help a lot to debug in VBA. In your case the variable admin was returning empty

Now about your code, the application.OnKey second argument is to call/run a function that's why it's not assigning 1 to admin since it's not a function

The Keypress method is already trapping the Keys pressed so you don't have to repeat the trapping by using application.onkey

try this:

Private Sub Userform_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim admin As Integer
    If KeyAscii = 97 Then admin = 1
    If admin = 1 Then
        MsgBox "Hello"
    End If
    If admin = 0 Then
        MsgBox "No"
    End If
End Sub

You will have to use the ascii table to use this efficiently http://www.asciitable.com/

Hope it helped!