Results 1 to 22 of 22

Thread: Re: INKEY$ routine

  1. #1

    Thread Starter
    New Member
    Join Date
    Feb 2010
    Posts
    9

    Post 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

  2. #2
    Super Moderator
    Join Date
    Dec 2003
    Posts
    4,787

    Re: INKEY$ routine

    Extracted from 10 year old post and moved to VBA

  3. #3
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: INKEY$ routine

    Welcome to the forums

    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
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  4. #4
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,449

    Re: INKEY$ routine

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  5. #5
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: INKEY$ routine

    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
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,449

    Re: INKEY$ routine

    it will work when the form loads, until some /any control gets focus
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: INKEY$ routine

    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...
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  8. #8

    Thread Starter
    New Member
    Join Date
    Feb 2010
    Posts
    9

    Re: INKEY$ routine

    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?
    Attached Images Attached Images  

  9. #9
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: INKEY$ routine

    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
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  10. #10

    Thread Starter
    New Member
    Join Date
    Feb 2010
    Posts
    9

    Re: INKEY$ routine

    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.

  11. #11
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: INKEY$ routine

    Quote Originally Posted by cjrothman View Post
    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
    Last edited by Siddharth Rout; Feb 24th, 2010 at 01:40 PM.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  12. #12

    Thread Starter
    New Member
    Join Date
    Feb 2010
    Posts
    9

    Re: INKEY$ routine

    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?

  13. #13
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: INKEY$ routine

    Ok, show me your workbook for a faster resolution....
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  14. #14

    Thread Starter
    New Member
    Join Date
    Feb 2010
    Posts
    9

    Re: INKEY$ routine

    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.
    Attached Files Attached Files

  15. #15
    PowerPoster
    Join Date
    Dec 2004
    Posts
    25,449

    Re: INKEY$ routine

    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
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  16. #16
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: INKEY$ routine

    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.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  17. #17

    Thread Starter
    New Member
    Join Date
    Feb 2010
    Posts
    9

    Re: INKEY$ routine

    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.

  18. #18
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: INKEY$ routine

    Unfortunately the link you provided gave me a "502 Bad Gateway" error.
    Refresh that page and you will get the code...

    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.

    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.
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  19. #19

    Thread Starter
    New Member
    Join Date
    Feb 2010
    Posts
    9

    Re: INKEY$ routine

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

  20. #20
    Super Moderator Siddharth Rout's Avatar
    Join Date
    Feb 2005
    Location
    Mumbai, India
    Posts
    11,998

    Re: INKEY$ routine

    Must I use the code or vba code?
    Sorry.. You lost me there... I don't understand what you mean?
    A good exercise for the Heart is to bend down and help another up...
    Please Mark your Thread "Resolved", if the query is solved

    Microsoft MVP: 2011 - 2015 IMP Links : Acceptable Use Policy, FAQ
    MyGear:
    OMEN by HP - 15-ce073tx with Win10+Office 2013. || Mac Book Pro (10.6.8) with Office 2011

  21. #21

    Thread Starter
    New Member
    Join Date
    Feb 2010
    Posts
    9

    Re: INKEY$ routine

    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?

  22. #22

    Thread Starter
    New Member
    Join Date
    Feb 2010
    Posts
    9

    Re: INKEY$ routine

    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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width