accepting numbers only in user forms (VBA)

[VBAmalta89]

I am doing some data validation on my user forms in VBA. I want the text boxes i have available to accept only numbers.

I have managed to do this for normal text boxes, however, I cannot do it for text boxes that lie within a frame. The line of code I am using is as follows for the normal text boxes:

Private Sub DisposeWood_Change()

OnlyNumbers

End Sub

Private Sub OnlyNumbers()

If TypeName(Me.ActiveControl) = "TextBox" Then

With Me.ActiveControl

If Not IsNumeric(.Value) And .Value <> vbNullString Then

MsgBox "Sorry, only numbers allowed"

.Value = vbNullString

End If

End With

End If

End Sub

How do i alter this to only accept numbers in text boxes which are inside frames?

 

[Norie]

I think the problem is because you are using ActiveControl which is a property of both a form and a frame

This might illustrate what I mean.

Private Sub TextBox1_Change()

    MsgBox Me.ActiveControl.Name

    If TypeName(Me.ActiveControl) = "Frame" Then
        MsgBox Me.Frame1.ActiveControl.Name
    End If

End Sub

It Textbox1 is in a frame you'll get 2 messages,, if it isn't you'll only get one.

 

[VBAmalta89]

I dont quite understand the solution u are giving me though?

 

[Norie]

It's not a solution, I was trying to explain why the code you posted wasn't working.

Apologies.

I would actually recommend using entirely different code to check for numeric.

That's mainly because, as you've seen, you can't rely on using ActiveControl.

If you don't have too many textboxes you could add a simple check in each one's Exit event.

Something like this perhaps.

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)

       If Not IsNumeric(TextBox1.Value) Then
               MsgBox "Only numbers allowed"
               Cancel = True
      End If
 
End Sub

There's probably quite a few other options to do validation.

 

[VBAmalta89]

ok so this seems to be working, however it is prompting mw with the message once i exit the user form.

Is there a way i can make it prompt once a letter is entered instead of a number in a text box?

 

[Norie]

A particular letter or character?

You could check that 1 character has been entered using Len but if you do want to restrict that to A-Z or something similar it would be slightly more complicated.

If did want that then perhaps you could try a combobox which only has the allowed characters listed?

The user could still type the value in and you could can easily check what they've typed is on the list.

 

[VBAmalta89]

All characters must be rejected and numbers only need to be accepted.

I am new to VBA so i would appreciate if you could explain what you meant in your previous message more clearly.

 

[Norie]

Oops, my bad.

I though you wanted a character to be entered, not a number.

Using IsNumeric will reject anything entered that has a character.

If you want to check every character as it's entered then you'll find code for that on the board.

 

[Krishnakumar]

Hi,

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
    Case 46, 48 To 57
    Case Else
        KeyAscii = 0
        MsgBox "Only numbers allowed"
End Select
End Sub

Remove 46 if you don't need a decimal.

HTH

 

[VBAmalta89]

thanks krishnakumar!

Finally managed to solve this problem once and for all