Nate, wanna try this one?

elgringo56

Well-known Member
Joined
Apr 15, 2002
Messages
869
You did so good for me the other day, wanna try to help me on this. I want to turn on and turn off Caps lock under macro control so that when I wnat the operator to input in caps I can make her do it. She wont have to remember. Ivan tried to help me but I dont understand what it is he is trying to have me do.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Hi,

You may have good reasons for wanting this, but consider allowing the user to input however s/he feels and then transforming the end result into the case you want.

e.g.

For each cell in Selection
Cell = UCase(Cell)
Next

You will have to play with this a bit, but this will overwrite the cells with ther same value, only in upper case.

An event macro can do this easily.

HTH,
Jay
 
Upvote 0
On 2002-05-11 17:58, elgringo56 wrote:
You did so good for me the other day, wanna try to help me on this. I want to turn on and turn off Caps lock under macro control so that when I wnat the operator to input in caps I can make her do it. She wont have to remember. Ivan tried to help me but I dont understand what it is he is trying to have me do.


There does not appear to be anything wrong with Ivan's code. You just have to install it properly.

However, here's an alternative way to do it without API calls :-

- Add a blank worksheet to your workbook and name it Caps Status. Hide this worksheet.

- Put the following two macros in a normal module and assign them to a button each.

Sub Caps_On()
Sheets("Caps Status").[A1].Value = "Caps On"
End Sub

Sub Caps_Off()
Sheets("Caps Status").[A1].Value = "Caps Off"
End Sub

- Put the following in the input worksheet's module

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
If Sheets("Caps Status").[A1].Value = "Caps On" Then
Application.EnableEvents = False
For Each cell In Selection
If cell.HasFormula = False Then
cell.Value = UCase(cell.Value)
End If
Next
Application.EnableEvents = True
End If
End Sub


Note :
When the Caps_On button is clicked, any input thereafter will automatically be capitals until the Caps_Off button is clicked.
When the Caps_Off button is clicked, input will be displayed in the normal way (i.e. capitals if the CapsLock key on the keyboard has been pressed, otherwise lower case).
 
Upvote 0
Yes, I could do that Jay, and it would work ok. I tried what you have there, I put a lower case value in A1, then did a Range("A1").Select then what you have. Nothing happened.?????????
 
Upvote 0
On 2002-05-11 19:40, elgringo56 wrote:
Yes, I could do that Jay, and it would work ok. I tried what you have there, I put a lower case value in A1, then did a Range("A1").Select then what you have. Nothing happened.?????????

Hi,

Try the following event macro (place in sheet module, not a regular code module.

Code:
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column = 1 And Target.Cells.Count = 1 Then
    If Not Target.HasFormula Then Target = UCase(Target)
End If


End Sub

HTH,
Jay
 
Upvote 0
Jay, I changed your formula a little and it worked.

For Each Cell In Selection
Cell.Value = UCase(Cell.Value)
Next

Thanks, its easy
 
Upvote 0
This lets me control certian cells. What I have is a program that does inventory and it is in English. The input girls are Meican and speak very little english, so I must make it as fool proof as possible to minimize mistakes. this is the last little thing for it. First program I have done and it all seems to work nicely. all you guys have been a really big help to me in this. I may take it up as a hobby. Thanks lots
 
Upvote 0
On 2002-05-11 19:53, elgringo56 wrote:
Jay, I changed your formula a little and it worked.

For Each Cell In Selection
Cell.Value = UCase(Cell.Value)
Next

Thanks, its easy

You were already provided with this solution in one of your ealier postings.
You waste people's time by starting so many different threads about the same thing.
 
Upvote 0
Sorry, but I dont believe I was. If you feel that I am wasting peoples time, I appologize, However, I dont think I got this one earlier.
 
Upvote 0
mdfh, what I got was this, which is not the same, yes, the same line is in it, but it didn't do what I needed. If you wish to watchdog and criticize, at least do it accuratley. I dont appreciate snide comments made when I am trying to work, that is not what this is for. a lot of people tried to help me and I appreciate that very much.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Application.EnableEvents = False
For Each cell In Selection
If cell.HasFormula = False Then
cell.Value = UCase(cell.Value)
End If
Next
Application.EnableEvents = True
End Sub
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top