VBA rewrite textbox with former value.

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.

 

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

 

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.

 

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.

 

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).

 

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

 

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:

 

Gracias Panic. Solved my problem.