Re: INKEY$ routine

Quote:

---

Originally Posted by ;19486

Why not using the form_keydown event.. eg.

Code:

---

Dim arrStations(3) As String

Private Sub Form_Load()
    arrStations(0) = "Station 1"
    arrStations(1) = "Station 2"
    arrStations(2) = "Station 3"
    arrStations(3) = "Station 4"
End Sub

Private Sub Form_KeyDown(KeyCode as Integer, Shift As Integer)
    Static i As Integer
    If KeyCode = The_Key_You_Want eg. vbKeySpace Then
        lblStation.Caption = arrStations(i)
        i = i + 1
        If i > 3 Then
            MsgBox "You've seen all stations."
            Unload Me
        End If
    End If
End Sub

---

[This message has been edited by Crazy D (edited 02-07-2000).]

---

I have done quite a bit of research on this issue of a routine in VB that does what the Inkey$ function does in GWBasic. This is one of many example I found, however, it does not work on its own. Do I need to declare an API somewhere/somehow? Pease help as I am new at this. I WANT TO USE THIS VB CODE IN EXCEL.

Private Sub Form_KeyPress(KeyAscii As Integer)
Select Case KeyAscii
Case ASC("a")
MsgBox ("a Key Pressed")
Case Else
MsgBox ("Key other than a Key Pressed")
End Select
End Sub

Extracted from 10 year old post and moved to VBA

Welcome to the forums :wave:

In excel there is no event as Form_KeyPress

Use this

Code:

---

Private Sub UserForm_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
    Select Case KeyAscii
        Case Asc("a")
            MsgBox ("a Key Pressed")
        Case Else
            MsgBox ("Key other than a Key Pressed")
    End Select
End Sub

---

i am not even sure if a userform can have focus, if there are controls one of the controls will always have focus and there is no keypreview in userforms as there are in vb6 forms, where your code sample is from

you would need to hook the keyboard using API

No need to use API's :)

Simply set the "TabStop" property of every control to false...

Then controls or no controls the code I gave will work :)

it will work when the form loads, until some /any control gets focus

You are absolutely right Pete but when you are in a textbox then why would you want to trap key "A" ? What if the user wanted to type "Apple" in the textbox. He wouldn't be able to...

When I try to run either of the routines/subs from the MS vb screen in Excel the macros window pops up. See attached macro.jpg . I am a vb dummy so is there anything else I need to set up 1st? Like an API? If so how?

What are you trying to do? I am asking this questions based on post 8...

I have already answered to your question in post1... see post 3

I am using Excel to print name tags one at a time. Since there are over 1000 names in the list, the ListBox to select from is getting tedious. I want to set it up so that as I start typing a name, the list is shortened to include only those names that have the sequence typed in it. Then when I click on a name in the ListBox I want it to print the name tag and reset so I can start again.

Quote:

---

Originally Posted by cjrothman

I am using Excel to print name tags one at a time. Since there are over 1000 names in the list, the ListBox to select from is getting tedious. I want to set it up so that as I start typing a name, the list is shortened to include only those names that have the sequence typed in it. Then when I click on a name in the ListBox I want it to print the name tag and reset so I can start again.

---

Whoa! Then what is your first post all about?

However, here is a link which will help you with the latest query...

http://www.vbforums.com/showpost.php...54&postcount=5

Sorry if my 1st post was misleading. I am new to the forum.

I followed the link. Still not sure how to activate or run the code. If I click on the Run/Play button the Macro window just pops up. Do I have to Call one or more of the sections of code from a regular macro?

Ok, show me your workbook for a faster resolution....

Ok, here is the excel file. It has MS Queries embedded so be sure to Disable the refresh. You will see 3 List boxes in the NewLabels tab. the 1st one is sorted by last name. The 2nd is sorted by 1st name. The 3rd one is limited by the characters typed in cell H12 using the Help macro.
I was wanting to improve on that 3rd option to where the 3rd listbox updates after each keystroke.

Quote:

---

If I click on the Run/Play button the Macro window just pops up. Do I have to Call one or more of the sections of code from a regular macro?

---

you can not run /play macros in userd form events, you must show the userform

of coure both sid and i were assuming from your original post that you were using a userform, but this may not be the case

the code sid linked to must be in the appropriate event for the combo / list box, you can find events for controls using the dropdown lists at the top of the code windows, to make sure the code goes to the correct places
again you can not run the code in the code window, but need to test it by typing into the text area of the listbox
the link is to do what you want in a combobox, so some changes need to be made to use for list box, you will need to find the constant value for lb_selectstring, and you will need to get a hwnd for the listbox as forms2 listboxes do not have a hwnd property, but i remember that sid has posted that before somewhere

Ok, After going through your workbook, I have a few suggestions...

1) Instead of using controls from Forms toolbar, use the listbox from the Control Toolbox. This can be accessed from menu View~~>Toolbar~~>Control toolbox.

2) Instead of using the Cell H12 for input, place a textbox on top of that cell and use that...

3) Once the above is done, use the code from the link below to achieve what you want...
It will be pretty straight and simple :)

http://www.vbforums.com/showthread.php?t=544971

@Pete: I guess you are talking about this. I did manage to get the hwnd of the listbox in the worksheet but after going through the workbook, I feel the above is a much simpler way for OP.

Sid, thanks for taking the time to go through the workbook. OK, so I set a text box from the Control Toolbox in place. Unfortunately the link you provided gave me a "502 Bad Gateway" error. When I get the code, how do I link it with the text box? Must I paste it in the code for the list box. Is this similar to assigning a macro to form based list box? Sorry for what must be very basic questions to you.

Quote:

---

Unfortunately the link you provided gave me a "502 Bad Gateway" error.

---

Refresh that page and you will get the code...

Quote:

---

When I get the code, how do I link it with the text box? Must I paste it in the code for the list box.

---

Simply amend the code to suit your needs and paste it in that sheet's code area.

Quote:

---

Is this similar to assigning a macro to form based list box?

---

No it is not. In fact, the method that I suggested gives you lot of flexibility to tweak the behavior of your controls.

Nevermind, the link did eventually work. Must I use the code or vba code?

Quote:

---

Must I use the code or vba code?

---

Sorry.. You lost me there... I don't understand what you mean?

Sid, the TextBox1_KeyDown seems to be working. This works great to find the position of & highlight the name I am looking for!
How can I populate my list from from all the names in column B of the Full db tab?
Also. it seems that the ListBox1_Click routine is running after every key stroke. Can I set it up to run when I click on a name?

It takes about 20 seconds to populate the list box with 1100 names if I do it one at a time in a loop. Is there a quicker way to do it? Since the list box has to be set up every time the file is opened, this is pretty tedious.
I still have not found out why the ListBox1_Click routine is running after every key stroke. Can I set it up to run when I click on a name?