Reference Cell Based On Button Location & Determine Button Used

  • I received this code from an example I found once upon a time that was originally submitted by someone else.


    Right now the code enters the current date in a cell of the same row as the checkbox when it's checked.


    I need to revise it, or come up with something similar, that will reference the value of a cell when it is in the same row as the button (from the Forms toolbar) that is clicked to activate the macro.


    First of all, I don't know how to reference a Forms button in VBA.


    Here is the code I found:



    Here is some other code I already created. Unfortunately, because I don't know how to do the row reference, I had to create 25 different macros, which just bulks up the size of my file and slows it down. But here is what I'm trying to accomplish in my macro:



    I appreciate any help you can provide.

  • Re: Reference Cell Based On Button Location


    If you assign this macro to the buttons it will report the topleft cell over which the button is positioned. And cell B's address of the row.


    [vba]
    Sub xx()
    With ActiveSheet.Shapes(Application.Caller)
    MsgBox "Topleft cell " & .TopLeftCell.Address
    MsgBox "Cell B of row " & .TopLeftCell.EntireRow.Cells(1, 2).Address
    End With
    End Sub
    [/vba]

  • Re: Reference Cell Based On Button Location


    To be honest, that really isn't enough to get me going. I need to be able to use code to actually pull the contents from a specific cell. All I got from your posts was a reference to the row. How can I reference the row and column? And I don't need it in a message box.


    Thanks!

  • Re: Reference Cell Based On Button Location


    well the second message box is referencing which ever row column 2.


    [vba]


    ' Put HELLO in cell B of row containing button
    ActiveSheet.Shapes(Application.Caller).TopLeftCell.EntireRow.Cells(1, 2).value = "HELLO"


    [/vba]

  • Re: Reference Cell Based On Button Location


    Something continues to not seem to work though. I used a portion of your code (WITH statement and the TopLeftCell Reference), yet I get a "Run-time error: 424 Object Required" error. I'm not understanding that is wrong.


  • Re: Reference Cell Based On Button Location


    Now I'm getting a different error: "Run-time error:9. Subcript out of range"


  • Re: Reference Cell Based On Button Location


    Which line?


    Anything to do with what looks like a extra space in your sheet name?


    [vba]
    Sheets("ADID History Lookup").Range("E2") = sADID
    Sheets("ADID History Lookup").Activate
    [/vba]