Application.onkey help with 'enter'key

[Dave Wheeler]

Have working routine with combobox lists which are split into 'beverages of sorts' - sent to textbox and from there put into columns of type/variety in other worksheets
All working fine apart from repeated 'hits' of the command buttons to activate the data movement
Have Excel2003 on XP Pro and have been trying to use the application.onkey sub to try and introduce the 'enter' key to try and make things easier
but......not winning (aaaagh)
code being used just now is

Sub commandbutton1_click()
Sheets("invsales").Activate
Worksheets("invsales").Range("value").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
ActiveCell.Value = TextBox6.Value
End sub
As you see from 'textbox6' - I have a few of these for different beverage types and costs
Would be grateful for any help in introducing an 'ENTER' keystroke into the code somewhere.....to make it that bit more 'easier'
......if you know what I mean
Many thanks as always

 

[Dave Wheeler]

Sorry all....my expanation still seems to be causing confusion
This is basis of current operations :-
Comcobox1 is populated with 'suppliers' names
From 'client list' provided, we see they have purchased 'soft drinks' from "sandys ale shop" - we then highlight "sandys" in combo1, enter value in textbox1, and press button1
This then sends value to selected 'range' in whatever wksheet the information is to be recorded
We wanted to try to accomodate ENTER key into sub.......instead of clicking COMMANDBUTTON
We got as far as:-

sub onkey_setup()
application.onkey "{ENTER}", "commandbutton1_sub"

Didn't work....so we tried "+{ENTER}" for shift key combination
Didn't work either
So we went to webpages but only seemed to come accross confusing 'warnings and examples' of PgUp and PgDn exercises...

We are in Windows XP Pro with Excel 2003, which does not have a textbox property "default" for some reason........
We are somewhat unsure of the TABKEYBEHAVIOR property........
so we have yet to find solution
That's really all we would like to do......hit ENTER key instead of clicking COMMANDBUTTON
All is working ok, otherwise
Never realised this ENTER advice request would be so much trouble
The fnal outcome does indeed have an 'accounts' format which is used in client presentation...which they seem to be pleased with
But any advice is welcome
Thank you all for your interest and caring advice
That is why we put our "message" here
You people are knowledgeable.....and kind to share it with us
Thank you again

 

[Norie]

Dave

Did you not see my suggestion about the Default property?

And the main confusion is probably because of lack of information.

 

[crimson_b1ade]

my suggestion to you would be to setup a short-cut key as follows:

1. Hold Alt + F8 keys.
2. Select the Macro that runs the Command Button you're pressing.
3. Click "Options..."
4. Setup a shortcut key for that macro.

Doing the above should help a bit. As the others have suggested it does sound like your spreadsheet needs a redesign too. Also, have you considered asking your clients to send you this data in a 'soft' copy so you don't have to rekey this information?

Btw, it wouldn't be wise to try to set an ONKEY event for the {Enter} key since you use the {Enter} key for all other sorts of stuff and you wouldn't want to run the command button macro at the wrong time by mistake.

 

[Dave Wheeler]

Sorry.....posted reply but did not go through....
Try again
My explanation is still causing confusion
Sorry
Routine / procedure is as follows:-
We receive client list of items purchased
From list, client has bought 'soft drinks' from 'sandys sales'
From combobox1, (list of suppliers) we highlight 'sandys sales' and input value into textbox1 - we then click 'button1' and value is sent to respective wksheet which records that particular information

We were looking to use ENTER key instead of clicking commandbutton everytime we entered values
Everything else seems to be working to what we need to provide

We tried
sub onkey_setup()
applicatio.onkey "{ENTER}2, "commandbutton1_sub"
Did not work, so we attempted "+{ENTER}" - shift key
Did not work either
We work with Windows XP Pro and Excel 2003, but for some reason do not have 'DEFAULT' property on our 'textboxes' - which apparently can be set at 'false' to operate ENTER key

