ActiveX Button Relative Cell Question

  • Hello, I am setting up a quality checklist with an activex button for each item. It allows the user to select a "Pass" button or a "Fail" button. I want to have the "Pass" or "Fail" text appear in the column to the right of the buttons. See the screen shot and code is below.


    I have this feature working, but it is not sustainable. I will have a 100 quality checks on this sheet (200 buttons!). I have made it work the hard-way for the first 20 items. Basically, I have 1 macro assigned to each button, so 40 macros. The macro will always write the text in the W22 cell in this example. The problem is if I add rows above these buttons, it will still dump the "Pass" or "Fail" text in cell W22, which will now be wrong with rows added above it.


    Instead of one macro per button (which this sheet would have 200 individual macros for the 200 buttons), I should be able to create just 2 macros, a "Pass" macro and "Fail" macro. These two macros should always insert the "Pass" text 2 columns to the right of the Pass button location, and the insert the "Fail" text 1 column to the right of the Fail button location, no matter where the button is located on the sheet. This would allow me to add or delete rows above or below the buttons and not have to worry about rewriting the macros.

    Thanks for helping me out, I really appreciate it!



    Code
    Sub Item1Pass()
    Range("W22").Select
    ActiveCell.FormulaR1C1 = "Pass"
    End Sub
    
    Sub Item1Fail()
    Range("W22").Select
    ActiveCell.FormulaR1C1 = "Fail"
    End Sub

    Edited once, last by Carim: Added Code Tags ().

  • I'd have thought it would bas as easy to just have a data validation dropdown list with Pass and Fail in it? Even if not, I'd suggest using Form buttons rather than activex - they are more stable and also a lot simpler to assign the same macro to.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!