by

Edit Macro Button In Excelleadingdwnload



Edit Faster with Free Macros

The form control button (push button) is assigned to run a macro. You can right click on the button to edit the text or to format it. This is Excel 2010 screenshot. I go over how to create a macro in Excel, how to edit it in Visual Basic and how to attach it.

8 August, 2010

By Paul Beverley

The life of a professional editor is great: I sit in the comfort of my own home reading interesting books – and people pay me to do it! But some bits of the job are repetitious and tedious, and that's where macros come in: they do the boring bits and let me concentrate on engaging with the text. So, over the years, I have developed macros that help with a wide range of common tasks. And they're now available in a free downloadable book, Macros for Editors.

But Aren't Macros Really Complicated?

No. A macro is just a script that automates repeated tasks in Word documents. And you don't have to write macros to use macros. My book offers a range of over 400 macros written by editors for editors. To use them, you just have to learn how to load a macro into Word - and the book tells you how.

Just a Taste

Here are a few of the most widely-used macros from the book. Imagine...

Macros
  • You're busy typing, and suddenly you realize that what you're typing is gobbling up the text ahead of it. Yes, you have accidentally hit the <Insert> key, and it is now in overtype mode. Bother! But this can be avoided by installing the OverTypeBeep macro.
  • How many times a day do you add a 'that' to a sentence? Or change 'that' to 'which' or vice versa? One macro does it all – it adds 'that' if there isn't one or it switches between 'that' and 'which'. The same macro also automatically changes 'like' to 'such as', 'England' to 'the UK', 'as' to 'because', 'per cent' to '%', etc. Indeed, you can add whatever pairs of words you want it to switch.
  • How often do you change a comma into a full stop and make the next word start with a capital, or do the reverse, or change it to a semi-colon or a colon? In each case, all you do is press Ctrl and Alt with the desired punctuation mark and let the macros sort out the case of the first letter of the following word.
  • You're reading some text and you see a word or a phrase and think, 'How many times does this occur?' If you run CountPhrase, it will answer that question, doing both case-sensitive and case-insensitive counts.
  • You have a book made up of chapters 1 to 10, and an appendix, all in separate files. Even if the client wants them kept in separate files, wouldn't it be helpful to have all the text in one file to make it easy to search for something? And if you're using PerfectIt, turning the text into one file massively increases the power of PerfectIt's consistency checking. One macro combines them for you. (And there's another that splits a big file up into chapters, if you need that.)
  • Do you need to take all the pictures and diagrams out into a separate file, leaving behind call-outs: 'Figure 1 here' etc.? FigStrip does that. And if you have to do the same with tables, there's TableStrip.
  • You see a word / phrase and you want to look back to the previous time it was used. Select it and, with one keypress, InstantFind will take you straight to the previous occurrence of that word / phrase. What's more, the word / phrase is now in the Find box, so by clicking Ctrl-PageUp and Ctrl-PageDown, you can jump through each and every occurrence in the text.
  • You're reading the text and you see a number – '6', say – coming up and you need it to be 'six', so you click somewhere on the line and run the NumberToText macro, and it's instantly changed. Then the text says 'I've got 3 As, 2 Bs and 1 C' – so click, click, click (run the macro three times) and it becomes 'I've got three As, two Bs and one C'.

Edit A Button In Excel

I hope these fire your imagination and encourage you to look at the rest of the macros. By picking the ones that best suit your way of working, you can save yourself a lot of time, and they will enable you to produce a more consistent output, especially in combination with PerfectIt.

Paul Beverley is an independent freelance editor who runs macro training sessions in the UK and overseas. His book, Macros for Editors, is available free of charge at www.archivepub.co.uk/TheBook.

  • Excel Macros Tutorial
  • Excel Macros Useful Resources
  • Selected Reading

Button

You can create a macro by writing the code in the VBA editor. In this chapter, you will learn where and how to write the code for a macro.

VBA Objects and Modules

Before you start coding for a Macro, understand the VBA Objects and Modules.

  • Open the macro-enabled workbook with your first macro.
  • Click the DEVELOPER tab on the Ribbon.
  • Click Visual Basic in the Code group.

The VBA editor window opens.

You will observe the following in the Projects Explorer window −

  • Your macro enabled workbook – MyFirstMacro.xlsm appears as a VBA Project.

  • All the worksheets and the workbook appear as Microsoft Excel Objects under the project.

  • Module1 appears under Modules. Your macro code is located here.

  • Click Module1.

  • Click the View tab on the Ribbon.

  • Select Code from the dropdown list.

The code of your macro appears.

Creating a Macro by Coding

Create A Macro Button In Excel

Next, create a second macro in the same workbook – this time by writing VBA code.

You can do this in two steps −

  • Insert a command button.

  • Write the code stating the actions to take place when you click the command button.

Inserting a Command Button

  • Create a new worksheet.

  • Click in the new worksheet.

  • Click the DEVELOPER button on the Ribbon.

  • Click Insert in the Controls group.

  • Select the button icon from Form Controls.

  • Click in the worksheet where you want to place the command button.
  • The Assign Macro dialog box appears.

The Visual Basic editor appears.

Can't Edit Excel Macro

You will observe the following −

  • A new module – Module2 is inserted in the Project Explorer.
  • Code window with title Module2 (Code) appears.
  • A sub procedure Button1_Click () is inserted in the Module2 code.
Edit Macro Button In Excelleadingdwnload

Coding the Macro

Your coding is half done by the VBA editor itself.

Can

For example, type MsgBox “Best Wishes to You!” in the sub procedure Button1_Click (). A message box with the given string will be displayed when the command button is clicked.

That’s it! Your macro code is ready to run. As you are aware, VBA code does not require compilation as it runs with an interpreter.

Running the Macro from VBA Editor

You can test your macro code from the VBA editor itself.

  • Click the Run tab on the Ribbon.

  • Select Run Sub/UserForm from the dropdown list. The message box with the string you typed appears in your worksheet.

You can see that the button is selected. Click OK in the message box. You will be taken back to the VBA editor.

Excel Assign Macro To Button

Running the Macro from Worksheet

You can run the macro that you coded any number of times from the worksheet.

  • Click somewhere on the worksheet.
  • Click the Button. The Message box appears on the worksheet.

You have created a macro by writing VBA code. As you can observe, VBA coding is simple.