+ Reply to Thread
Results 1 to 9 of 9

Use VBA to Spellcheck several cells only

  1. #1
    Registered User
    Join Date
    07-22-2013
    Location
    Home Counties
    MS-Off Ver
    Excel 2010
    Posts
    14

    Use VBA to Spellcheck several cells only

    Hello.

    I have a spreadsheet where I would like a macro that can spellcheck three cells (E35,E82, and B89), but I'm having trouble programming this.

    Using code from this OzGrid Forum post, I came up with:

    Please Login or Register  to view this content.
    This does Spellcheck the correct cells, but after it checks all three, it brings up three consecutive "Do you want to continue Checking at the beginning of the sheet?" Prompts

    Researching this, I found these ExcelForum and DailyDose posts, which I used to make this code:

    Please Login or Register  to view this content.
    This code doesn't pop up any boxes at all, even if there's mis-typed words in Cells E35, B82, and B89, even though they are picked up correctly from a GUI F7 Spellcheck.

    I have a feeling the code I need lies somewhere between these two, but I haven't been able to code or google-Fu my way to a solution. Can anyone point me in the right direction?

  2. #2
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Use VBA to Spellcheck several cells only

    When you tested the first code did you place misspelled words in other cells?

    That code prompts you three times because Range("E35").CheckSpelling is going to check every cell after E35 (hitting all three misspelled cells) then it will display the alert. If you hit no it goes to the next line and checks spelling for everything after B89... so on and so forth.

    Are there a limited number of input options into those three cells? Could you build a dictionary of possible entries?
    Last edited by Solus Rankin; 04-03-2014 at 09:04 AM.
    Thanks,
    Solus


    Please remember the following:

    1. Use [code] code tags [/code]. It keeps posts clean, easy-to-read, and maintains VBA formatting.
    Highlight the code in your post and press the # button in the toolbar.
    2. Show appreciation to those who have helped you by clicking below their posts.
    3. If you are happy with a solution to your problem, mark the thread as [SOLVED] using the tools at the top.

    "Slow is smooth, smooth is fast."

  3. #3
    Registered User
    Join Date
    07-22-2013
    Location
    Home Counties
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Use VBA to Spellcheck several cells only

    Thanks for the reply, Solus.

    For the first code, you are right: It does spellcheck all the other cells after the first one (both the other two in the range, and the other non specified cells)

    This is the behaviour I'm trying to suppress: I want it to check only those three cells, ignoring all other cells, and not giving the "continue Checking at the beginning" cells.

  4. #4
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Use VBA to Spellcheck several cells only

    Can you provide a list of possible entries into those 3 cells that the entries could be checked against?

  5. #5
    Registered User
    Join Date
    07-22-2013
    Location
    Home Counties
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Use VBA to Spellcheck several cells only

    They're Freeform cells: Anything could be typed into them, which is why I want to spellcheck them

  6. #6
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Use VBA to Spellcheck several cells only

    Maybe?
    Please Login or Register  to view this content.

  7. #7
    Registered User
    Join Date
    07-22-2013
    Location
    Home Counties
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Use VBA to Spellcheck several cells only

    Thanks for the code, Solus.

    it seems to be explicitly checking just those three cells. It's popping up [Cell] is spelled incorrectly, rather than the Suggest/Change/Ignore Spelling Window - can that window be displayed?

  8. #8
    Forum Expert Solus Rankin's Avatar
    Join Date
    05-24-2013
    Location
    Hollywood, CA
    MS-Off Ver
    Win7 Office 2010 VS Express 2012
    Posts
    2,655

    Re: Use VBA to Spellcheck several cells only

    The spellcheck dialog box is not one you can call in this manner. You could set up a custom userform to mimic the functionality.

  9. #9
    Registered User
    Join Date
    07-22-2013
    Location
    Home Counties
    MS-Off Ver
    Excel 2010
    Posts
    14

    Re: Use VBA to Spellcheck several cells only

    Thanks for the info, Solus.

    I've been away for the week, but I had another look at it. I Recorded a macro by highlighting the cells I want, which (With a bit of tidying up), gave this:

    Please Login or Register  to view this content.
    It looks like the merged cells were confusing things - This code gives the desired behaviour: Spell checks the three "Cells" using the normal spellcheck window, and does not prompt to check other cells.

    However, this stops working when the worksheet is protracted: It ends with "Run-Time error 1004 CheckSpelling Method of Range Class failed"

    When I check the merged cell properties, they aren't set to locked or hidden. Can anyone suggest what to look into to make that code snippet work with protection?

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 10-26-2010, 01:31 PM
  2. [SOLVED] MS words spellcheck
    By Webtekr in forum Word Programming / VBA / Macros
    Replies: 1
    Last Post: 03-21-2009, 07:31 AM
  3. Excel 2007 : Spellcheck
    By jonnygrim in forum Excel General
    Replies: 2
    Last Post: 07-23-2008, 05:48 PM
  4. Spellcheck Macro Help
    By Kevin Ward in forum Excel General
    Replies: 2
    Last Post: 02-24-2006, 08:14 PM
  5. Spellcheck
    By zt in forum Excel General
    Replies: 3
    Last Post: 07-08-2005, 05:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1