Consulting

Results 1 to 4 of 4

Thread: Worksheet_calculate runs based on every formula in sheet

  1. #1
    VBAX Regular
    Joined
    Apr 2012
    Posts
    15
    Location

    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?

    Thanks!

    AIDSNGO

    [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 Wizard p45cal's Avatar
    Joined
    Oct 2005
    Location
    Surrey UK
    Posts
    5,844
    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:
    [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
    Everyone: 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 Wizard
    Joined
    Apr 2012
    Posts
    5,635
    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 Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    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
  •