Results 1 to 10 of 10
  1. #1
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Format cell (2002)

    Can I format a cell to specify that each word must start with an uppercase letter, all else lowercase, regardless of how entered? I've tried searching but not found owt yet.

    So if I entered vEGAS nATH, would auto change to Vegas Nath

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format cell (2002)

    There is no such cell format.

    You can use Brooke's ConvertToProperCase macro from <post#=51226>post 51226</post#> to convert the cells in the current selection to proper case.
    Or you could adapt his code to be used in the Worksheet_Change event procedure in the worksheet module.

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format cell (2002)

    If you're using VBA anyway, you can use the StrConv function directly:

    Worksheets("MON ADJ FORM").Cells(195, 4).Value = StrConv(TextBox1.Value, vbProperCase)
    Worksheets("MON ADJ FORM").Cells(195, 10).Value = StrConv(TextBox2.Value, vbProperCase)

  4. #4
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cell (2002)

    Thanks Hans, your suggestion led me to the following:

    I have a userform to capture 2 names and then the following:

    Worksheets("MON ADJ FORM").Cells(195, 4).Value = TextBox1.Value 'D195
    Worksheets("MON ADJ FORM").Cells(195, 10).Value = TextBox2.Value 'J195

    Instead of using D195 and J195, I can alter to X1 and X2 and then use

    D195 =proper(X1)
    J195 =proper(X2)

    Edited:

    This works fine for me, but was wondering if I am missing a more obvious route? eg: = TextBox2.proper_Value <img src=/S/question.gif border=0 alt=question width=15 height=15>

  5. #5
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cell (2002)

    Thankyou for responding to my edit, before I edited it!! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format cell (2002)

    That's the power of Woody's Lounge! <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>

  7. #7
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cell (2002)

    Hans,
    My preference is to have the worksheet locked, but when the userform writes to the sheet, I get an error.
    Is there a way to lock the sheet to the user, but leave unlocked to vba?
    Thanks

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format cell (2002)

    You can do so by protecting the worksheet using VBA:
    <code>
    ActiveSheet.Protect UserInterfaceOnly:=True
    </code>
    for the currently active sheet, or
    <code>
    Worksheets("MON ADJ FORM").Protect UserInterfaceOnly:=True
    </code>
    for a specific sheet. Specifying UserInterfaceOnly as True means that VBA code can still modify the cells of the worksheet, even if their Locked property is ticked.

  9. #9
    Bronze Lounger
    Join Date
    Sep 2007
    Posts
    1,203
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Format cell (2002)

    Hans, It asks me for the password?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Format cell (2002)

    Protect asks you for a password? I'd have to know more about the workbook to explain that.

Posting Permissions

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