Results 1 to 4 of 4

Thread: Worksheet_calculate runs based on every formula in sheet

  1. #1
    VBAX Regular
    Apr 2012

    Worksheet_calculate runs based on every formula in sheet

    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?



    [VBA]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[/VBA]

  2. #2
    Knowledge Base Approver VBAX Guru
    Oct 2005
    Surrey UK
    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()
    [vba]Private Sub OptionButton1_Change()
    Rows("35:68").EntireRow.Hidden = ActiveSheet.OptionButton1
    end sub[/vba]
    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:
    [vba]Sub OptionButton1_Click()
    Rows("35:68").EntireRow.Hidden = (ActiveSheet.OptionButtons("Option Button 1").Value = 1)
    End Sub
    [/vba] 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.
    p45cal - - - - -This is my signature, it appears after all my posts. Below is not directed at anyone in particular - so don't take offence! - (You might guess why it's there though)
    If I've helped and you can't be bothered to acknowledge it, I can't be bothered to look at further posts from you.

  3. #3
    Knowledge Base Approver VBAX Guru
    Apr 2012
    If the groupbox contains 2 optionbuttons (yes & No) you'd better use a checkbox:

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

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master xld's Avatar
    Apr 2005
    You don't need Entirerow when you use the Rows object, Rows is the entirerow

    [vba]Rows("35:68").Hidden = ...[/vba]
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts