Solved how i can use following macro by pasting in personal xlx

March 28, 2014 at 10:49:25
Specs: Windows 7
This will SpellCheck each word in the changed cell after you hit the Enter or Tab key, etc.
Private Sub Worksheet_Change(ByVal Target As Range)
Application.DisplayAlerts = False
Application.DisplayAlerts = False
End Sub

Now i want to use it for every new sheet but dont want to copy and paste is every time. thank you

See More: how i can use following macro by pasting in personal xlx

March 28, 2014 at 13:43:54
✔ Best Answer
re: "This will SpellCheck each word in the changed cell after you hit the Enter or Tab key, etc."

It will also SpellCheck every formula, number, URL, or anything else you enter in a cell. That's not something I would want in every spreadsheet I worked with.

re: Application.DisplayAlerts = False

Don't you think you ought to set DisplayAlerts to TRUE before you exit the sub? I'm not sure why you are setting DisplayAlerts to FALSE twice. Seems redundant and dangerous.

As far as actually running the code from the personal.xls workbook, I don't think it can be done. A Worksheet Event macro (e.g. Worksheet_Change) has to reside in the Sheet Module for the worksheet you want it to run in. Even if you stored the code as a "regular" macro in personal.xls, you'd need a Worksheet_Change event in every sheet to call the macro.

Private Sub Worksheet_Change(ByVal Target As Range)
   Application.Run "personal.xls!My_Spell_Checker"
End Sub

There are two ways that I think of to accomplish your goal, assuming you really want to SpellCheck every cell every time you change it, which I sure wouldn't. I've never tried either method and don't have time to try it right now...maybe this weekend.

Method 1:

1 - Open a blank workbook
2 - Store a copy of your SpellCheck macro in the sheet module of each sheet.
3 - Save the workbook in your XLSTART folder

Any workbook stored in XLSTART will open every time you open Excel. Use that workbook as your "new workbook" from now on and the code will be available in all sheets. If you want to add a new sheet to any workbook, copy an existing sheet instead of adding a new one and the code will be copied along with it.

Method 2 - This is something I might try this weekend if I have time

1 - Store the SpellChecker code in the Sheet1 module of your personal.xls
2 - Create another macro that copies the code from that sheet module to the sheet modules of the ActiveWorkbook. (See link below)
3 - Store that macro (e.g. CopyCode) in your personal.xls workbook
4 - Assign the CopyCode macro to a button on your toolbar so that is available whenever you open Excel
5 - Whenever you open a new workbook, click the button to run CopyCode macro to copy the SpellCheck macro to each sheet in the ActiveWorkbook.

This link hints at copying VBA code from one sheet module to another. Perhaps some modifications will allow it to copy code from your personal.xls workbook to a sheet module in the ActiveWorkbook.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

Report •

March 31, 2014 at 00:04:29
Thanks Derby i am using the first method hope it will work for me...:)

Report •
Related Solutions

Ask Question