none
Where Is My Option Button Coding? RRS feed

  • Question

  • A couple of years ago I created a worksheet on which I have a group of 5 option buttons. The buttons filter a range of data and the subtotals of various columns display in col F. Everything works fine:

     

    However, I now want to insert some additional columns and amend the coding to subtotal different columns - but where is the coding? I cannot, for the life of me, recall or find where the coding is that creates these filters. I've searched my worksheet and the 'net for hours and still cannot figure out where the coding is hidden.

    With considerable embarrassment at having to ask this - can somebody please advise where I should be looking for the coding that I wrote.

    Friday, November 30, 2018 1:21 AM

All replies

  • I am assuming that the option buttons are ActiveX controls. If so, then one of the following methods.

    1. Right click the worksheet tab name and select "View Code" and the sub name should contain the option button name.
    2. On the Developer ribbon select "Design mode" and the sizing handles should appear around the controls. Right click the control and select "View code"
    3. Alt and F11 to open the VBA editor and double click the worksheet name in the Project explorer (Left column) and find the sub based on the option button name.

    Regards, OssieMac

    Friday, November 30, 2018 1:49 AM
  • It's so long since I wrote the code that I forget whether it was a Form Control or an ActiveX control, but from the results of checking your three suggestions it looks like it's a Form Control. Results are:

    1.  The sheet code page is blank
    2.  Left clicking the control (I'm left-handed and so my mouse buttons are the opposite way round to most) brought up Assign Macro rather than View Code (which tells me it's a Form Control). Left clicking the GroupBox control and then Assign Macro shows a non-existent macro name. Selecting Macros on the Developer tab lists three macros, none of which relate to the option buttons.
    3.  ALT + F11 + double clicking the sheet name brings up a blank code page.
    Friday, November 30, 2018 2:13 AM
  • What are you actually seeing when you say "clicking the GroupBox control and then Assign Macro shows a non-existent macro name"? Is there a workbook name associated? Maybe the code is in the Personal workbook? If you see the macro name only then copy it and then open the VBA editor at one of the macros you can see when selecting Macros on the Developer tab.

    Then select VBA menu item Edit -> Find and in the "Find what" field paste the macro name. Select Option "Current project" and then click "Find next". If found, it should display the code in the editor. Click in blank space in the Project Explorer (left column) and it should highlight the module you are in.

    If no success then if you can upload the workbook to OneDrive then I will have a look at it for you.

    Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

    • Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
    • To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
    • Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
    • Go to this link.  https://onedrive.live.com
    • Use the same login Id and Password that you use for this forum.
    • Select Upload in the blue bar across the top and browse to the zipped folder to be uploaded.
    • Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
    • Right click the file on OneDrive and select Share.
    • Select "Get a Link" from the popup menu.
    • Click in the field displaying the link and Ctrl and A should highlight the entire link and then Copy and Paste the link into your reply on this forum. (I suggest that you avoid the "Copy" button on the "Get a link" screen because it introduces additional steps that are not required.)


    Regards, OssieMac

    Friday, November 30, 2018 3:50 AM
  • Also have you expanded the Project Explorer info (left column in VBA editor) by clicking the plus signs to the left of the items displayed in the project explorer in case the code is in a module that is just not currently visible in the Project Explorer.

    Regards, OssieMac

    Friday, November 30, 2018 3:54 AM
  • When I left-click the GroupBox control I get the Form Control drop-down menu. When I select Assign Macro it shows:

    GroupBox7 at that point doesn't exist as a macro; the only existing macros are the three listed.

    "Is there a workbook name associated?"  No, as you can see there is no workbook name associated.

    I checked the Personal.xlsb coding; all that's in there are the code blocks for either printing to my printer or printing to a pdf file.

    Following through the search process as you detailed resulted in:

    The workbook is 20+ MB so I'll scale it down, or find another one which is smaller (I use these grouped option buttons on 100+ worksheets) and upload it as per your instructions. I'll post the link once it's been uploaded.

    And yes, I've fully expanded all the plus signs, and checked each page searching for the missing code. The good thing is that it has to be there somewhere, because the option buttons work correctly.

    In the interim, my grateful thanks for your assistance.
    David

    Friday, November 30, 2018 6:54 AM
  • I located an old worksheet that has the option buttons on it and extracted the sheet from the workbook. Before uploading I saved the sheet to a USB stick and ran it on my laptop to ensure it worked normally, which it does. However, while it was loading I received a pop-up noting that the file had links to an external workbook. I selected Don't Update, then ensured that the option buttons worked correctly. I checked Data / Edit Links and saw that it was attempting to link to the workbook that it came from, which is on the desktop, not on the laptop, so I broke the link. Anyway, the option buttons worked correctly so whatever coding they need is on the file that I've uploaded.

    https://1drv.ms/u/s!ArANX3rGwxC6a7w7Au_lJqgWYUs

    Friday, November 30, 2018 8:47 AM
  • Hi David,

    There is no VBA code assigned to the buttons. Everything that takes place on the worksheet when a button is selected is based on the linked cell which is AA10 (Under Button 1 that has caption "Go to EqC").

    When you right click and select "Assign Macro", the macro name you are seeing is the default name of a macro that will be created if you click the "New" button and not a previously assigned macro. If you click the "New" button then it will create a Macro in a standard module and then open the VBA editor where it creates the default macro sub name.

    That's the reason that I needed a copy of the workbook because I would never have guessed that was what you were seeing but with the workbook example it became obvious to me what was occurring.

    All the best with the modifications you are contemplating for the project.


    Regards, OssieMac

    Friday, November 30, 2018 11:09 AM
  • Hi OssieMac

    Ok, I follow that, but what makes the data in col F change when the option buttons are selected? I was anticipating finding coding, somewhere, that takes the value in AA10 and takes a number of alternative actions depending on what that value is.

    Friday, November 30, 2018 12:18 PM
  • I can't follow what the sheet is actually doing but there is a formula in cell AA2 that references AA10.

    The formulas in range F2:F11 are subtotals for various columns. If you follow the formulas in these columns then they will probably lead back to AA10 somehow via AA2.

    I really can't help further because I don't understand what the worksheet is actually doing. However, I am quite certain that the changing value in AA10 is the start of altering data dependent on which button is selected and that no VBA code is associated on the sheet that you provided. 


    Regards, OssieMac

    Friday, November 30, 2018 11:11 PM
  • Thanks OssieMac

    Yes, the sheet is not easy to understand, especially those top 12 rows. You're correct in your assessment of F2:F11, in fact those few cells are the critical output from a 20MB workbook. AA2 is just a visual indicator to remind me which column in the Y : AH range I'm using, is based on the value hidden in AA10, and serves no other useful purpose.

    I wondered whether there may have been a hidden link somewhere so I hunted down Bill Manville's Find Links add-in, recommended by Jim Cone in another post, but that showed the sheet to be free of external links, and in any case the stand-alone sheet that I uploaded works fine without linking to anything.

    Unfortunately, despite having created the worksheet myself, I simply cannot recall how I made those option buttons work the way they do. It's the only time in years of Excel that I've ever found a need for option buttons, so I would almost certainly have sought assistance. I've scoured every forum that I participate in and there's no sign of a cry for help, so how I achieved what I did remains a mystery.

    I've spent all day trying to resolve this and will continue searching as it's a problem that I need to resolve. I'm sure you're correct that the changing value in AA10 is the clue. Thank you for trying to help; if I ever resolve it I'll add a final post here.
    David
    Saturday, December 1, 2018 6:52 AM
  • Have a look at the following link re tracing precedents and dependents for formulas. It will probably assist you to find the references you require.

    https://support.office.com/en-us/article/display-the-relationships-between-formulas-and-cells-a59bef2b-3701-46bf-8ff1-d3518771d507


    Regards, OssieMac

    Saturday, December 1, 2018 10:26 AM