PDA

View Full Version : Worksheet_calculate runs based on every formula in sheet



AIDSNGO
04-30-2012, 09:35 AM
I am trying to hide several rows whenever the option button "no" is checked in a groupbox in a spreadsheet. I could not get any code to run when the option buttons were checked, so I set the cell link to R27 and then put into R26 the formula =R27. This seems to work with the code below, but the spreadsheet is constantly updating, with lots of spinning cursors and eventual freezing. Is there a simple fix, or a more direct method than my workaround?

Thanks!

AIDSNGO

Private Sub Worksheet_Calculate()
Dim target As Range
Set target = Range("R26")
If target.Value = "2" Then
Rows("35:68").EntireRow.Hidden = True
End If
End Sub

p45cal
04-30-2012, 01:13 PM
Are these activeX optionbuttons, or the other kind?
Excel Version?
If they're activeX then in design mode if you double click one it'll take you to it's click event code. Change it from the likes of:
Private Sub OptionButton1_Click()
to:
Private Sub OptionButton1_Change()
Rows("35:68").EntireRow.Hidden = ActiveSheet.OptionButton1
end sub
If the option buttons are of the other type then if you right-click it you get the opportunity to assign a macro. Presumably you also have another option button to switch the other way. Both these option buttons need to assigned to the same macro which should be like:
Sub OptionButton1_Click()
Rows("35:68").EntireRow.Hidden = (ActiveSheet.OptionButtons("Option Button 1").Value = 1)
End Sub
Obviously the codes should be adjusted to reflect the actual names of the option buttons.
You don't need to link any cells, nor any calculate_event code.

snb
05-01-2012, 03:22 AM
If the groupbox contains 2 optionbuttons (yes & No) you'd better use a checkbox:

private sub checkbox1_change()
Rows("35:68").EntireRow.Hidden = checkbox1.value
end sub

Bob Phillips
05-01-2012, 03:44 AM
You don't need Entirerow when you use the Rows object, Rows is the entirerow

Rows("35:68").Hidden = ...