Add decimal point at specific position

[sharky12345]

I'm using this to loop through a series of Textboxes on a Userform and enter a decimal point after the first 2 numbers entered;

Dim ctrl As MSForms.controlFor Each ctrl In Controls
If ctrl.Name Like "TextSTime*" Then
If Len(ctrl.Value) = 3 Then ctrl.Value = Left(ctrl.Value, 2) & "." & Right(ctrl.Value, 1)
End If
Next

I need something similar now - what I need is for it to add the decimal point before the last 2 numbers, regardless of how many are entered, (although I have limited the Textboxes to a maximum of 6 characters).

Some examples of what I'd expect to see:

101.98

91.45

8.73

Anyone?

 

[tyija1995]

Hi,

How about Left(ctrl.Value, Len(ctrl.Value)-2) & "." & Right(ctrl.Value, 2)

EDIT: Length must be at least 2

 

[Fluff]

How about

Dim ctrl As MSForms.Control
For Each ctrl In Controls
   If ctrl.Name Like "TextSTime*" Then
      ctrl.Value = ctrl.Value / 100
   End If
Next

 

[SupremeDr]

=a1/100

 

[sharky12345]

Guys,

I've tried this:

Dim ctrl As MSForms.controlFor Each ctrl In Controls
If ctrl.Name Like "TextSTime*" Then
If Len(ctrl.Value) = 3 Then ctrl.Value = Left(ctrl.Value, Len(ctrl.Value) - 2) & "." & Right(ctrl.Value, 2)
End If
Next

but if I enter "9825" that gives me:

9.825

I've also tried this:

Dim ctrl As MSForms.controlFor Each ctrl In Controls
   If ctrl.Name Like "TextSTime*" Then
      ctrl.Value = ctrl.Value / 100
   End If
Next

but that gives me 'Type Mismatch' on this line:

ctrl.Value = ctrl.Value / 100

Just to confirm, these are textboxes on a userform, not a worksheet - any ideas?

 

[Fluff]

Are all those textboxes numbers only?
If so & some can be empty try

      If ctrl <> "" Then ctrl.Value = ctrl.Value / 100

 

[sharky12345]

Cheers Fluff - yes, they are....your suggestion works, but only if I put it in the exit event - I was hoping it could be done on the change event so it's formatted as they type, can that be done? No worries if not.

 

[Fluff]

Why on earth are you looping through all the textboxes with a change event?

Also as you are changing the value in the textbox, it's best not to run that in a change event.
Try something like

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
   With Me.TextBox1
      If Len(.Value) > 0 Then .Value = .Value / 100
   End With
End Sub

 

[sharky12345]

I'm doing it in the change event because I wanted it to format as they type instead of having to wait for them to tab out for it to be formatted.....

 

[Fluff]

Do you have any code in the change event to prevent it from triggering itself?