How to force a specific entry in a textbox/combobox

[illectric]

I would like to force a certain number format as the input so i can always be sure of what type of format i am dealing with.

One is with a Date ComboBox. I would like it in a specific format being that the user cannot enter anything but the format that i request.

Second is some textboxes that i would like a money figure entered and nothing but.

If users try to enter something different then i throw an error msgbox and make them try again.

Thanks.

 

[PHV]

Have you tried to play with the Change events of your controls or with the BeforeUpdate event of your form ?

Hope This Help
PH.

 

[Nelviticus]

This is a partial solution that I found elsewhere, can't remember the exact place. It works by nullifying any keypresses in your text box that don't meet your criteria. Here's the main function:

Function OnlyDateKeys(KeyAscii As Integer) As Integer
    
    Select Case KeyAscii
        
        Case 8          ' Backspace
            
            ' Do nothing
            
        Case 47         ' Forward slash
            
            ' Do nothing
            
        Case 48 To 57   ' Digits
            
            ' Do nothing
            
        Case Else:
            
            KeyAscii = 0 ' Reject everything else
            
    End Select
    
    OnlyNumericKeys = KeyAscii
    
End Function

NB this assumes that you want the date in a format like dd/mm/yy, rather than dd.mm.yy - if you want something else, you'll need to change the code for which keypresses (ASCII values) are accepted.

Next, you have to call the function from your text box's

KeyPress

event like this:

Private Sub txtDate_KeyPress(KeyAscii As MSForms.ReturnInteger)
    
    KeyAscii = OnlyNumericKeys(Int(KeyAscii))
    
End Sub

(NB - depending on your version of Office, the KeyPress function might have to be slightly different. Use this instead if it doesn't work:

Private Sub txtDate_KeyPress(KeyAscii As Integer)
    
    KeyAscii = OnlyNumericKeys(KeyAscii)
    
End Sub

)

You could write a similar function (

OnlyCurrencyKeys

, for instance) to be called from your currency input boxes - the ASCII value for the dollar sign is 36, for the sterling sign is 163, for the comma is 44 and for the period is 46.

You'd need to write extra functions to validate what the user had input, as they could still enter a date like '99993///4////' - the above only restricts them to pressing certain keys. Something like this should do it:

Private Sub txtDate_AfterUpdate()
    
    If Not IsDate(txtDate) Then
        
        MsgBox "Please enter a valid date."
        
        txtDate = ""
        
    End If
    
End Sub

Hope that helps.

Nelviticus