Solved: Highlight control in UserForm

[jwise]

Perhaps I'm using the wrong terminology... I have a UserForm and have successfully updated a row. The UserForm positions to the next row correctly, but the "Process" command button is still "highlighted" when the new data is displayed. I would prefer the "highlight" to move back to the first data entry field (a TextBox). Any ideas?

TIA

[Charlize]

[VBA]Me.TextBox1.SetFocus[/VBA] ? Where Me is referring to the active object, probably a userform.

Charlize

[jwise]

Thanks Charlize. I really appreciate your assistance.

When I tried this, I was surprised that the cursor was at the "end" of the data field. Let's assume that the field contains "12345". The cursor is positioned AFTER the "5", which means the user must back up to change the data. Is there a way to position at the "1"?

Thanks again for the information. I'm getting there!

[Bob Phillips]

[vba]With Me.TextBox1
.SelStart=0
.SetFocus
End With
[/vba]
If you want it all selected

[vba]With Me.TextBox1
.SelStart=0
.SelLength = Len(.Text)
.SetFocus
End With
[/vba]

[jwise]

XLD: Thank you. You have been most helpful.

I see what your code is doing, but I have no clue why this "With" construction is necessary. Will you elaborate on this?

Thanks again.

[johnske]

It's not so much 'necessary' as "good practice" as it speeds up code execution and avoids unneeded repetition - read - as the alternative would be to use...[vba] Me.TextBox1.SelStart=0
Me.TextBox1.SelLength = Len(.Text)
Me.TextBox1.SetFocus[/vba]which is definitely not best practice

[Bob Phillips]

It's not so much 'necessary' as "good practice" as it speeds up code execution and avoids unneeded repetition - read - as the alternative would be to use...[vba] Me.TextBox1.SelStart=0
Me.TextBox1.SelLength = Len(.Text)
Me.TextBox1.SetFocus[/vba]which is definitely not best practice

More important IMO is readability, the larger the block withion the With ... End With, the more the readability is improved.

[jwise]

Thanks again for all the posts.

I used this information to help input a large amount (for me!) of numbers. I began using the numeric pad about half way through this project. I later spoke with an accountant who uses the numeric pad almost exclusively, and he challenged me with this requirement.

Asssume the user form was a 4 by 3 matrix of fields for numeric entry. I want the cursor to begin at the (1,1) position. If I press ENTER or the RIGHT Arrow, I want the cursor positioned at the (1,2) position. Next entry it would go to (1,3), and then to (2,1), (2,2), ... (4,3).

However, if I press the DOWN arrow, I want the cursor to move down to the next row. On the bottom row, it would jump to the top. The UP arrrow would cause movement of the cursor up one row, etc.

Is there a way to do this?

[Bob Phillips]

worksheet event code

[vba]

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Const SHEET_RANGE As String = "G5:J7" '<=== change to suit

If Intersect(Target, Me.Range(SHEET_RANGE)) Is Nothing Then
With Me.Range(SHEET_RANGE)
If Target.Row > .Row + .Rows.Count - 1 Then
Me.Cells(.Row, Target.Column).Select
ElseIf Target.Row < .Row Then
Me.Cells(.Row + .Rows.Count - 1, Target.Column).Select
End If
If Target.Column > .Column + .Columns.Count - 1 Then
Me.Cells(Target.Row, .Column).Select
ElseIf Target.Column < .Column Then
Me.Cells(Target.Row, .Column + .Columns.Count - 1).Select
End If
End With
End If

End Sub
[/vba]

[Charlize]

Try this for enter, right and down. Starts at A1.

Charlize

edited : apparently a little too late ...

[jwise]

Thanks to xld and Charlize. It will take me a while to check this out.

I really appreciate your quick replies, and I am so impressed with the quality and technical sophistication of your coding suggestions.

Thanks again.

[jwise]

The UserForm I developed works, but I have discovered an additional complication. There are two separate sources for the numbers, and when using the second source, the last number (it comes from a single field on the first source) becomes a sum from two fields (second source).

Can I put a choice for source 1 or 2 such that there are 8 input fields on source 1, but 9 input fields on source 2? The UserForm is designed to look like the paper form where the numbers originate, and fortunately the field in question is field 8 (the last field). My thought is that the form would have three sections. The top section would include this choice button(s). Section 2 or 3 would be visible (actually grayed out for not selected) based on this "choice button".

Is this doable? Any suggestions? A better design?

TIA