+ Reply to Thread
Results 1 to 7 of 7

Hide Option Button based on Linked Cell Value

Hybrid View

  1. 05-21-2015, 11:51 AM #1
    slinn727
    slinn727 is offline
    Registered User
    Join Date
    05-21-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    4

    Hide Option Button based on Linked Cell Value

    Hi All,

    I have a worksheet attached with a code to hide Option Buttons based on cell values. The option buttons and group boxes related to Range E7 does not function.

    I linked the option buttons that change cell E7 to link to E8 and was then able to get the functions below based on E7 to function (After changing all E7 values to E8).

    Please help. Rev4.xlsm

    Private Sub Worksheet_Calculate()

   If Range("E8").Value = 0 Then
        Shapes("Option Button 21").Visible = True
        Shapes("Option Button 23").Visible = True
        Shapes("Group Box 24").Visible = True
        Shapes("Option Button 25").Visible = True
        Shapes("Option Button 26").Visible = True
        Shapes("Group Box 27").Visible = True
        Shapes("Option Button 28").Visible = True
        Shapes("Option Button 29").Visible = True
        Shapes("Option Button 30").Visible = True

   ElseIf Range("E8").Value = 3 Then
        Shapes("Option Button 21").Visible = False
        Shapes("Option Button 23").Visible = True
        Shapes("Group Box 24").Visible = False
        Shapes("Option Button 25").Visible = False
        Shapes("Option Button 26").Visible = False
        Shapes("Group Box 27").Visible = True
        Shapes("Option Button 28").Visible = True
        Shapes("Option Button 29").Visible = True
        Shapes("Option Button 30").Visible = True

   ElseIf Range("E8").Value = 2 Then
        Shapes("Option Button 21").Visible = True
        Shapes("Option Button 23").Visible = False
        Shapes("Group Box 24").Visible = False
        Shapes("Option Button 25").Visible = False
        Shapes("Option Button 26").Visible = False
        Shapes("Group Box 27").Visible = False
        Shapes("Option Button 28").Visible = False
        Shapes("Option Button 29").Visible = False
        Shapes("Option Button 30").Visible = False

   ElseIf Range("E8").Value = 1 Then
        Shapes("Option Button 21").Visible = True
        Shapes("Option Button 23").Visible = False
        Shapes("Group Box 24").Visible = True
        Shapes("Option Button 25").Visible = True
        Shapes("Option Button 26").Visible = True
        Shapes("Group Box 27").Visible = False
        Shapes("Option Button 28").Visible = False
        Shapes("Option Button 29").Visible = False
        Shapes("Option Button 30").Visible = False

   End If

   If Range("E7").Value = 0 Then
        Shapes("Option Button 69").Visible = True
        Shapes("Option Button 70").Visible = True
        Shapes("Group Box 68").Visible = True

   ElseIf Range("E7").Value = 1 Then
        Shapes("Option Button 69").Visible = False
        Shapes("Option Button 70").Visible = False
        Shapes("Group Box 68").Visible = False
                
   ElseIf Range("E7").Value = 2 Then
        Shapes("Option Button 69").Visible = True
        Shapes("Option Button 70").Visible = True
        Shapes("Group Box 68").Visible = True
        
   ElseIf Range("E7").Value = 3 Then
        Shapes("Option Button 69").Visible = True
        Shapes("Option Button 70").Visible = True
        Shapes("Group Box 68").Visible = True


   End If
End Sub
    Last edited by 6StringJazzer; 05-21-2015 at 12:00 PM. Reason: changed QUOTE tags to CODE tags
    Reply With QuoteRegister To Reply
  2. 05-21-2015, 12:11 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2411
    Posts
    26,729

    Re: Hide Option Button based on Linked Cell Value

    I would use
    Private Sub Worksheet_Change(ByVal Target As Range)
    instead of
    Private Sub Worksheet_Calculate()
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 05-21-2015, 12:15 PM #3
    slinn727
    slinn727 is offline
    Registered User
    Join Date
    05-21-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    4

    Re: Hide Option Button based on Linked Cell Value

    Hi Jeff,

    Thank you for your response. I used Worksheet_Calculate since the Values in "E7" and "E8" are based on the selection of option buttons in particular groups. The Worksheet_Change function will not activate the code.

    I do not understand why a change in cell "E8" triggers the code to run, but not a change in cell "E7".
    Reply With QuoteRegister To Reply
  4. 05-21-2015, 12:27 PM #4
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Hide Option Button based on Linked Cell Value

    Hello slinn727,

    Have you thought about using a UserForm to guide the user's choices?
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
  5. 05-21-2015, 12:37 PM #5
    slinn727
    slinn727 is offline
    Registered User
    Join Date
    05-21-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    4

    Re: Hide Option Button based on Linked Cell Value

    Hi Leith,

    I have to admit I am new to VBA coding and know nothing about UserForm. Is there a tutorial available I could use?

    Thank you!
    Reply With QuoteRegister To Reply
  6. 05-21-2015, 01:31 PM #6
    StuCram
    StuCram is offline
    Valued Forum Contributor
    Join Date
    10-05-2014
    Location
    Regina, SK Canada
    MS-Off Ver
    Excel for Mac 11
    Posts
    330

    Re: Hide Option Button based on Linked Cell Value

    A 'User Form' would be a cool interface for this project.

    However, here's a quick way to force the 'calculate' event and macro when either cells E7 or E8 is changed by selecting various buttons.
    Just add a formula like = E7 + E8 in an unused cell.

    It seemed to work in your example when I tested it.

    Optionally, you might want to extend that to add all the code values for the various button groups.
    Reply With QuoteRegister To Reply
  7. 05-21-2015, 01:41 PM #7
    slinn727
    slinn727 is offline
    Registered User
    Join Date
    05-21-2015
    Location
    United States
    MS-Off Ver
    2010
    Posts
    4

    Re: Hide Option Button based on Linked Cell Value

    Hi StruCram,

    Such a simple solution! Thank you! I'm going to look into User Form for this as well since I'm in the early stages for a few month project.
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Option button linked cells
    By Gethsaine in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 10-21-2014, 10:21 AM
  2. [SOLVED] Macro based on Spin Button Linked cell value
    By turist in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-15-2013, 04:07 PM
  3. Hide a picture button placed in all sheets using a option button (form control)
    By lagaranch in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-26-2012, 10:33 AM
  4. Macro cannot hide rows based on cell linked to checkbox
    By fernaldd in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 08-04-2009, 11:46 AM
  5. Need Help With Linked Option Button
    By Bd_Blues in forum Excel General
    Replies: 0
    Last Post: 02-01-2005, 08:39 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1