+ Reply to Thread
Results 1 to 12 of 12

Linking Option Button(Control Form) to Shapes

  1. #1
    Registered User
    Join Date
    09-04-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    21

    Linking Option Button(Control Form) to Shapes

    Hi all,

    As attached is a worksheet I am currently working on, however I'm faced with a problem and hope to get some help here.
    As shown in the attachment there are five boxes (rectangle shapes) and 5 option buttons of Form Control (Linked to cell Z2)

    What I hope to achieve is to link the 5 boxes respectively to the 5 option buttons into a sense that when one of the option is being selected, only the box being linked to the option button will appear whereas the rest of the 4 boxes will remain hidden.

    I tried to make do with conditional formatting and highlighting the cells but I could not achieve the exact dimensions that will be required as compared to the boxes seen.
    Exact dimensions are required as I will need to check if the blue box below will fit into the white box or will there be any excess overhang.

    Here are the dimensions for the five options in case it could be of some use to lead to a solution:
    1. PMC - 96 X 125
    2. PGA (20 footer) - 96 X 238.5
    3. PLA - 60.4 X 125
    4. PEB - 53 X 88
    5. PAG - 88 X 125

    Do appreciate any sort of assistance available (I'm quite weak and badly trained with MS Excel, and am still quite ignorant to deal with macro without instructions) and do pardon my bad English.
    Thanks!

    Cheers.
    Attached Files Attached Files
    Last edited by Heva; 09-16-2013 at 03:52 AM.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linking Option Button(Control Form) to Shapes

    perhaps just use one shape and a table of dimensions like this
    Attached Files Attached Files
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    09-04-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Linking Option Button(Control Form) to Shapes

    Quote Originally Posted by JosephP View Post
    perhaps just use one shape and a table of dimensions like this
    Thanks Joseph!
    Just checking, is there any possibilities of having the worksheet sense that, once any part of the blue box goes out of the white box perimeters, it will highlight a cell (say L4)? To indicate that the blue box has exceeded and overhung the white box?
    (Though I reckon there is a slim chance of this to be possible)

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linking Option Button(Control Form) to Shapes

    perhaps
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-04-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Linking Option Button(Control Form) to Shapes

    Quote Originally Posted by JosephP View Post
    perhaps
    Please Login or Register  to view this content.
    I tried replacing the previous code you give with this but cell L4 do not seems to be highlighted up.
    Is there anywhere I did wrongly perhaps?
    Attached Files Attached Files

  6. #6
    Forum Guru Andy Pope's Avatar
    Join Date
    05-10-2004
    Location
    Essex, UK
    MS-Off Ver
    O365
    Posts
    20,419

    Re: Linking Option Button(Control Form) to Shapes

    you can have a couple of cells report whether the box is too wide and or tall
    Attached Files Attached Files
    Cheers
    Andy
    www.andypope.info

  7. #7
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linking Option Button(Control Form) to Shapes

    I misunderstood your requirement-I believed you wanted to know if the blue shape were wider or taller than the white one. perhaps
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-04-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Linking Option Button(Control Form) to Shapes

    Thanks Joseph and Andy so far!
    However when I tried to protect the sheet (like the file attahced) and changed the dimensions of the white box according to the form control, it still shows me that it is read-only and cannot be changed (even though I had deselected the "Locked" Option in their properties).
    Is there anything else in additional to that step which I may have to do? (Sorry to trouble you guys so much ><)
    Attached Files Attached Files

  9. #9
    Forum Contributor
    Join Date
    01-02-2007
    Location
    Australia NSW
    MS-Off Ver
    2013
    Posts
    494

    Re: Linking Option Button(Control Form) to Shapes

    You might have to tick the EDIT OBJECTS item at the bottom of the listed items when protecting.

  10. #10
    Registered User
    Join Date
    09-04-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Linking Option Button(Control Form) to Shapes

    Quote Originally Posted by coreytroy View Post
    You might have to tick the EDIT OBJECTS item at the bottom of the listed items when protecting.
    I had did what you advised but the error still persists as below...

    Error1.jpgError2.jpg

  11. #11
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Linking Option Button(Control Form) to Shapes

    you must unlock cells L4 and Z2-the cell linked to the option buttons and allow cell formats when protecting the sheet

  12. #12
    Registered User
    Join Date
    09-04-2013
    Location
    Singapore
    MS-Off Ver
    Excel 2007
    Posts
    21

    Re: Linking Option Button(Control Form) to Shapes

    Quote Originally Posted by JosephP View Post
    you must unlock cells L4 and Z2-the cell linked to the option buttons and allow cell formats when protecting the sheet
    It works!
    Thanks alot Joseph! ^^

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Form Control Option Button corrupting spreadsheet
    By PBest78 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 05-29-2013, 10:04 AM
  2. 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
  3. [SOLVED] Call Form Control Option Button within an If statement
    By SonOfOdin in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-03-2012, 02:23 PM
  4. Form Control Option Button Code
    By Boston in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 09-20-2010, 02:30 PM
  5. [SOLVED] Editing color/msoGradiant of shapes, option button and text boxes
    By MD in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-23-2006, 01:10 PM

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1