Page 1 of 2 12 LastLast
Results 1 to 15 of 27
  1. #1
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    spell check & CAPS (XP)

    Hi
    I am trying to find a way to include a spell check button on a userform to invoke the spell checker in Excel to check a textbox/cell or a specified column of the spreadsheet. I can't seem to find any help on this or another little problem, how to change the contents of a cell to all capitals.

    Any help would be appreciated. Thanks.
    <img src=/S/confused3.gif border=0 alt=confused3 width=45 height=45>

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

    Re: spell check & CAPS (XP)

    1. The Range and Selection objects have a CheckSpelling method:

    Range("B35").CheckSpelling
    or
    Selection.CheckSpelling

    Look up CheckSpelling in the Excel VBA help for more options of CheckSpelling.

    2. You can use UCase:

    Range("B3") = UCase(Range("B3"))
    or
    Selection = UCase(Selection)

    (Selection must be a single cell in the last example)

  3. #3
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: spell check & CAPS (XP)

    That's excellent Hans. Thank you.

    I don't know where you are, but I'm in Bangkok and it's just about 6:00am here. I guess you're somewhere in Europe where it must be very late.

    I've looked around for the sort of documented help you're giving, but I must be looking in the wrong place. Is it in the VBA help files somewhere?

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

    Re: spell check & CAPS (XP)

    I'm in The Netherlands, and it's 1 AM here.

    The Object Browser is a good way to explore properties and methods of objects you can use in VBA. Press F2 in the Visual Basic Editor to activate the Object Browser.
    Type the word Range in the search box, then press Enter.
    Scroll down the list of search results until you see Excel - Range; click on this row.
    The properties and methods of Range will be displayed below. If you click on one of them, for example CheckSpelling, you'll see some information in the pane below the list.
    You can press F1 to get online help on the selected item.

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: spell check & CAPS (XP)

    You may also be interested in this tool see <post#=69,696>post 69,696</post#>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

  6. #6
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: spell check & CAPS (XP)

    I am really impressed with the speed and quality of replies to queries on this site. Thanks to all and well done for a terrific user forum.

  7. #7
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: spell check & CAPS (XP)

    Well here I am again.

    The spell checker doesn't work. Because the spell checker checks the cell or worksheet I did a global spell check which checked everything, not useful as there are cell with lots of code and names in. Here's the problem, I can't refer to a cell. The contents of the text comment box are inserted into the worksheet cell
    Range("M" & Me.cboStudent ), cboStudent is an Integer and this reference puts the text into the correct cell M8.
    When I try to spellcheck with Range("M" & Me.cboStudent ).checkspelling it doesn't work. The Range shows the text in the cell and not the reference.

    I've tried everything I can think of, but nothing works. Please help. I am tearing my hair out.

  8. #8
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: spell check & CAPS (XP)

    The CheckSpelling method certainly is weird. Why do they allow it to be used on a range if it always checks the entire sheet?

    Anyway enough whining. What works for me is to have a hidden sheet (in my case Sheet3) and use the ControlSource property of your form's controls to a range on the hidden sheet (in my case Sheet3!A1). Then the Check Spelling button just checks the spelling on the sheet: <pre>Worksheets("Sheet3").CheckSpelling</pre>

    I've attached my test workbook. Just press <Alt><F11> to go to VBA, open the UserForm object, and press <F5> to execute it. HTH --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  9. #9
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: spell check & CAPS (XP)

    <hr>Why do they allow it to be used on a range if it always checks the entire sheet?<hr>

    In XL97 if I spellcheck a range, only the range is checked, it does not check the entire sheet.

    Steve

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

    Re: spell check & CAPS (XP)

    Same for me in Excel 2002 (XP): if I use

    Range("F28:F31").CheckSpelling

    only F28:F31 is checked, even if there are spelling errors in cells outside this range.

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: spell check & CAPS (XP)

    I don't understand what you mean by "doesn't work": could you elaborate?

    When I use a single cell (eg Range("M8").CheckSpelling) it checks the spelling of each word in the cell, M8, and pops up a dialog to ask what to do with each misspelled word.

    Steve

  12. #12
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Springfield, Ohio, USA
    Posts
    2,136
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: spell check & CAPS (XP)

    With XP 2003, unless I coded it wrong, and of course I'm nowhere near a 2003 machine, when I asked it to check A1, it also checked A2, I thought it was weird, but I had never used the spell checker in code before. I'll check it again next Wednesday when I return to work. --Sam
    <font face="Comic Sans MS">Sam Barrett, CACI </font face=comic>
    <small>And the things that you have heard... commit these to faithful men who will be able to teach others also. 2 Timothy 2:2</small>

  13. #13
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: spell check & CAPS (XP)

    Of course it wouldn't surprise me, if in the recoding from earlier versions, this got "messed up"and it works differently now.

    Steve

  14. #14
    Lounger
    Join Date
    Jul 2005
    Location
    Bangkok, Bangkok Metropolis, Thailand
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: spell check & CAPS (XP)

    My apologies to everyone.

    The Excel spreadsheet is exported from third party software as a protected worksheet and as some of the sells are protected the spellchecker is disabled for all the spreadsheet.

    I have unprotected it and it works fine, although it still seems to check the spelling in the cell identified then continues checking the whole sheet. I will work on it now.

    Thanks again

  15. #15
    Silver Lounger
    Join Date
    Jan 2001
    Location
    West Long Branch, New Jersey, USA
    Posts
    1,921
    Thanks
    6
    Thanked 9 Times in 7 Posts

    Re: spell check & CAPS (XP)

    Hans, Sammy,

    Since I never used checkspelling in code before either, I decided to try this on a 2003 machine. Here's my code, written from scratch <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Sub chkspl()
    Range("a1:a2").CheckSpelling
    End Sub

    With that code, only a1 and a2 are checked.

    But if I change the range to "a1", all 3 cells with misspellings are checked (I entered misspelled words in a1:a3 and nothing else).

    Both observations are true whether this is done in code, per above, or from the Tools menu. In the latter case, if you have >1 cell selected, only those are checked. If you have 1 cell selected (regardless of whether it's one of a1:a3 or some other cell), I get a message "Do you want to continue checking at the beginning of the sheet?".

    HTH

    Fred

Page 1 of 2 12 LastLast

Posting Permissions

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