|
|
|
|
|
|
Excel
|
Clear contents of user input range on workbook open
|
|
Ease of Use
|
Easy
|
Version tested with
|
2000, 2002, 2003
|
Submitted by:
|
lucas
|
Description:
|
Data input in a range is used somewhere else in the workbook so the data in the input sheet is no longer needed.
|
Discussion:
|
You have users inputting data in a range and then that data is added to a different sheet. They close the book and you will want the input range cleared for the next user.
|
Code:
|
instructions for use
|
Option Explicit
Private Sub Workbook_Open()
Dim ws As Worksheet, sht1 As Worksheet
Set sht1 = ActiveSheet
Application.ScreenUpdating = False
ActiveSheet.Range("a2:E7").ClearContents
sht1.Activate
Set sht1 = Nothing
Application.ScreenUpdating = True
End Sub
|
How to use:
|
- Right mouse click on the sheet 1 tab and click on view code.
- Paste the code from above into the module for sheet 1
- Close the Visual Basic Editor by clicking on the X in the upper right hand corner of the VBE or by going to File-Close.
|
Test the code:
|
- Type some data into the range A2 through E7 and save the workbook
- Close the workbook and reopen it.
- The range A2 through E7 will be empty.
|
Sample File:
|
clear contents of range on open.zip 7.66KB
|
Approved by mdmackillop
|
This entry has been viewed 162 times.
|
|