Sheet Calculate Event

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,467
Hello,

I have code in the worksheet_calculate Event - the code is in the worksheet module of only one sheet. However, the code is called when changes are made to other sheets.

Is there a way to avoid this? The only way a user can cause changes to the worksheet is from a selection from controls combobox. Since the Worksheet Change event does not respond to sheet changes stemming from changes caused by selections from a control combobox, I had to use the calculate even. Thank you for any help.
 

Some videos you may like

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Could you use a dropdown list on the sheet of interest instead of a combobox?
 

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,467
Hi Shg. Well - the combobox is linked to a cell. That value of that cell is used in a vlookup in dozens of cells. I thought of using a combobox from the activeX collection, then I could place the code in the "change" event of that combobox and bypass the sheet calculate event, but if you link that type of combobox to a cell, you don't get a numeric value rather you get the value of what was selected from that combobox.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Well -- changing the drop-down would fire the Change event, and you could get the list index of the validation range with a formula for your other calculations.
 

Evagrius Ponticus

Well-known Member
Joined
May 24, 2007
Messages
1,467
Thanks Shg! Would have an example of the formula of how I would get the list index of a validation range; do you mean a lookup type formula? This is something I have not tried before. Thank you!
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

If A1 contains the dropdown list,

=match(a1, valrange, 0)
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
Well done, good luck.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,519
Messages
5,596,635
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top