Posts by milkman00

    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