Cell Refresh Button

August 14, 2010 at 13:21:00
Specs: Windows XP


I hope you can offer advice on how I can complete a worksheet I'm working on.

I've designed a worksheet in Excel, in one column (E3:E7) I have 5 data entry cells where the value entered needs to add to a running total for each cell contained in cells G4:K4.

I made 4 other cells with =E3+G4 etc. and after a button has been pressed, the contents of this cell need to copied to G4 to keep the running total going.

Also when the data has been copied to E3 (and the other 4 cells) , I need to reset the value of E3 to 0 during the same button press.

I've used a lot of complex logic functions to get all the math side of my worksheet working but I've no clue about writing a macro and I'm also struggling to add a button to my worksheet (Id like my button to be in cell E8 but I've been able to find out how to put a button in the toolbar which is no good)

Was thinking, instead or resetting cells E3:E7 I could just copy 0 into those cells so I just need to copy the contents of my 5 running total counters and a cell containing 0 could be copied into the data entry cells, just need the button that will refresh it all.

I hope this is easier than I'm making it sound.
Many thanks for taking the time to read through my request, I hope its not too much of a problem and I look forward to reading your reply.


See More: Cell Refresh Button

August 14, 2010 at 16:09:52
Best to post this question in the Office Software forum. Maybe a moderator will move it there.

Report •

August 14, 2010 at 16:12:32
This should be posted here for more help:


Report •

August 14, 2010 at 20:10:11

You want to add values from a group of cells to a same number of 'total' cells, and then when the original cells are updated with new values, add the new values to the 'total' cells.

This cannot be done with regular Excel formulas.

To do this some visual basic code is required and this code needs to be 'triggered' by the 'Change' event.

Excel has a series of events, including a change event which activates every time the value in a cell changes.

To get what you want you need to capture the change event for cells E3 to E7

When any of the values in these cells changes, the value in the corresponding cell in the range G4 to K4 will add the new value in the corresponding cell (E3 to E7).

To do this, right-click on the worksheet tab name. Select 'View Code'
In the large VBA window paste this code:

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ErrHnd

Dim rngIsect As Range

'stop changes made by this macro from re-triggering it
Application.EnableEvents = False

'test if change is in a cell in the input cells E3 to E7
Set rngIsect = Application.Intersect(Target, ActiveSheet.Range("E3:E7"))
If Not rngIsect Is Nothing Then
    'it is in the range - so add the cell's value
    'to the related cell
    Select Case Target.Address
        Case "$E$3"
            ActiveSheet.Range("G4").Value = _
                    ActiveSheet.Range("G4").Value + _
        Case "$E$4"
            ActiveSheet.Range("H4").Value = _
                    ActiveSheet.Range("H4").Value + _
        Case "$E$5"
            ActiveSheet.Range("I4").Value = _
                    ActiveSheet.Range("I4").Value + _
        Case "$E$6"
            ActiveSheet.Range("J4").Value = _
                    ActiveSheet.Range("J4").Value + _
        Case "$E$7"
             ActiveSheet.Range("K4").Value = _
                    ActiveSheet.Range("K4").Value + _
    End Select
End If
're-enable events
Application.EnableEvents = True
Exit Sub

'error handler
're-enable events
Application.EnableEvents = True
End Sub

Now when data in a cell in the range E3 to E7 is changed, the value will be added to the value in the related cell G4 to K4.

Hope this is what you were looking for.


Report •
Related Solutions

Ask Question