Results 1 to 4 of 4

Thread: Worksheet_calculate runs based on every formula in sheet

  1. #1

    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?



    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 
    Formatting tags added by mark007

  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()
    Private Sub OptionButton1_[COLOR=Blue]Change[/COLOR]() 
        Rows("35:68").EntireRow.Hidden = ActiveSheet.OptionButton1 
    End Sub 
    Formatting tags added by mark007
    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 
    Formatting tags added by mark007
    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
    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 
    Formatting tags added by mark007

  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

    Rows("35:68").Hidden = ... 
    Formatting tags added by mark007
    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