Excel Userform - Restrict entry to quarter decimals 1

[RP1America]

Hi! I'm looking to restrict userform (textbox) entry to only quarter decimals (.25, .50, .75, 1, 1.25, etc.).

I currently have the following code:

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    txtTime.Text = Format(CDec(txtTime.Text), "0.00")
    
End Sub

Private Sub txtTime_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case Asc("0") To Asc("9")
    Case Asc(".")
        If InStr(1, Me.txtTime.Text, ".") > 0 Then
            KeyAscii = 0
        End If
    Case Else
        KeyAscii = 0
End Select
End Sub

There are two things that I need to happen...

1) Only allow input of quarter decimals (as explained above).

2) Be able to have an entry of ".25". Currently, I am unable to enter a "." first before any digits. So user would have to enter "0.25", yet I would like for them to simply enter ".25" when under an hour.

Thanks in advance!!

Ryan

 

[combo]

Instead of characters you can test potential textbox entry:

Private Sub txtTime_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(Me.txtTime & Chr(KeyAscii) & 0) Then
    KeyAscii = 0
Else
    v = CDbl(Me.txtTime & Chr(KeyAscii) & 0)
    If (4 * v <> Int(4 * v)) And (4 * v + 0.2 <> Int(4 * v + 0.2)) Then KeyAscii = 0
End If
End Sub

combo

 

[SkipVought]

how about

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    txtTime.Text = Format(INT(CDec(txtTime.Text)/.25)*.25, "0.00")
    
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Thanks for the reply!

I could not get that code to work. In fact, it restricted all numeric entry.

 

[RP1America]

Skip,

Thanks!

That works pretty darn great for restricting to quarters! Doesn't do much for rounding (but that's not a big deal anyway).

I still have the issue of not being able to enter a decimal FIRST. (EX: ".25")

 

[SkipVought]

hmmmm???

In my test, I can simply enter .25 and then TAB, and the result in the box becomes 0.25

Is that not what you want?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Doesn't do much for rounding

Adjust in the formula.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[RP1America]

Skip,

I see that if I remove my second Sub (only allows 0-9 and ".") then yes, I can enter .25 and it becomes 0.25. Yet I would like to allow for only 0-9 and "." to be entered into the field, non alpha.

Also, I found some information on Round(), yet am not sure how to implement it. Your thoughts?

Thanks!

 

[RP1America]

I replaced Int with Round and have achieved what I am looking for in rounding.

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    txtTime.Text = Format(Round(CDec(txtTime.Text) / 0.25) * 0.25, "0.00")
    
End Sub

 

[SkipVought]

adding .125 would round up, for instance...
[

   txtTime.Text = Format(Int((CDec(txtTime.Text) + 0.125) / 0.25) * 0.25, "0.00")

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Andrzejek]

Don't forget:

Private Sub txtTime_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case Asc("0") To Asc("9"),[b] [blue]8[/blue][/b]

ao allow the BackSpace in case they want to delete something

Have fun.

---- Andy

 

[RP1America]

Thanks for all the info, folks!

The last thing I need is to know how to be able to enter a decimal as the leading character (Ex: entering ".25" into the field). So I want to be able to enter ".25" rather than "0.25".

Here is my current code:

Private Sub txtTime_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
    txtTime.Text = Format(Round(CDec(txtTime.Text) / 0.25) * 0.25, "0.00")
    
End Sub

Private Sub txtTime_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)

Select Case KeyAscii
    Case Asc("0") To Asc("9")
    Case Asc(".")
        If InStr(1, Me.txtTime.Text, ".") > 0 Then
            KeyAscii = 0
        End If
    Case Else
        KeyAscii = 0
End Select
End Sub