Achieve specific behavior in a textbox (Crosspost)

[kapela2017]

In accordance with what is indicated in Rule # 13 of the forum, I will limit the following
Link

Input in textbox from right to left with comma positioning

Greetings code warriors, I will try to be as explicit as possible, for months I have been struggling with a code without success, I am trying to create a textbox code that allows me to enter the digits from right to left, and in the second digit place a comma automatically concatenated with the...

www.excelforum.com

Post details

Greetings seniors, I've been trying for a while to get a text box to behave in a specific way, which I'll try to briefly describe, I need the values to be entered from right to left, like on a calculator and the format should have "Kg" concatenated either It must be necessary to enter the comma because it is automatically positioned, I attach the Work Code in the Two textboxes can be seen But this must be done in a single textbox without the need to have another text box as an auxiliary, I get the impression that this will be easier with a Udf, but I remain attentive to your suggestions

Option Explicit

Dim PreviousValue As String
Dim ProgramChange As Boolean

Private Sub TextBoxDataEntry_Change()

   Dim FormattedNumber As String
   
   If ProgramChange Then Exit Sub
   
   If TextBoxDataEntry = "" Then
      TextBoxDataDisplay = ""
   ElseIf Not IsNumeric(TextBoxDataEntry) Then
      MsgBox "Entry must be numeric"
      ProgramChange = True
      TextBoxDataEntry = PreviousValue
      ProgramChange = False
   ElseIf CLng(TextBoxDataEntry) > 9999 Then
      MsgBox "Entry must be numeric 0 - 9999"
      ProgramChange = True
      TextBoxDataEntry = PreviousValue
      ProgramChange = False
   Else
      FormattedNumber = Format(TextBoxDataEntry, "0000")
      TextBoxDataDisplay = Left(FormattedNumber, 2) & "," & Right(FormattedNumber, 2) & " Kg"
      PreviousValue = TextBoxDataEntry
   End If

End Sub

 

[Micron]

I'm not a member there so I cannot see any code or download any posted files, so my suggestion may be totally useless or even already suggested.
Perhaps if you used the KeyUp event to return the character of the key, then concatenate the textbox.Text to that and make that string the new value. Forgetting about commas or Kg for the moment, that would mean if the control contained 1, and you press 2 you'd make the control.Text property equal to 12. Then press 3 and you concatenate 3 to 12 to get 123.

That's just a germ of an idea but should allow you to use one control. However, your comma placement makes no sense to me. 12,34 and not 1,234??
You could clear the control with a double click, otherwise you have to start worrying about delete and backspace characters, which might turn out to be necessary anyway.

 

[kapela2017]

I'm not a member there so I cannot see any code or download any posted files, so my suggestion may be totally useless or even already suggested.
Perhaps if you used the KeyUp event to return the character of the key, then concatenate the textbox.Text to that and make that string the new value. Forgetting about commas or Kg for the moment, that would mean if the control contained 1, and you press 2 you'd make the control.Text property equal to 12. Then press 3 and you concatenate 3 to 12 to get 123.

That's just a germ of an idea but should allow you to use one control. However, your comma placement makes no sense to me. 12,34 and not 1,234??
You could clear the control with a double click, otherwise you have to start worrying about delete and backspace characters, which might turn out to be necessary anyway.

Regards, Micron, take a good look at the image and you will see the detailed description, and believe me it is more complex than what you mention and I tried it without success, I thought it would be easier and I left it for the end, serious error, and I have sought help without any success until now. moment

 

[Micron]

I saw the image, but you're right - it's not so simple. I have a working approach but it's a bit of a convoluted thing since I took to using various Mid functions in a Select Case block that retrieves a module level integer variable. Started out with 00,00 Kg and it seems to work. I ended up with this in my first step through test (entering 1 then 2 and so on, up to 4):

I think that starting with 00,00 Kg looks confusing.

Where would the user put the cursor in that to edit it?

Second test without stepping through gave the proper result but unfortunately it leaves the entered character after the Kg so I likely broke something after the first test. To provide a clue of the convolution, here's the expression for the 4th character entry

TextBox2 = Mid(TextBox2, 2, 1) & Mid(TextBox2, 4, 1) & "," & Trim(Mid(TextBox2, 5, 1)) & Chr(KeyAscii)

Yes, a string variable will help - it's just how things ended up for something that you might not want to pursue anyway. Also, there is no validation that only numbers are entered in what I have (at least not yet).

It's after midnight here so that's it for me tonight.

 

[kapela2017]

Vi la imagen, pero tienes razón, no es tan simple. Tengo un enfoque de trabajo, pero es un poco complicado ya que comencé a usar varias funciones Mid en un bloque Select Case que recupera una variable entera de nivel de módulo. Comenzó con 00,00 Kg y parece funcionar. Terminé con esto en mi primer paso a través de la prueba (ingresando 1, luego 2 y así sucesivamente, hasta 4):
View attachment 86385

Creo que empezar con 00,00 Kg se ve confuso.
View attachment 86386
¿Dónde colocaría el usuario el cursor para editarlo?

La segunda prueba sin avanzar dio el resultado correcto, pero desafortunadamente deja el carácter ingresado después del Kg, por lo que probablemente rompí algo después de la primera prueba. Para proporcionar una pista de la convolución, aquí está la expresión para la entrada del cuarto carácter

TextBox2 = Mid(TextBox2, 2, 1) & Mid(TextBox2, 4, 1) & "," & Trim(Mid(TextBox2, 5, 1)) & Chr(KeyAscii)

