Solved: keybd_event API missing information on User Form

[chacanger]

Hello

I've recently been trying to create a piece of code within XL VBA to automatically pre-fill a user form with specific information, to do this I have used the keybd_event API to send virtual key commands to the form, it works except it misses a field of information out but does the other fields.

I have made a mock up of what the xl document I am making is and a replica of the xl sheet, which is in the form of an attachment.

Once you've opened up both documents press the proceed button and you'll end up with the form like so...



If your wondering why I'm doing it this way or have an queries I have listed a few Q and A's below to clear up the most likely questions:

Q. Why are you using keybd_event API to enter another user form when it's in the same program?
A. This is because the user form is saved within an old document with passwords so I can't just access the vba code of the form to interact with it.
Q. Doesn’t that mean your hacking the code?
A. No because anyone can use the user form anyway and type what they want into it, the only difference I'm doing is just speeding up the data input that the user would normally type in anyway.
Q. Why don't you just replicate a similar form and print the documents out that way?
A. The printers the user form uses are old 1970/80's printers which are incredibly difficult to code and don't appear in the printers and faxes folder like normal ones. They are special too so resorting to normal printers isn’t an option.
Q. Why are you referring to column letters with ASCII codes?
A. This was because the sheet you see on the workbook I made will not be selected on the final version so referring to Cells didn’t work when I tried it.

If you can get it to work I would be a happy person and be very greatfull.

Thanks

Chacanger

PS. Code can be seen in my xl worksheet.

[GTO]

Greetings Chacanger,

Although I am not exactly following the need for sending keys, my compliments, your question is nicely layed out.

That said, given that the code is interacting with the form in 'Printing Sheet.xls', I cannot imagine that anyone would be willing to run code that they cannot see. You have the project locked in 'Printing Sheet.xls'...

Mark

[chacanger]

Hello GTO

My apologies, the Printing Sheet.xls password is 1, I know it's a terrible password, but it was just to give the impresion it's a code we can't see not that it kept anything confidential.

I had a look into sending keys through the same API before with user forms in VB opposed to VBA and it seems VBA user forms are harder to interact with as you can't interact with the controls, just the window.

Cheers

Adam

[GTO]

Hi Adam,

I've recently been trying to create a piece of code within XL VBA to automatically pre-fill a user form with specific information, to do this I have used the keybd_event API to send virtual key commands to the form, it works except it misses a field of information out but does the other fields.

I test ran your code a couple of times. I am laughably far away from having any expertise with API, but FWIW - I think that as the form's window is reactivated, the first control doesn't really have the focus. Thus, the keys are sent to the great beyond. That's my stab anyways.

Q. Why are you referring to column letters with ASCII codes?
A. This was because the sheet you see on the workbook I made will not be selected on the final version so referring to Cells didn’t work when I tried it.

If you can get it to work I would be a happy person and be very greatfull.

With that, I'm not sure what I've done will help, but I cannot see how it would fail using either the cells' .Value or .Text property, so hopefully worth a try...

Hello GTO

My apologies, the Printing Sheet.xls password is 1, I know it's a terrible password, but it was just to give the impresion it's a code we can't see not that it kept anything confidential.

I had a look into sending keys through the same API before with user forms in VB opposed to VBA and it seems VBA user forms are harder to interact with as you can't interact with the controls, just the window.

Cheers

Adam

I may not be clearly understanding you on this part. True, we cannot easily alter the code programmatically, but we seem to be update the controls' values.

In 'My Worksheet.xls' - in a Standard Module:
[vba]
Option Explicit

Public Declare Function FindWindow Lib "user32" _
Alias "FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As String _
) As Long

Sub RunTest()
Dim hWnd As Long
Dim wb As Workbook
Dim PrintForm As Object

hWnd = FindWindow("ThunderDFrame", "Print Record...")
If hWnd > 0 Then
On Error Resume Next
Set wb = Workbooks("Printing Sheet.xls")
On Error GoTo 0
If wb Is Nothing Then Exit Sub '<maybe include a msg to user

Set PrintForm = Application.Run("'" & wb.FullName & "'!basReference.OBJ_Printform")

