MrExcel Publishing
Your One Stop for Excel Tips & Solutions

CAPS


Posted by Robb on February 06, 2002 10:59 AM

How do I restrict data entry on worksheets to CAPS? Do I use an API call? If so what is it? Thanx.


Posted by Mark O'Brien on February 06, 2002 11:52 AM

Why do you want to restrict data entry to be all CAPS?

If you are using VBA anyway you could use the "UCase" function to convert any text to all CAPS in your code and on the spreadsheet.

HTH

Posted by Steve Hartman on February 06, 2002 11:56 AM

Data Validation

Select all the cells. click Data > Validation. Choose Custom and enter =exact(upper(A1),A1). Click Ok

Posted by Robb on February 06, 2002 12:32 PM

Re: Data Validation

Thanx, but this is what I'm really looking for: User types in data onto my form and typed data is in UPPER CASE regardless if the Caps Lock is on or not. (I can do this in VB6 using an API call but not in Excel).

Posted by Robb on February 06, 2002 12:37 PM

Thanx, Yes, I'am using VBA. Why Caps you ask? Because my boss wants it that way.

This is what I'm looking for: User types in data onto my form and the typed data is in UPPER CASE regardless if the Caps Lock is on or not. (I can do this in VB6 using an API call but not in Excel).

Posted by Mark O'Brien on February 06, 2002 1:37 PM

If you can do this in VB6 using an API call then you should be able to use the same API call in VBA. The VB6 engine is actually based on VBA so any API functions should work in both.

OR. Perhaps more simply, I've created a userform and stuck on a text box, TextBox one and put this code in the change event:

Private Sub TextBox1_Change()
Me.TextBox1.Text = UCase(Me.TextBox1.Text)
End Sub

This would be cumbersome if you have a lot of textboxes on the form, but is definitely a means to an end.

Bosses can be a complete pain in the a**e.