Automatically triggering recalculate when a specific worksheet is brought into focus

[olddrummerguy]

I'm trying to make a spreadsheet be as user friendly as I can for upper management.

I have a spreadsheet that uses some VB code I found that sums certain cells on a specific worksheet based on the color of cells in one of the other worksheets. The problem is the sums dont adjust to data changes, You have to issue a CTRL + ALT F9 to get it to recalculate. It must be because this sun is really AB code.

Is there any way to have the manual recalculate be issued every time this specific worksheet is brought into focus ( clicked on)?

Thanks

 

[mumps]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your sheet and click 'View Code'. Paste the macro into the empty code window that opens up. Replace the text in red with the name of the macro that sums certain cells. Close the code window to return to your sheet. each time you activate the sheet, the macro that sums the cells will be executed.

Private Sub Worksheet_Activate()
    Run "YourMacroNameHere"
End Sub

 

[olddrummerguy]

Hi Rich,

It appears the VB code I already have isnt a macro. When I right click on the "view code" The window that comes up isnt blank. It contains that "summing" logic. I'm going to try and paste a screen shot of the window that comes up. I'm afraid I'm very very green with the macro and VB universe in Excel.

 

[kevin9999]

Your code is actually a Function, which means that somewhere on your sheet you'll have a formula that looks like "=SumByColor(something, something_else)"

To force calculation when you select a sheet, try the following in the sheet's code module:

Private Sub Worksheet_Activate()
    Me.Calculate
End Sub

 

[olddrummerguy]

First off thanks for taking the time to look and help me with this

I pasted this into the same module that the Function is in. I used all the exact characters you shared in your example. I'm unsure when and why I should create added modules. This doesn't seem to work after inserting this, saving and closing and reopening the spreadsheet and making a change in the worksheet that the changes are made in. To be clear. Changes are made in worksheet "A" and this recaluation should happen when I bring into focus worksheet "B". "B" is where I added this calculate VBA code.

 

[kevin9999]

First off thanks for taking the time to look and help me with this

I pasted this into the same module that the Function is in. I used all the exact characters you shared in your example. I'm unsure when and why I should create added modules. This doesn't seem to work after inserting this, saving and closing and reopening the spreadsheet and making a change in the worksheet that the changes are made in. To be clear. Changes are made in worksheet "A" and this recaluation should happen when I bring into focus worksheet "B". "B" is where I added this calculate VBA code.

I'd be surprised if the function is in the worksheet module. To put the code in the correct l place- when the vba editor screen is open, double click on the worksheet "B" in the small project explorer window at the upper left of the screen. A new blank window should appear on the right side of the screen. That's where the code should go.

 

[olddrummerguy]

Again Kevin, Thanks for your patience with me. Let me give you more specifics.

Data is entered and cells are colored in the "RELEASES" worksheet. Heres the VBA screen shot for that sheet.

When the "CUSTOMER_STATUS_SUMMARY" worksheet is opened thats when the "SumByColor" function should run. This looks at values on the "RELEASES" sheet and sums rows based on the colors. Heres the VBA in the "CUSTOMER_STATUS_SUMMARY" worksheet.

For further clarity here are partial screen shots of both of the worksheets that are involved in this.

My problem is columns E, F and G refer to this special SUM function and values dont change when a cell is colored on the "RELEASES" worksheet unless a qty is changed somewhere on that sheet or the CTRL + ALT + F9 keys are pressed. So as you can see I already have the special function in the code area of the "CUSTOMER_STATUS_SUMMARY" sheet.

I'm way way over my pay grade on this as my experience with anything in VBA is blindly copy from the internet and past into places in the VBA editor.

 

[kevin9999]

What you describe as the "VBA in the CUSTOMER_STATUS_SUMMARY worksheet" is the location where the code in post #4 should be put. Normally, your function code would not go there, but in its own standard module.