If Not PrintForm Is Nothing Then
With ThisWorkbook.Worksheets("MainSheet")
PrintForm.Controls("Account").Value = .Cells(2, "B").Value
PrintForm.Controls("ChqNo").Value = .Cells(2, "C").Value
PrintForm.Controls("Payee").Value = .Cells(2, "D").Text
PrintForm.Controls("Amount").Value = .Cells(2, "E")
PrintForm.Controls("EffDate").Value = .Cells(2, "F")
End With
End If
End If
End Sub
[/vba]

In 'Printing Sheet.xls' - add a Standard Module

Name the module: 'basReference' and add code:

[vba]
Option Explicit

Public Function OBJ_Printform() As UserForm
Dim frm As Object

For Each frm In UserForms
If frm.Name = "PrintFRM" Then
Set OBJ_Printform = PrintFRM
Exit For
End If
Next
End Function
[/vba]

Presuming the form is running, we basically set a late-bound reference to it and update the control values.

You may wish to read:
http://www.tushar-mehta.com/publish_...n_method.shtml

http://support.microsoft.com/kb/555159

Hope that helps,

Mark

[chacanger]

Once again thanks Mark

This is really interesting to know, it's definitley much safer and reliable than VK commands. The issue of the actual version of the file has a password that was applied about 7 years ago, that person who made the file has now gone on to another job and made it impossible to access the code, so I can't add a module to the Printing Sheet.xls in the real version, I would only be able to add the code to the My Worksheet.xls file.

Cheers

Adam

[GTO]

Hi again Adam,

Well shucks, here I thought I might be onto something :-(

In that case, have you tried sending the tab key enough times to rotate back to the first textbox? Spotty at best, but maybe worh a try.

Mark

[Aflatoon]

If this workbook belongs to the company you work at, then presumably they own the IP? If so, why not just remove the password?

[Kenneth Hobs]

As Greg said, you can send more Tab keys. Another method is to send Shift+Tab.

I posted some routines to make it easier using the API method at: http://www.mrexcel.com/forum/showthread.php?p=2872719

The routines and some comments about class code:

[vba]
'--------- Class Name: clsKeys
'zip with class, http://www.freevbcode.com/ShowCode.asp?ID=340
'same but code, http://www.visualbasic.happycodings....us/code38.html
'vk_keys, http://msdn.microsoft.com/en-us/library/ms927178.aspx

Declare Sub keybd_event Lib "user32.dll" _
(ByVal bVk As Byte, ByVal bScan As Byte, ByVal dwFlags As Long, ByVal dwExtraInfo As Long)

Sub Test_keybd_event1()
'F5
Key 116

'Ctrl+T
'KeyPlusKey 17, Asc("T")
'or
KeyPlusChar 17, "T"
End Sub

Sub Test_keybd_event2()
'Send Ctrl+O
KeyPlusChar 17, "O" '11h or 17 - vk_control
'Send Shift+Tab
KeyPlusKey 16, 9 '10h or 16 = vk_Shift, 9 = vk_tab
'Send Down and then Up to set focus to first item in FileOpenDlg()
Key 40 '&H28 = 38, vk_down
Key 38 '&H26 = 40, vk_up
End Sub

' Use this to send key command key plus a command key. e.g. Shift+Tab
Sub KeyPlusKey(str1 As Variant, str2 As Variant)
KeyDown str1
Key str2
KeyUp str1
End Sub

' Use this to send key command plus a key combination. e.g. Ctrl+O
Sub KeyPlusChar(str1 As Variant, str2 As Variant)
KeyDown str1
Keys str2
KeyUp str1
End Sub

' KeyDown() and KeyUp() for each character string in str.
Sub Keys(str As Variant)
Dim i As Integer, s As String, j As Integer
For i = 1 To Len(str)
s = Mid(str, i, 1)
For j = 1 To 330
'Debug.Print j, Asc(s) - j
Next j
If Val(s) = 0 Then s = Asc(s)
DoEvents
Key Val(s)
Next i
End Sub

' Release a key
Sub KeyUp(str As Variant)
keybd_event str, &H9D, 2, 0
End Sub

' Press a key
Sub KeyDown(str As Variant)
keybd_event str, &H9D, 0, 0
End Sub

' Press and release a key
Sub Key(str As Variant)
KeyDown str
KeyUp str
End Sub[/vba]

[chacanger]

Thanks guys for all your help, I will keep trying to get a work around on the key commands, it's not the most ideal way but it will have to do.