Nate, wanna try this one?

[elgringo56]

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.

[Jay Petrulis]

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

[Lo Lun To]

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).

[elgringo56]

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.?????????

[Jay Petrulis]

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.

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

[elgringo56]

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

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

Thanks, its easy

[elgringo56]

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

[mdfh]

[quote]
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

[quote]


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.

[elgringo56]

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.

[elgringo56]

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