5.

Task Five: Deleting the Worksheet

You imported the text file worksheet so that you could fill in the labels and add a column of dates before appending the data to the database. Once the data is safely appended, you don't need the imported worksheet any more.

Create a Macro to Delete the Active Worksheet

Record Macro

  1. Activate an expendable worksheet, click the Record Macro button, type DeleteSheet as the macro name, and then click OK.

  2. Choose the Edit menu, click Delete Sheet, and then click Delete when asked to confirm.

    Stop Recording

  3. Click the Stop Recording button to turn off the recorder.

  4. Select another expendable worksheet, and step through the DeleteSheet macro:

Sub DeleteSheet()     ActiveWindow.SelectedSheets.Delete End Sub
  1. Click Delete when asked to confirm the deletion.

The recorded statement refers to the 'selected sheets of the active window' because it's possible to select and delete multiple sheets at the same time. (Press and hold the Ctrl key as you click several sheet tabs to see how you can select multiple sheets. Then click an unselected sheet without using the Ctrl key to deselect the sheets.) Because you're deleting only one sheet, you could change the statement to ActiveSheet.Delete if you wanted, but that isn't necessary.

The only problem with this macro is that it asks for confirmation each time you run it. When the macro deletes the imported sheet as part of the larger project, you would prefer not to be prompted.

Make the Macro Operate Quietly

The Delete method does not have an optional argument that eliminates the confirmation prompt. You must add a new statement to turn off the warning.

Run Macro

  1. Click the Run Macro button, select the DeleteSheet macro, and click Edit.

  2. Insert a new line after the comments following the statement Sub DeleteSheet() and then enter this statement:

    Application.DisplayAlerts = False

    DisplayAlerts is a property of the Excel application. When you set the value of DisplayAlerts to False, any confirmation prompts that you would normally see are treated as if you had selected the default answer. The DisplayAlerts setting lasts only until the macro finishes running; you don't need to set it back to True. However, you do need to be careful not to run this macro when the active sheet is something you care about. You should also, naturally, save your work often.

    Tip 

    The Auto List Members feature will help you type the words DisplayAlerts and False. When you select a word in the list, press the Tab key to finish entering the word into the statement.

  3. Save the Chapter02 workbook.

  4. Select an expendable worksheet, and run the DeleteSheet macro.



Microsoft Excel 2002 Visual Basic for Applications Step by Step
FrameMaker 6: Beyond the Basics
ISBN: 735613591
EAN: 2147483647
Year: 2005
Pages: 122
Authors: Lisa Jahred

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net