Prompt For Password, Delete Before Closing

Jaffey · Dec 10th 2007

Hello Oz! This is my first post and I want to start by saying thanks! The length you guys go to help people is AMAZING. I've been using your site for some months now and I've learned so many neat things and found all kinds of great code and I am very grateful. You have saved me god knows how many hours of work already and made some of my coworkers extremely happy with the new tools I've been able to provide. THANK YOU SO MUCH!

Now for my first question;
The following script is attached to a button labelled "Audit" on a worksheet that starts by copying the cell a user has selected (containing an account#), it then calls the emulator(UV/TERM) we use to access our host system and pulls up the account for them. The script runs perfectly (yes I know, the dreaded SENDKEYS but I don't know what else to try...I couldn't find it that list of books you can add in VB editor). Currently I have hardcoded in a single User ID and a series of passwords. What I would like to do is prompt the user to enter their own credentials, then save them so the next time they press the Audit button they are not prompted to enter it again. However, when they exit the spreadsheet no record of their user ID or password should be saved. Note that I am purposely calling a new instance of the emulator each time they press the button, it would be next to impossible to use the same instance since the user will have changed screens.

Code

Sub AUDIT_FILE()


'copy account# for pasting into host system
Selection.Copy


'opens host system
Call Shell("C:\uvterm\uvterm.exe", 1)
SendKeys "%F~", Wait:=True
'select host configuration
SendKeys "c:\uvterm\uvterm.cfg~", Wait:=True
' connect to host
SendKeys "%CN~", Wait:=True
' enter user ID
SendKeys "t.user~", Wait:=True
Application.Wait Now + TimeSerial(0, 0, 1)
'enter password#1
SendKeys "pw1234", Wait:=True
SendKeys "~", Wait:=True
Application.Wait Now + TimeSerial(0, 0, 1)
SendKeys "~", Wait:=True
SendKeys "~", Wait:=True
'enter password#2
SendKeys "pw5678~", Wait:=True
'start navigating to the account lookup screen
SendKeys "5~", Wait:=True
SendKeys "1~", Wait:=True
SendKeys "*Q7~", Wait:=True
'at this point the host is ready to accept the account# for lookup
'the following Sendkeys command is the equivalent of Ctrl-V and pastes in
'the account# copied to the clipboard at the the beginning of the script.
SendKeys "^v~", Wait:=True
End Sub

Display More

Bill Rockenbach · Dec 11th 2007

Re: Prompt For Password, Delete Before Closing

May I suggest inserting a hidden worksheet named "Credentiales". Also create a userform named “ufCredentiales” with three textboxes with labels for ID, Password #1 and Password #2 and an ok button

Put this code in the ufCredentiales code module

Code

Private Sub CommandButton1_Click()
    Sheets("Credentiales").Range("A1") = TextBox1
    Sheets("Credentiales").Range("A2") = TextBox2
    Sheets("Credentiales").Range("A3") = TextBox3
    Unload Me
End Sub

Add this code to same sheet module that your current Audit_File code is in

Code

Sub AUDIT_FILE()
If IsEmpty(Sheets("Credentiales").Range("A1")) Then ufCredentiales.Show
Open_Audit_File
End Sub

Change the name of your current Audit_File procedure to Open_Audit_File and modify it to reference the Credentials worksheet.

Code

Sub Open_Audit_File()


     'copy account# for pasting into host system
    Selection.Copy
     
     'opens host system
    Call Shell("C:\uvterm\uvterm.exe", 1)
    SendKeys "%F~", Wait:=True
     'select host configuration
    SendKeys "c:\uvterm\uvterm.cfg~", Wait:=True
     ' connect to host
    SendKeys "%CN~", Wait:=True
     ' enter user ID
    SendKeys Sheets("Credentiales").Range("A1"), Wait:=True
    Application.Wait Now + TimeSerial(0, 0, 1)
     'enter password#1
    SendKeys Sheets("Credentiales").Range("A2"), Wait:=True
    SendKeys "~", Wait:=True
    Application.Wait Now + TimeSerial(0, 0, 1)
    SendKeys "~", Wait:=True
    SendKeys "~", Wait:=True
     'enter password#2
    SendKeys Sheets("Credentiales").Range("A3"), Wait:=True
     'start navigating to the account  lookup screen
    SendKeys "5~", Wait:=True
    SendKeys "1~", Wait:=True
    SendKeys "*Q7~", Wait:=True
     'at this point the host is ready to accept the account# for lookup
     'the following Sendkeys command is the equivalent of Ctrl-V and pastes in
     'the account# copied to the clipboard at the the beginning of the script.
    SendKeys "^v~", Wait:=True
End Sub

Display More

When the user clicks the “Audit” button on the work sheet it will run the Audit_File procedure and check if the credentials are saved. If they are not saved, it will display the ufCredentials userform. After the user enteres the credentials and the ok button is clicked the userform will save the the credentials in the hidden Credentials worksheet.

Next the Open_User_Form procedure will be called which will use the information stored in the Credentials worksheet.

Place the following code in the Thisworkbook code module. When the workbook closes ii will clear the information in the Credentials worksheet

Code

Private Sub Workbook_BeforeClose(Cancel As Boolean)
 Sheets("Credentiales").Range("A1:A3").Clear
End Sub

[hr]*[/hr] Auto Merged Post;[dl]*[/dl]Set the Password Char property of Textbox1 and Textbox2 to “*” to hide entry of the passwords from view.

nike_5 · Dec 11th 2007

Re: Prompt For Password, Delete Before Closing

Hi Bill the suggestion you gave is really very nice.
A small suggestion on the same.
If you want to have a long term solution , the same thing can be done using a database.
Here each user will have their own user name / password for login and other things would be similar as mentioned above in - using a hidden worksheet - section.

Jaffey · Dec 11th 2007

Re: Prompt For Password, Delete Before Closing

Hello again, I tried moving the code for connecting the textboxes to the cells directly to the the userform instead of putting it on the Credentiales page module and now the script continues when I press the OK button! Unfortunately some new sendkeys timing errors seem to have been introduced (no surprise!) and I'm troubleshooting those now but so far so good! Many thanks Bill!

Code

Private Sub CommandButton1_Click()
    Sheets("Credentiales").Range("A1") = Textbox1
    Sheets("Credentiales").Range("A2") = Textbox2
    Sheets("Credentiales").Range("A3") = Textbox3
    Unload Me
End Sub

Prompt For Password, Delete Before Closing

Participate now!

Share