VBA solution
VBA solution
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 3 of 3

Thread: VBA solution

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Hi All
    Problem: I wish to record the changes to certain cells in a worksheet when worksheet.save. I wish to make a table on a separate sheet listing the cells (or named ranges) that I wish to monitor. So each time the workbook is saved, the table creates a new line of information (with the date) Therefore I end up with a table of information on a separate sheet that records the contents in certain cells on different dates.
    Reason: I wish to monitor changes made by a small number of users who will be given the password to the protected worksheet (so only they can save changes). For this reason I cannot use the Share Workbook - Track Changes functionality in Excel.
    Solution: By creating a separate table referencing the monitored cells (or named ranges) and using the 'offset' property/method (to create another line in the table)?? when worksheet.save But I am no good at writing such code. Any help/ideas appreciated!!!!
    Thanks for you time.




  2. #2
    MrExcel MVP Damon Ostrander's Avatar
    Join Date
    Feb 2002
    Location
    Denver, Colorado USA
    Posts
    4,240
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default


    Hi Lucky,

    Lets just say for example that the list of cells or named ranges (I will assume each named range consists of one cell) you want to save are in cells A1:An (where n is any number) in worksheet "Save Cells", and the worksheet containing the saved lines is named "Data Log". Your workbook Save event should look something like this:

    Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim SWS As Worksheet
    Dim LWS As Worksheet
    Dim NewRow As Long
    Dim iRow As Integer
    Set SWS = Worksheets("Save Cells")
    Set LWS = Worksheets("Data Log")
    NewRow = LWS.[a65536].End(xlUp).Row + 1
    'put date-time stamp in first column
    LWS.Cells(NewRow, 1) = Now()
    For iRow = 1 To SWS.[a65536].End(xlUp).Row
    'use iRow on SWS to determine the column on LWS
    LWS.Cells(NewRow, iRow + 1) = Range(SWS.Cells(iRow, 1).Value)
    Next iRow
    End Sub

    It is important that if the ranges you are logging are not always going to be on the active worksheet at the time the save occurs that the ranges specified on the Save Cells worksheet specify the worksheet as well as the cell, e.g., Sheet1!B4, or Sheet1!GrandTotal...
    Keep Excelling.

    Damon

    VBAexpert Excel Consulting
    LinkedIn Profile http://www.linkedin.com/pub/damon-ostrander/7/79/a93
    AllExperts Profile http://www.allexperts.com/ep/1059-30...-Ostrander.htm

  3. #3
    New Member
    Join Date
    Feb 2002
    Posts
    18
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thank you Damon for your very precise response.
    A bit of personalising and it has worked a treat!!
    Cheers!

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com