Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
A macro that shows updates - OVER MY HEAD
Hello,
I am trying very unsuccessfully to modify a macro that I found in another post to work with my worksheet. My worksheet uses columns A-M. The way they are set up, A is the date column. Columns B&C are "booked" and "estimated" for Q1 respectively. Columns D&E are "booked" and "estimated" for Q2, F&G are "booked" and "estimated" for Q3, and H&I are "booked" and "estimated" for Q4. Columns J&K are totals for the "booked" and "estimated". Column L is the combined total and Column M is for comments. Ideally, what I would like is a macro that inserts a line at the bottom of the spreadsheet and lists the updates as follows: Column A would show the date of the change Columns B-I would show any NEW values Columns J-L would show totals Column M would show the username of the person who updated the sheet There would be a separate line for all changes made since the last time the macro was run. Here is the macro that I tried unsuccessfully to modify: Private Sub Worksheet_Change(ByVal Target As Range) 'Column to be watched Const sWatch As String = "J" 'Column of reference data that will show on Track sheet Const sRef As String = "A" Dim rWatch As Range Dim rCell As Range Dim sUser As String Dim lOffset As Long Set rWatch = Intersect(Target, Columns(sWatch)) If rWatch Is Nothing Then Exit Sub sUser = Environ("username") lOffset = Columns(sRef).Column - Columns(sWatch).Column With Worksheets("DUEDATE-CONT COMPLIANCE") ActiveSheet.Unprotect For Each rCell In rWatch With .Cells(Rows.Count, "A").End(xlUp).Offset(1, 0) .Value = rCell.Offset(0, lOffset) .Offset(0, 1).Value = Now .Offset(0, 2).Value = sUser .Offset(0, 3).Value = rCell.Value ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingColumns:=True, AllowFormattingRows:=True, _ AllowInsertingRows:=True, AllowDeletingColumns:=True, AllowDeletingRows:= _ True, AllowSorting:=True, AllowFiltering:=True End With Next rCell End With End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
My excel macro recorder no longer shows up when recording macro | Excel Discussion (Misc queries) | |||
Create or change color of a (line with arrow head) with a Macro en | Excel Programming | |||
Macro updates | Excel Programming | |||
Downloading updates a message box shows up saying SKU011.CAB file. | Excel Programming |