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
(E-Mail Removed)



"WLMPilot" <(E-Mail Removed)> wrote in message
news:C2A85278-D754-4226-B80D-(E-Mail Removed)...
>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
> (E-Mail Removed)
>
>
>
> "WLMPilot" <(E-Mail Removed)> wrote in message
> news:C2A85278-D754-4226-B80D-(E-Mail Removed)...
> >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
>