Do .Sel and .SelLength not work on formatted numbers in a TextBox?

[birdieman]

I have the following code:

Private Sub TextBox8_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

If Val(TextBox8) > 25000000 Then
    MsgBox ("This box does not support entries greater than $25,000,000."), vbInformation
    TextBox8.SelStart = 0
    TextBox8.SelLength = Len(TextBox8.Text)
    TextBox8 = 0
    Cancel = True
 End If
        
   TextBox8.Value = Format(TextBox8.Value, "$###,###,##0")

End Sub

$0 is sitting in the TextBox before any user input. Assume the user keys in 30000000

I would like the contents of the TextBox (now showing a $0) to be entirely highlighted. However, the cursor is blinking to the right of $0.

1. What do I change to make the entire contents be selected/highlighted? Or does it not work on formatted numbers?

2. Since a $0 appears in the TextBox after someone keys in 30000000, I am assuming the line "TextBox8=0" Refires the BeforeUpdate event. Correct?

thanks for looking -- I am still learning this stuff

 

[Domenic]

Try...

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] TextBox8_BeforeUpdate([COLOR=darkblue]ByVal[/COLOR] Cancel [COLOR=darkblue]As[/COLOR] MSForms.ReturnBoolean)
    
    [COLOR=darkblue]If[/COLOR] Val(TextBox8) > 25000000 [COLOR=darkblue]Then[/COLOR]
        MsgBox ("This box does not support entries greater than $25,000,000."), vbInformation
        TextBox8.Value = Format(0, "$###,###,##0")
        TextBox8.SelStart = 0
        TextBox8.SelLength = Len(TextBox8.Text)
        Cancel = [COLOR=darkblue]True[/COLOR]
        [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]

    TextBox8.Value = Format(TextBox8.Value, "$###,###,##0")

[COLOR=darkblue]End[/COLOR] [COLOR=darkblue]Sub[/COLOR]

Hope this helps!

 

[birdieman]

Domenic

yes, it worked, thank you . However, 1) can you explain why adding the Exit Sub makes it work, and 2) I guess when they enter a new (correct) number, the If statement becomes false, then processing would fall to the format line, where I would add anything else that I need to do. Correct?

 

[Domenic]

1) Actually, what made it work is entering 0 before attempting to select the contents of the textbox. Then, Exit Sub is used to exit the procedure so that the contents of the textbox doesn't get formatted and, as a result, de-selected.

2) That's right.

 

[birdieman]

To make sure I understand.

1.You say the Exit sub prevents the number from being formatted, yet there is a format statement in the code. When I run the sub, it shows $0 (after having entered 30000000). ??

2. When the code puts a 0 in the textbox (and the user does not change it), and the user hits tab/enter/exits the box, the whole sub fires again, right? -- as if the user input the 0, which constituted an "update" to the box???

thanks - no more questions

 

[Domenic]

1. When the value entered exceeds the target amount, it enters 0, and formats it. Then it selects the contents of the textbox, and then it exits. If it didn't exit, it would proceed to format the contents again, and as a result would de-select the contents. However, when the value entered is less than or equal to the target amount, it simply proceeds to format the existing contents of the textbox.

2. Yes, the event gets triggered.