Enable/Disable calculation property macro has weird results

LRobert

New Member
Joined
Nov 12, 2019
Messages
4
I am trying set to the Calculation Property of each worksheet in a workbook independently with a macro. The user identifies worksheets where Calculation is to be enabled using a Listbox. The user then clicks a button to call the macro to apply the desired settings. Here is my code for Enabling Worksheet Calculation:

VBA Code:
Private Sub EnableCalc()

    Dim ws As Worksheet
    Dim i As Long
    
    For Each ws In ThisWorkbook.Worksheets
        ws.EnableCalculation = ListBoxWorksheets.Selected(i)
        i = i + 1
        Next ws

End Sub

It only works (and works as intended for all worksheets) when ListBoxWorksheets.Selected(0) is TRUE. For example, these values produce the expected result:

ListBoxWorksheets.Selected(0) = TRUE​
ListBoxWorksheets.Selected(1) = FALSE​
ListBoxWorksheets.Selected(2) = FALSE​
ListBoxWorksheets.Selected(3) = TRUE​
If ListBoxWorksheets.Selected(0) is FALSE, the Calculation Property of all worksheets is set to FALSE not matter what the ListBoxWorksheets.Selected values are. For example, these values set the Calculation Property of all worksheets to FALSE:

ListBoxWorksheets.Selected(0) = FALSE​
ListBoxWorksheets.Selected(1) = TRUE​
ListBoxWorksheets.Selected(2) = TRUE​
ListBoxWorksheets.Selected(3) = TRUE​

I have verified that ListBoxWorksheets.Selected(i) produces the expected series of TRUE/FALSE such as TRUE, TRUE, FALSE, TRUE... with a MsgBox. Any guess as to what the problem may be?
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
maybe the worksheets in the list box are in a different order from that of the "for next" ws loop
you could check that with some debug.printing

another thought, what if from the list box an array listing the worksheets is sent to the EnableCalc routine? then the routine can work explicitly with the intended worksheet (to remove the possibility suggested above)
 
Upvote 0
or what if the results of one worksheet depend on another worksheet - and that other worksheet has not been re-calced? could that be a situation?
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,923
Members
448,533
Latest member
thietbibeboiwasaco

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
Back
Top