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

 

[westconn1]

i am not quite sure what you want to do, but you can use the keypress event of textbox to trap for enterkey

are these in a form or sheet?

 

[crimson_b1ade]

like westconn1 mentioned, it is hard to understand what you are asking to do, but it seems to me that you would want to use SENDKEYS rather than ONKEY.

SendKeys "{ENTER}"

 

[crimson_b1ade]

and another thing. rather than looping to find the last row in a column with data, you can find it also like so:

LastRow = Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Row

and you'll use it like so...

Sheets("Sheet1").Range("A" & LastRow + 1) = TextBox6.Value

 

[Dave Wheeler]

Thanks 'Westconn'
It's all on worksheet
We are trying to keep track of 'how much spent' on 'xxx supplier' for its 'yyy vodka' etc
By a series of combo + textboxes and buttons, we select manufacturer from Combo1 - put cost in Textbox1....and send this to range of columns per 'brand type' in next few pages
We then have cost weekly/monthly of brand 'xxx vodka' etc
It's working fine apart from labourious return to button to click after each value added to textbox.........as you can imagine
So would like to use {ENTER} key to simplify things
But don't know where to add APPLICATION.ONKEY instructions (if this is correct).....
or how to put the instruction detail into sensible VBA
Have followed your instruction but can only find TABKEYBEHAVIOR in properties for TEXTBOX
Sorry....but haven't a clue what to do with it
Have been searching HELP + F1 for advice....but nothing found
Anything you suggest will be gratefully received

 

[Norie]

Dave

Do not, I repeat do not try and use a Sendkeys and probably not the other key events suggested either.

Like the rest of the members I'm a little confused as to what you actually want to do.

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 committ 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.

 

[westconn1]

there should also be an enterkeybehaviour property

<title>EnterKeyBehavior Property</title><style>@import url(/Office.css);</style><link disabled="" href="/MSOffice.css" type="text/css" rel="stylesheet">The EnterKeyBehavior and MultiLine properties are closely related. The values described above only apply if MultiLine is True. If MultiLine is False, pressing ENTER always moves the focus to the next control in the tab order regardless of the value of EnterKeyBehavior.

but i still don't know where you want the enter key to take you
but you can also use textbox7.activate, to move to some other specific textbox, or other control, if that helps at all

 

[Dave Wheeler]

Thanks to all for comments

Maybe if I've not explained the function and the reason behind the routine that we are using
All our actions are more are less covered by the code I've given
We get 'very long' invoice/packing lists from our clients, with lots and lots of items which we need to thoroughly examine and enter all relevant items that we need to record..... all this for 'stockturn' exercises and 'ratios'
We did originally do this exercise - as one would expect - lots of worksheets with columns assigned to just about everything you can think of in the 'Hotel/Public House line of business
Trouble was jumping about and searching page headings for the correct column, then lining up active cell for value input......
So , to make ii 'seem' easier, we now have CONTROL worksheet with column of comboboxes containing all you would expect to find - suppliers of spirits / wines / ales / juices / soft drinks / crisps / nuts etc etc
So we search combo1 / 2 / 3 etc for ware type - goto textbox assigned to combo - then insert value of purchase
Then, by the click of the CommandButton assigned to the 2 boxes,
we enter the data, courtesy of further code, to the relevant worksheets - where we look see the 'weekly/monthly monies spent'
on any particular item the Hotel/ Pub is selling
It is rather nerve racking after you've input 1000 items by mouse clicking on a commandbutton everytime
We, like most would, looked for a shortcut via the ENTER button
But after trying examples from webpages and VBA books, it got rather complicated with all the "won't work because dialogue box takes focus" and the like.......we're not too good at this.....so we
came to the best place, as we have before, and found wonderful people who give us their time and their knowledge to solve our problem.....
Judging by comments on webpages - this maybe is not so easily resolved
but thank you all for aiding and abetting

 

[Norie]

Dave

I think this could be resolved but it could require a major rethink/design.

You really shouldn't not have different columns for each different product/field/whatever, that just doesn't make sense.

Imagine if this was some sort of accounting system, which it appears it might be.

You would probably have a date column, a product/service/whatever column, columns for amounts in/out/etc, perhaps a column for comments, one for invoice number.

This might sound complicated but it's actually a simple approach and if set up properly will be conducive to analysis/manipulation/whatever.

If you were to try this different route then I'm sure we could help, but there's one vital thing we need - information.

ie examples, expected results, what you currently have set-up etc

PS I'm still confused why you want to emulate the ENTER button.

 

[westconn1]

i still have no idea at what point you want to send enter, to what you want to send enter, or where you want to go by sending enter

as norie says
with code all things are possible, but the solution may require rethinking the method, many other similar programs have found solutions to what you want to achieve