Posts by Jaffey

Re: Check If Active Cell Is In Specified Column

Here is the code (yes I know, the dreaded sendkeys....I'm totally open to alternatives)

Sub AUDIT_FILE()
    If ActiveCell.Column <> 1 Or ActiveCell.Row = 1 Then
         MsgBox "Please select an account in Column A"
         Range("A2").Select
    End If
    'Application.Wait Now + TimeSerial(0, 0, 3) - this didn't help
    ' check password page
    If IsEmpty(Sheets("Credentiales").Range("A1")) Then ufCredentiales.Show
    Open_Audit_File
End Sub


Sub Open_Audit_File()
    'at this point it copies the contents of the active cell the user has selected
    'and passes it to the host in the very last line
    Selection.Copy
    Call Shell("C:\uvterm\uvterm.exe", 1)
    SendKeys "%F~", Wait:=True
    SendKeys "c:\uvterm\uvterm.cfg~", Wait:=True
    SendKeys "%CN~", Wait:=True
    SendKeys Sheets("Credentiales").Range("A1"), Wait:=True
    Application.Wait Now + TimeSerial(0, 0, 1)
    SendKeys "~", Wait:=True
    Application.Wait Now + TimeSerial(0, 0, 1)
    SendKeys Sheets("Credentiales").Range("A2"), Wait:=True
    Application.Wait Now + TimeSerial(0, 0, 1)
    SendKeys "~", Wait:=True
    SendKeys "~", Wait:=True
    SendKeys "~", Wait:=True
    SendKeys Sheets("Credentiales").Range("A3"), Wait:=True
    SendKeys "~", Wait:=True
    SendKeys "5~", Wait:=True
    SendKeys "1~", Wait:=True
    SendKeys "*Q7~", Wait:=True
    SendKeys "^v~", Wait:=True
End Sub

Re: Check If Active Cell Is In Specified Column

I think they are exiting but as I said the rest of the code then fires before the user has had a chance to select their desired cell in column 1. While the message box is displayed the can't select anything and as soon as they click OK on the message box the rest of the code executes. The timer I tried adding doesn't allow the user to change cell selections while it's counting down. I need something to return the focus to the user so they can pick a cell before it proceeds.

Re: Check If Active Cell Is In Specified Column

The message box pops up properly but it doesn't allow the user to select a new cell. It's just moving the active cell to A2 as per the code and then executing the rest of the script. I need it to give the user a few seconds to select a cell. I tried inserting

Application.Wait Now + TimeSerial(0, 0, 3)

after your code but that just gave me 3 seconds of hourglass before proceeding with the rest of the script and I couldn't select a new cell.

I have a macro that is intdended to run after the user has selected a cell in column A:A (any position except A1)
Once the user has selected their desired cell, they press a button and the macro runs.
Occasionally a user will press the button without selecting a cell in the proper column.
I would like a message box to pop up to tell the user "Please select an account in Column A"
and bump the cursor (active cell) to A2 so at least it's in the right column to help get them started. Appreciate any help!

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!

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

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.

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