+ Reply to Thread
Results 1 to 11 of 11

Option button linked cells

  1. #1
    Registered User
    Join Date
    10-15-2014
    Location
    Portsmouth
    MS-Off Ver
    2010
    Posts
    6

    Option button linked cells

    Hi all bit of a VB novice here and could use some help.

    I've been putting together a budget spreadsheet that allows a user to select what payment method was used for each item, this is done with ActiveX option buttons, these all work no problem.

    The issue arises when a user attempts to put in a new row. I've borrowed a piece of VB code that will insert a new row along with all formulas from above and a new set of option buttons (this solved the first problem I had)

    The trouble is, the new set of option buttons are still considered a part of the same group so do not operate independently of the other groups, they also do not have a linked cell. Is there a way to do this via macro?

    E.g. The first row's option buttons are in cell F12, they are linked with cells J,K and L 12 and are in a group called 'Group1', the next row's option buttons (which I created manually) are in F13 and are similarly linked to J,K and L 13, the group name is 'Group2' If I run the macro below, the new row is created but the new option buttons are all in 'Group2' still and have no linked cell where it would need to be J,K and L 14.

    Basically, is there a macro to set properties of ActiveX option buttons as they are inserted with a new row?

    Here is the code I've borrowed from another site to insert rows:

    Moderator's note: Please take the time to review our rules. There aren't many, and they are all important. Rule #3 requires code tags. I have added them for you this time because you are a new member. --6StringJazzer

    Please Login or Register  to view this content.
    Last edited by 6StringJazzer; 10-17-2014 at 09:22 AM.

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,785

    Re: Option button linked cells

    It is definitely possible to set the group and linked cell for an option button in VBA code.

    Please Login or Register  to view this content.
    However, the code you posted does not explicitly create the buttons, so it's hard to identify them to be able to write this code. It creates the buttons as a consequence of copying and pasting the row that the buttons are in. It can still be done, because the option buttons are in a collection of controls for the sheet, so we just need to address the last three buttons in the collection.

    Before I try to provide any actual code, I need to understand what you actually want the code above to do. Sorry to be blunt but it is rather clumsy code. The method used to identify and loop through all selected sheets is convoluted. Not only that but do you really need to perform the same action on multiple worksheets? Your description makes it sound like you are only concerned with one sheet. Also, vRows is not declared here so I can't tell where else its value might be updated. And indentation to show control structures is also desirable.

    I can provide a solution if you answer my question about multiple sheets. It would also be very helpful to attach your Excel file.
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    10-15-2014
    Location
    Portsmouth
    MS-Off Ver
    2010
    Posts
    6

    Re: Option button linked cells

    Thanks for that

    The code I posted is used to create new rows and copy formulas. It doesn't necessarily need to be there.

    What I'm trying to achieve is that a user will be able to insert a new row with a new set of option buttons that are grouped separately from the existing ones with there own set of linked cells.

    The reason I was using the code above was because I had a friend who said he could adapt it to do what I wanted, he couldn't.

    I only have one worksheet so there is no need to replicate it across sheets, and even the function to add more than one row at once isn't essential. The most important feature is the ability to operate a new set of option buttons independently with linked cells for each row that's created.

    Have attached the Spreadsheet for reference.

    Sorry about not using code tags, will do in future
    Attached Files Attached Files

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS 365 Family 64-bit 2502
    Posts
    26,785

    Re: Option button linked cells

    Hmm, wasn't as simple as I thought. Just to let you know I'm still looking at it, will get back when I have something for you.

  5. #5
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Option button linked cells

    Hi Gethsaine,

    See the attached file which creates groups of Active X OptionButtons. The file does not use 'Linked Cells' but the following syntax in the Create Section should do the job for you. This particular file uses a class event handler.
    Please Login or Register  to view this content.
    In your situation, I would name the OptionButtons and Groups in some kind of sequence (e.g. 'OptionButton00103 (3rd option button in first group) and 'Group001'. That way each item has a unique name. If you are inserting new rows, you would have to access all the OptionButtons on the Sheet to determine what the next sequential group number would be.

    Lewis

    ---------------------------
    Ordinary Module Code:
    Please Login or Register  to view this content.
    Class Module Code (Must be in Class Module Named 'ClassOptionButtonEvent'):
    Please Login or Register  to view this content.
    Attached Files Attached Files

  6. #6
    Registered User
    Join Date
    10-15-2014
    Location
    Portsmouth
    MS-Off Ver
    2010
    Posts
    6

    Re: Option button linked cells

    Thanks for that code LJ, but i'm not sure how to adapt it for what I want. I've managed to alter it so that it only gives 3 option buttons, but I'm stumped beyond that.

    The create controls macro is very useful. Is there a way to create them in a single cell on a new row (same as the others). If not I can change the design so the three option buttons are in each of the relative columns.

    Would this be an easier method?

    Apologies for my dismal knowledge of Visual Basic, but when I started putting the sheet together, I never thought it would become this complicated.

    Thanks for all the help.

  7. #7
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Option button linked cells

    Hi,

    There is nothing wrong with your design. Actually, I think it is quite good. Try the following macros adapted from your original code and your workbook.

    Lewis

    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    10-15-2014
    Location
    Portsmouth
    MS-Off Ver
    2010
    Posts
    6

    Re: Option button linked cells

    Thank you! That is perfect, you sir, are a genius

  9. #9
    Registered User
    Join Date
    10-15-2014
    Location
    Portsmouth
    MS-Off Ver
    2010
    Posts
    6

    Re: Option button linked cells

    Sorry, having played about with it, it's very nearly perfect but not quite. Each new set of option buttons is still functioning as one big group. I need each new set to be an independently functioning group. Other than that it's brilliant.

    Thanks

  10. #10
    Forum Expert
    Join Date
    01-23-2013
    Location
    USA
    MS-Off Ver
    Microsoft 365 aka Office 365
    Posts
    3,863

    Re: Option button linked cells

    Sorry, I forgot all about the groups. My fault for not testing properly. Try the following with the changes in Red.

    Lewis

    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    10-15-2014
    Location
    Portsmouth
    MS-Off Ver
    2010
    Posts
    6

    Re: Option button linked cells

    That's absolutely perfect, thank you

+ 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. Disable cells contingent on an option button
    By jghender in forum Excel General
    Replies: 0
    Last Post: 12-27-2012, 06:10 PM
  2. Option Button that shows/hide certain cells
    By DoubLeA in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 05-17-2012, 03:31 PM
  3. Radio Button Groups linked to cells for counting values
    By kathyb10 in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-31-2011, 06:35 PM
  4. [SOLVED] using option button to highlight cells
    By Carl in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 06-05-2006, 01:55 PM
  5. Need Help With Linked Option Button
    By Bd_Blues in forum Excel General
    Replies: 0
    Last Post: 02-01-2005, 08:39 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