All our web searches came up with confusing advice "have to use ALT + CTRL" etc etc plus plenty of advice on PgUp and PgDn ONKEY setup
We are confused with the TABKEYBEHAVIOR with MULTILINE
So that's why we came for your expert advice
You people out there are the knowledgeable ones....and kind + helpful with it
But we never imagined the ENTER key would be such trouble
Sorry if our request was confusing
There certainly would be 'accounts' aspects in our final presentation...and yes, there is no doubt room for improvement
and any help will be most appreciated

Client has requested 'complete breakdown' of how much has been spent on 'spirits / wines/ ales etc ( identified expenditure )
and what we provide, in 'ratio' fashion etc, keeps them pleased
Many thanks to all who gave their time and advice..........we will add you to Xmas Card list for 2010

 

[crimson_b1ade]

it's your funeral. Hold Alt+F11 keys, insert a new module, paste the below code. close the vba ide. Hold Alt+F8 keys select the MyKey macro and click 'Run'. Anytime the "Enter" key on the numberpad section of the keyboard is pressed, the HelloWorld macro will run. You can replace the HelloWorld code with whatever you like or you can change the MyKey macro to run another procedure. Enjoy! Happy Easter!

Sub HelloWorld()
 
MsgBox "hello"
 
End Sub

Sub MyKey()
 
Application.OnKey "{Enter}", "HelloWorld"
 
End Sub

 

[Norie]

Dave

I think I'm going to give up here, sorry.

Why do you insist on automating the enter key?

Let's try a little test.

1 Create a userform with a combobox and a command button.

2 Add this code to the userform module.

Option Explicit
 
Private Sub CommandButton1_Click()
If ComboBox1.ListIndex <> -1 Then
MsgBox "You chose " & ComboBox1.Value & " from the list."
Else
If Len(ComboBox1.Value) <> 0 Then
MsgBox "You entered " & ComboBox1.Value & ", which is not in the list."
Else
MsgBox "You chose nothing from the list and entered nothing. "
End If
End If
ComboBox1.SetFocus
End Sub
Private Sub UserForm_Initialize()
ComboBox1.List = Array(1, 2, 3, 4, 5, 6, 7, 8, 9, 10)
CommandButton1.Default = True
End Sub

Now what happens when you press enter?

 

[musoguy]

I think what Dave has been trying to explain is that rather than having to click the Command Button every time he finished entering data into a text box to copy the data into his workbook, he wants to be able to hit the return key which would do the same action. That way he wouldn't have to click the mouse all the time and could keep his fingers on the keyboard. (The same way that when you finish typing data into an InputBox you can hit return instead of clicking the OK button) At least that is what I was searching for a solution to when I came across this thread.

Thank you Norie. Adding the code:

Private Sub UserForm_Initialize()
CommandButton1.Default = True
End Sub

Solved the problem

 

[musoguy]

Dave


Have you considered using a listbox?

Perhaps in combination with a combobox, textbox and command button.

User select makes selection from combobox,textbox etc hit's the command
button (or presses Enter if it's Default property is set to Yes), data added to listbox, other controls cleared...

Then once the user is done they can review what they've chosen in the listbox and with another command button commit it to a worksheet(s).

This could be further enhanced by adding other controls to add/delete/alter etc

If you post back with more specific details and some sample data/results I'm sure we will be able to give more specific help.

Sorry I don't mean to hijack the thread but what Norie wrote above is exactly what I am trying to create right down to the add/delete/alter buttons! I've tried searching for a clue on how to add multiple lines of text that I create from a TextBox one at a time to a listbox, but haven't had any luck. I also don't know where to start with altering/adding or removing the text once it is in the ListBox, and in indeed how to then copy each line to a column in the workbook. So everything really! (I can create a UserForm. That's got to count for something, right?!)
Please let me know if I should have posted this to a new thread. As always, I am in awe of all of you, and I'm trying to learn as quickly as I can, so I can also help people out as opposed to always having to be the one asking the questions!

James

 

[Norie]

James

No harm in adding to this thread - though it might be a little confusing for some people, the original one was for me.

Try starting a new thread and I'll try and take a look at it.

 

[musoguy]

Thanks Norie, I'd really appreciate it if you get a moment. The new thread is here: http://www.mrexcel.com/forum/showthread.php?p=1923381#post1923381

James