Sí, una variable de cadena ayudará: así es como terminaron las cosas para algo que tal vez no quiera seguir de todos modos. Además, no hay validación de que solo se ingresen números en lo que tengo (al menos no todavía).

Es más de medianoche aquí, así que eso es todo para mí esta noche.

You are right, the correct approach would be between the numeric value and the string, as for the preview if the textbox is empty it doesn't matter as textx will display the data from a serial position created with python (we are still working on it ) if it does not register anything it will not show anything

 

[Micron]

textx will display the data from a serial position created with python (we are still working on it ) if it does not register anything it will not show anything

You totally lost me there.

 

[Micron]

This is what seems to work, as long as you shift the focus after an entry (e.g. hit enter). Textbox is on a userform but that may not matter.
It seems like a lot of work for not much return.

'at userform module level
Option Explicit
Dim charCount As Integer
Dim bolStripNum As Boolean

Private Sub TextBox2_AfterUpdate()
If bolStripNum Then TextBox2 = Left(TextBox2, Len(TextBox2) - 1)
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim str As String
'00,00 Kg ; 00,01 Kg ; 00,12 Kg ; 01,23 Kg ; 12,34 Kg

'Application.EnableEvents = False
If Not KeyAscii > 47 Or Not KeyAscii < 58 Then
    MsgBox "Enter only numbers"
    Exit Sub
End If

bolStripNum = False
str = Me.TextBox2
Select Case charCount
    Case 0
        str = "00,0"
        TextBox2 = str
        charCount = charCount + 1
    Case 1
        str = Mid(str, 1, 3) & Mid(str, 5, 1)
        TextBox2.Text = str
        charCount = charCount + 1
    Case 2
        str = Mid(str, 1, 1) & Mid(str, 4, 1) & "," & Trim(Mid(str, 5, 2))
        TextBox2.Text = str
        charCount = charCount + 1
    Case 3
        str = Mid(str, 2, 1) & Mid(str, 4, 1) & "," & Trim(Mid(str, 5, 1))
        TextBox2.Text = str
        charCount = charCount + 1
    Case Else
        bolStripNum = True
        Exit Sub
End Select

TextBox2 = str & Chr(KeyAscii) & " Kg"
bolStripNum = True
'Application.EnableEvents = True

End Sub

Private Sub TextBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'TextBox2 = "00,00 Kg"
TextBox2 = ""
charCount = 0
End Sub

 

[kapela2017]

This is what seems to work, as long as you shift the focus after an entry (e.g. hit enter). Textbox is on a userform but that may not matter.
It seems like a lot of work for not much return.

'at userform module level
Option Explicit
Dim charCount As Integer
Dim bolStripNum As Boolean

Private Sub TextBox2_AfterUpdate()
If bolStripNum Then TextBox2 = Left(TextBox2, Len(TextBox2) - 1)
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Dim str As String
'00,00 Kg ; 00,01 Kg ; 00,12 Kg ; 01,23 Kg ; 12,34 Kg

'Application.EnableEvents = False
If Not KeyAscii > 47 Or Not KeyAscii < 58 Then
    MsgBox "Enter only numbers"
    Exit Sub
End If

bolStripNum = False
str = Me.TextBox2
Select Case charCount
    Case 0
        str = "00,0"
        TextBox2 = str
        charCount = charCount + 1
    Case 1
        str = Mid(str, 1, 3) & Mid(str, 5, 1)
        TextBox2.Text = str
        charCount = charCount + 1
    Case 2
        str = Mid(str, 1, 1) & Mid(str, 4, 1) & "," & Trim(Mid(str, 5, 2))
        TextBox2.Text = str
        charCount = charCount + 1
    Case 3
        str = Mid(str, 2, 1) & Mid(str, 4, 1) & "," & Trim(Mid(str, 5, 1))
        TextBox2.Text = str
        charCount = charCount + 1
    Case Else
        bolStripNum = True
        Exit Sub
End Select

TextBox2 = str & Chr(KeyAscii) & " Kg"
bolStripNum = True
'Application.EnableEvents = True

End Sub

Private Sub TextBox2_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
'TextBox2 = "00,00 Kg"
TextBox2 = ""
charCount = 0
End Sub

Micron Broo Thanks for all the interest in solving the query, believe me several experts have tried and so far without success, the code you provided generates the following error as soon as I enter a value in the

 

[Micron]

Make sure you hit Enter after you input the number. There is nothing that I can see that will get around that when it comes to Excel. If that's not the fix then post back but with all the code as you have it. IMO, Excel is sorely lacking in some events, methods and properties when compared to Access. I suspect that some programmers were not invited to some of those meetings.
Anyway, I coded based on limited info, so if you intend to type in 123 then another approach is required. I probably should not have interpreted your pic as entering 1 and committing, then 2 and committing, then 3...
Maybe fully explain what user is doing and verify that the end result is what is wanted (12,34 Kg) and not everything you've shown in between start and finish.

 

[kapela2017]

Make sure you hit Enter after you input the number. There is nothing that I can see that will get around that when it comes to Excel. If that's not the fix then post back but with all the code as you have it. IMO, Excel is sorely lacking in some events, methods and properties when compared to Access. I suspect that some programmers were not invited to some of those meetings.
Anyway, I coded based on limited info, so if you intend to type in 123 then another approach is required. I probably should not have interpreted your pic as entering 1 and committing, then 2 and committing, then 3...
Maybe fully explain what user is doing and verify that the end result is what is wanted (12,34 Kg) and not everything you've shown in between start and finish.

It is not just an example, it can be any value, it is looking for that type of behavior, it must be dynamic with the change event, but in any case I am very grateful, I will continue looking for the solution, any ideas I remain attentive