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.

Option Explicit
    Const TTR_val_max As Byte = 32
    Const TTR_val_min As Byte = 1

Private Sub TTR_control_enter()
    Dim TTR_val As Byte
    Dim TTR_val_old As Byte
    
    TTR_val_old = TTR_control.Value '???
    TTR_val = CByte(Val(TextBox_TTR.Text))
    
    If TTR_val > TTR_val_max Or TTR_val < TTR_val_min Then
        TextBox_TTR = TTR_val_old '???
        Exit Sub
    Else
        'Range("GC4").Value = TTR_val - for debugging only
        TextBox_TTR = TTR_val
        TTR_control.Value = TTR_val
    End If
End Sub

Private Sub TTR_control_SpinDown()
    TextBox_TTR = TTR_control.Value
End Sub

Private Sub TTR_control_SpinUp()
    TextBox_TTR = TTR_control.Value
End Sub

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

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

 

I assume

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.

Yes, that event. Whether I hit Enter or not, the value changes. (?)

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

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:

Option Explicit

Private Sub SpinButton1_Change()
   TextBox1.Text = SpinButton1.Value
End Sub

Private Sub TextBox1_Change()
 Dim n As Integer
 n = Val(TextBox1.Text)
 If n > SpinButton1.max Then SpinButton1.Value = SpinButton1.max
 If n < SpinButton1.min Then SpinButton1.Value = SpinButton1.min
 TextBox1.Text = SpinButton1.Value
End Sub

Private Sub Worksheet_Activate()
 SpinButton1.max = 32
 SpinButton1.min = 1
End Sub

 

i think this is exactly what you wanted:

Option Explicit

Private Sub Worksheet_Activate()
 SpinButton1.max = 32
 SpinButton1.min = 1
End Sub

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
 Dim n As Integer
 n = Val(TextBox1.Text)
 If (KeyCode = 13) Then 'value 13 is the Enter key
    If (n <= SpinButton1.max) And (n >= SpinButton1.min) Then
       SpinButton1.Value = n
    Else
       MsgBox ("Sorry but that value is not in range " & SpinButton1.min & "-" & SpinButton1.max)
    End If
    TextBox1.Text = SpinButton1.Value
 End If
End Sub

Private Sub SpinButton1_Change()
   TextBox1.Text = SpinButton1.Value
End Sub

here is a working file:

 

Gracias Panic. Solved my problem.