TextBox setfocus unexpected call

[Ziggy12]

I've got a user form with 12 textbox, one for each month a total textbox number 13 and a 14th text box (TB28) to enter $'s in.

AfterUpdate in each textbox 1-12 posts its value to a worksheet all values are summed in a =sum range which in turn populates textbox 13. When TB13.text = 100.00 it sucessfully calls a number of routines but what I can't get it to do is set focus on TB28.

Private Sub TextBox13_Change()
If TextBox13.Text = "100.00" Then
With FormPhasedAmt.TextBox28
.SelStart = 0
.SelLength = Len(TextBox28.Text)
.SetFocus
End With
End If
End Sub

It returns a Run time error Unexpected call to method or property access highlighting .SetFocus. The rest of the code works OK. Is it because other textbox on the same user form have text highlighted because of the tab order?

cheers
Ziggy

 

[mikerickson]

Does TextBox13 have and AfterUpdate, Exit or other Code?
Does TextBox28 have an Enter event?

 

[Ziggy12]

I tried TB13 withAfterUpdate but it didn't even register. Nothing happened a stop on the line didn't activate.

This works though

Private Sub TextBox13_Change()
If TextBox13.Text = "100.00" Then
With FormPhasedAmt.TextBox28

.SelText = "Enter Annual Budget Amt"

End With
End If
End Sub

But it doesn't select the text and the text box doesn't get the focus!!

Ziggy

 

[mikerickson]

Silly question but, the code you posted in the OP, that for TextBox13. Is TextBox13 in FormPhasedAmt? If you replace FormPhasedAmt with Me, does it still error?

 

[Ziggy12]

Yes it is on the same user form and still errors

Private Sub TextBox13_Change()
If TextBox13.Text = "100.00" Then
With Me.TextBox28
.Text = "Enter Annual Budget Amt"
'.SelText = "Enter Annual Budget Amt"
.SelStart = 0
.SelLength = Len(TextBox28.Text)
.SetFocus
End With
End If
End Sub

Odd that SelText adds the text but doesn't Sel it.

 

[mikerickson]

AFAIK, .SelText isn't a property of a textbox.

What version of Excel are you using.

 

[Norie]

Ziggy

Why are you using all these events, all these controls?

In fact why are you using SetFocus?

Mike

SelText is a property of a textbox, and as far as I know it always has been.

 

[Ziggy12]

Using XL2007

Ah!
SetFocus isn't a property of a textbox but SelText is and it doesn't select the text after entering it. Although it does enter the text. Wonder if the tab order or action on enter for TB1 to TB13 which makes the next TB get the focus after the preceeding one has been entered and seltext taking it off TB28 after its been updated.

i.e TB1="100.00">Wrkbk updated>TB13="100.00">TB28 updated>TB2 tabbed to and gets the focus unselecting the text in TB28.

Private Sub TextBox13_Change()
If TextBox13.Text = "100.00" Then
With Me.TextBox28
.SelText = "Enter Annual Budget Amt"
End With
End If
End Sub

 

[Norie]

Why are you using SelText?

If you just want to put some text in a textbox use it's Value or Text property.

I'm pretty confused why you want/need to use all these different methods/events.

 

[Ziggy12]

Hi Norie & Mikerickson

What I want to do is have the user enter values into 12 textboxes one for each month (TB1 to TB 12) till the total in TB13 equals 100.00. and then prompt the user to enter an amount into TB28. The prompt being TB Text = "Enter Annual Budget Amt" appearing selected in TB28.

I also want TB1 to 12 tab one after the other.

The user might change any of TB1-12 after TB13 = 100.00

Ziggy