Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 3 123 LastLast
Results 1 to 10 of 23

Thread: Nate, wanna try this one?

  1. #1
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3

    Join Date
    May 2002
    Posts
    8
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  4. #4
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  5. #5
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

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

    Thanks, its easy

  7. #7
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  8. #8

    Join Date
    Apr 2002
    Posts
    15
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  9. #9
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    Board Regular
    Join Date
    Apr 2002
    Location
    Puerto Vallarta, Mexico
    Posts
    869
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •