I have a userform with 23 textboxes and 2 comboboxes. In an effort to
advance the cursor to the next textbox or combobox if the user hit the ENTER
key, I have the following for each, ie a total of 25 of these routines (with
appropriate adjustments to match):

Private Sub TextBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 13 Then Textbox7.SetFocus
End Sub

I was wondering if there was a way to condense to one routine and use SELECT
CASE, or IF-THEN?

I don't know what the first line would have to be (Private Sub...), but I am
thinking
along the lines that I would need to know the ACTIVE (current textbox), then
be able to advance using a variable, similar to below:

DIM b as Interger
b would need to be a value to represent active textbox
then code might look like this:
If KeyCode = 13 Then Textbox & (b+1).SetFocus

Thanks,
Les

You will still need to detect the event for the control, however you might
consider using the Controls collection for the form to determine the next
control.


Me.Controls("TextBox" & b + 1).SetFocus

Pass the value of b from the current control.

On the face of it it does not save you a lot, but it might be useful if your
have other common code to run.

--

Regards,
Nigel




"WLMPilot" wrote in message
...
I have a userform with 23 textboxes and 2 comboboxes. In an effort to
advance the cursor to the next textbox or combobox if the user hit the
ENTER
key, I have the following for each, ie a total of 25 of these routines
(with
appropriate adjustments to match):

Private Sub TextBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 13 Then Textbox7.SetFocus
End Sub

I was wondering if there was a way to condense to one routine and use
SELECT
CASE, or IF-THEN?

I don't know what the first line would have to be (Private Sub...), but I
am
thinking
along the lines that I would need to know the ACTIVE (current textbox),
then
be able to advance using a variable, similar to below:

DIM b as Interger
b would need to be a value to represent active textbox
then code might look like this:
If KeyCode = 13 Then Textbox & (b+1).SetFocus

Thanks,
Les

I don't understand exactly what you are saying or how it is going to work. I
am still learning VBA Excel. I do have programming experience from other
languages from the 80's so I can guess there are ways to shorten what I want
to do, just don't know how to do it.

Les


"Nigel" wrote:

You will still need to detect the event for the control, however you might
consider using the Controls collection for the form to determine the next
control.


Me.Controls("TextBox" & b + 1).SetFocus

Pass the value of b from the current control.

On the face of it it does not save you a lot, but it might be useful if your
have other common code to run.

--

Regards,
Nigel




"WLMPilot" wrote in message
...
I have a userform with 23 textboxes and 2 comboboxes. In an effort to
advance the cursor to the next textbox or combobox if the user hit the
ENTER
key, I have the following for each, ie a total of 25 of these routines
(with
appropriate adjustments to match):

Private Sub TextBox6_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal
Shift As Integer)
If KeyCode = 13 Then Textbox7.SetFocus
End Sub

I was wondering if there was a way to condense to one routine and use
SELECT
CASE, or IF-THEN?

I don't know what the first line would have to be (Private Sub...), but I
am
thinking
along the lines that I would need to know the ACTIVE (current textbox),
then
be able to advance using a variable, similar to below:

DIM b as Interger
b would need to be a value to represent active textbox
then code might look like this:
If KeyCode = 13 Then Textbox & (b+1).SetFocus

Thanks,
Les