ThisWorkBookLocation = ActiveWorkbook.Path Private Sub Workbook_AfterSave(ByVal Success As Boolean) This procedure displays a message confirming the file name and path of the workbook after it has been saved. MsgBox "Welcome to worksheet: " & Sh.Name Private Sub Workbook_SheetActivate(ByVal Sh As Object) This procedure displays a welcome message when any sheet is activated. The Sheet Activate event also creates an argument called Sh which represents this worksheet that has just been activated. Range("A1") = "Sheet Created by " & UserName & " on " & Now Private Sub Workbook_NewSheet(ByVal Sh As Object) The date and time the sheet is created is displayed in cell A1. The user input is stored in a variable called SheetName. This example procedure asks the user to name the sheet when it is created.
VISUAL BASIC FOR EXCEL ON CHANGE EVENT CODE
In the example code below we use Sh to name the new sheet. Sh represents the worksheet that has been created. The New Sheet event creates an argument called Sh. MsgBox "It's Monday again, you need to file the XYZ Report" 'If weekday is Monday display XYZ report reminder StatusBar = "Good Evening " & UserName _ ' Display message in status bar and window caption MsgBox "Hi There " & UserName & ", the time and date is is " & Now It also presents the user with a welcome message on opening the workbook. This procedure shows a welcome message in the status bar and window caption. The examples below are intended to illustrate the workings of each event rather than to provide commercially useful procedures. Occurs when any workbook window is resized.Ĭode for workbook events are saved in the ThisWorkbook module.Īt the top of the code window change General to Workbook in the first drop down menu.įrom the second drop down menu at the top of the code window, select which event you want to write a procedure for. Occurs when any workbook window is deactivated. Occurs when any workbook window is activated. Does not occur if the selection is on a chart sheet. Occurs when the selection changes on any worksheet. Occurs when cells in any worksheet are changed by the user or by an external link. Occurs when a new sheet is created in the workbook. Occurs when a new chart is created in the workbook. Occurs before the workbook (or anything in it) is printed. If the workbook has been changed, this event occurs before the user is asked to save changes. Point taken, I'll watch out for this in future.Here are some commonly used workbook events. Note the use of Integer/Long data types for Row counters !! Object doesn't support this property or method". Now if I make any changes in the sheet (not just in column E), I get the error message "Run-time error 438. I've tried changing the reference to say Activesheet.Column. If I make any changes in the sheet (not just in column E), I get an error message saying that. Row onto the end of SpecialCells(xlCellTypeLastCell)īut it still isn't quite working.
Line 6 appears to have a big space at the end of it, before the.
0 kommentar(er)
