VBA Macro Code To Log Changes Of Range

  • Hello, I was desperately needing some help with this. I want to log changes from a range of cells in sheet 1 lets say A1:A255. I want those cells to be recorded in sheet 2 to cells A1:A10. I only want the last 10 changes from sheet 1 to be displayed in sheet 2. When cell A10 contains data I want cell A1 to be cleared and to start over with the next change. I have been searching for days for answers.

  • Re: Log File


    The following goes in the code module for the sheet you want to monitor. IF you have any existing code for the WorkSheet_SelectChange and WorkSheet_Change events, they will have to be incorporated.



    Quick and dirty, but essentially works - there may be minor issues



    This needs a sheet named Log to work.


    Basic outline:
    Every time a cell is selected, the value is stored in the variable vOldData
    If a cell is changed, it checks to see if in the range A1:A10
    If so, the LogChange procedure is called
    LogProcedure gets the last used column on the Log for the row changed
    if this is more than the number of changes to record, it gets set to 2
    vOldData stored in Cell (Row, Col)

  • Re: VBA Macro Code To Log Changes Of Range


    After re-reading, that is possibly not what you want, but it should be easy enough to modify the LogChange procedure to do exactly what you need.


    I would change it, but using a mobile phone now...


    Also, this seems to be essentiality the same as your post yesterday. Duplicate posts can waste time when answers are provided in both threads so your other thread has been locked. Please post back if they are different issues and the other thread will be re-opened.

  • Re: VBA Macro Code To Log Changes Of Range


    Quote from cytop;609047

    After re-reading, that is possibly not what you want, but it should be easy enough to modify the LogChange procedure to do exactly what you need.


    I would change it, but using a mobile phone now...


    Also, this seems to be essentiality the same as your post yesterday. Duplicate posts can waste time when answers are provided in both threads so your other thread has been locked. Please post back if they are different issues and the other thread will be re-opened.


    Hey man, thanks so much. I am fairly new to VBA but I understand the code. I am sorry for the duplicate post, the initial post I felt was way too difficult to understand without having the actual spreadsheet. So I made new post with a more simple request. I'm going to try this out and we will see what happens. Again thank you!

  • Re: VBA Macro Code To Log Changes Of Range


    Best in mind the code adds the old value across the columns on the same row on the log sheet as was changed on the original sheet.


    That needs changing to insert down the rows of column A - it's a fairly minor change. If you can't make the change and nobody else picks this up I'll modify out next time on the computer (later this afternoon)

  • Re: VBA Macro Code To Log Changes Of Range


    I've attached a screenshot of what I'm doing. As you can see in the Hosts sheet, I click Check Status and it pings over 100 switches that I have on my network. If a switch goes down it records the time it was last down. In this example it would be 192.168.1.9. If that switch is down I want the name of the switch to be displayed on the Network Monitoring Sheet in the Log section (IE "Switch 1" or "Switch 2". The range of the log section is A22:A40 for column 1 and N22:N40 for column 2. The cells are merged. Your previous script was close to what I need but it doesn't work unless I select the cells and as you can see in the picture I don't select any cells personally, I just let the script do it's thing. <IMG>http://i93.photobucket.com/alb…haloz/myspace/Example.jpg</IMG> I'm not lazy or anything I just have no idea how I would even begin to do this. Also last thing, when I click Check Status it clears the "Node Up Or Node Down" status, so that effects the "worksheet change" section.