VBA rewrite textbox with former value.

Discussion in 'Programmer's Corner' started by atferrari, Nov 5, 2013.

  1. atferrari

    Thread Starter AAC Fanatic!

    Jan 6, 2004
    2,648
    762
    VBA Excel

    In a user form, the spin button brings a value up and down between 1 and 32. Value is shown in the textbox.

    The user is given the possibility of typing the value directly in the textbox.

    The code rejects it, if out of range, but fails to rewrite the old (good) one in the textbox to make evident that the last typed was not accepted.


    Code ( (Unknown Language)):
    1. Option Explicit
    2.     Const TTR_val_max As Byte = 32
    3.     Const TTR_val_min As Byte = 1
    4.  
    5. Private Sub TTR_control_enter()
    6.     Dim TTR_val As Byte
    7.     Dim TTR_val_old As Byte
    8.    
    9.     TTR_val_old = TTR_control.Value '???
    10.     TTR_val = CByte(Val(TextBox_TTR.Text))
    11.    
    12.     If TTR_val > TTR_val_max Or TTR_val < TTR_val_min Then
    13.         TextBox_TTR = TTR_val_old '???
    14.         Exit Sub
    15.     Else
    16.         'Range("GC4").Value = TTR_val - for debugging only
    17.         TextBox_TTR = TTR_val
    18.         TTR_control.Value = TTR_val
    19.     End If
    20. End Sub
    21.  
    22. Private Sub TTR_control_SpinDown()
    23.     TextBox_TTR = TTR_control.Value
    24. End Sub
    25.  
    26. Private Sub TTR_control_SpinUp()
    27.     TextBox_TTR = TTR_control.Value
    28. End Sub
    29.  
    I found that the typed value is processed no matter if a hit Enter or not. More to add to my confusion.

    Any suggestion on how to put this straight?

    I am sure I have something mixed up (properties and...?)

    Gracias.
     
  2. sirch2

    Well-Known Member

    Jan 21, 2013
    1,008
    351
    I assume
    Code ( (Unknown Language)):
    1. Sub TTR_control_enter()
    is the control "onEnter" event?

    If it is then the value has not yet been changed, probably better to do it on exit or something.

    Also, I wouldn't use byte for storing integers unless there is some real burning need. Use int, your life will be simpler
     
  3. atferrari

    Thread Starter AAC Fanatic!

    Jan 6, 2004
    2,648
    762
    Yes, that event. Whether I hit Enter or not, the value changes. (?)


    For values going just from 1 to 32? It looks reasonable to me since it will not suffer any process; just being sent to a file which is later transferred to a micro.

    Thanks for replying.
     
  4. sirch2

    Well-Known Member

    Jan 21, 2013
    1,008
    351
    The Enter event is not "when the Enter is pressed"
    according to MSDN it "occurs before a control actually receives the focus from a control on the same form control"

    So the event fires just before the control is gets focus so I believe you are doing your checks etc. before the value has changed. I guess what you really want to do is run those checks after the value has been set? So use the Change or Exit event (generally you need both because the user may not move off the control and so Exit may not fire.

    Re byte, if you have a 32 bit processor it reads 4 bytes at a time whether it is a byte or an int and with a gigabyte or two of RAM saving 3 bytes just isn't worth the effort.
     
    atferrari likes this.
  5. atferrari

    Thread Starter AAC Fanatic!

    Jan 6, 2004
    2,648
    762
    The processor might be a 32-bit one but I am dealing with 5*30*7*7*7 cells with data. I believe it is relevant (but I use to be wrong most of the time as my ex and her mother insisted to say every time they had a chance).
     
  6. panic mode

    Senior Member

    Oct 10, 2011
    1,320
    304
    i would set min/max values in properties of the spinbox.
    then i would evaluate typed value (if entered directly) and make sure it is in range, something like:

    Code ( (Unknown Language)):
    1. Option Explicit
    2.  
    3. Private Sub SpinButton1_Change()
    4.    TextBox1.Text = SpinButton1.Value
    5. End Sub
    6.  
    7. Private Sub TextBox1_Change()
    8.  Dim n As Integer
    9.  n = Val(TextBox1.Text)
    10.  If n > SpinButton1.max Then SpinButton1.Value = SpinButton1.max
    11.  If n < SpinButton1.min Then SpinButton1.Value = SpinButton1.min
    12.  TextBox1.Text = SpinButton1.Value
    13. End Sub
    14.  
    15. Private Sub Worksheet_Activate()
    16.  SpinButton1.max = 32
    17.  SpinButton1.min = 1
    18. End Sub
     
  7. panic mode

    Senior Member

    Oct 10, 2011
    1,320
    304
    i think this is exactly what you wanted:

    Code ( (Unknown Language)):
    1. Option Explicit
    2.  
    3. Private Sub Worksheet_Activate()
    4.  SpinButton1.max = 32
    5.  SpinButton1.min = 1
    6. End Sub
    7.  
    8. Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    9.  Dim n As Integer
    10.  n = Val(TextBox1.Text)
    11.  If (KeyCode = 13) Then 'value 13 is the Enter key
    12.     If (n <= SpinButton1.max) And (n >= SpinButton1.min) Then
    13.        SpinButton1.Value = n
    14.     Else
    15.        MsgBox ("Sorry but that value is not in range " & SpinButton1.min & "-" & SpinButton1.max)
    16.     End If
    17.     TextBox1.Text = SpinButton1.Value
    18.  End If
    19. End Sub
    20.  
    21. Private Sub SpinButton1_Change()
    22.    TextBox1.Text = SpinButton1.Value
    23. End Sub
    24.  
    here is a working file:
     
    atferrari likes this.
  8. atferrari

    Thread Starter AAC Fanatic!

    Jan 6, 2004
    2,648
    762
    Gracias Panic. Solved my problem.
     
Loading...