Get Column Number Of Clicked Button

    Hello Guru's


    Code below


    Sub Mainscoresheet()
    ' Mainlineup Macro
    Dim k As Integer, i As Integer, cs As Integer
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
    cs = .Column
    End With
    ...... rest of code



    I have a button assigned to this macro about 20 times all over my worksheet
    I will place the button in dirrerent locations. What I need to do is get the column that the top-left corner of the button and assign that to "cs".


    Example if the top left corner of the button is in column "F" then cs would be 6.


    Thanks
    Ross

    Re: Select column based on macro button location


    Hi Ross


    You can use the following code to get the topleft cell's row


    Code
    cs = CommandButton1.TopLeftCell.Column

    :angrypc:

    Re: Select column based on macro button location


    Thanks for the relpy, but I get a runtime error 424 Object required on the
    cs = Line


    Sub Mainscoresheet()
    ' Mainlineup Macro
    Dim k As Integer, i As Integer, cs As Integer, ws As Worksheet, sh As Worksheet, rs As Integer, hr As Integer, hc As Integer
    cs = CommandButton1.TopLeftCell.Column 'Column to start
    .......

    Re: Select column based on macro button location



    Are you sure the button you are using is called commandbutton1??


    Aadarsh

    :angrypc:

    Re: Select column based on macro button location


    aadarsh,


    Not sure what the name of the button is.
    But, for easy of use for the end-user, there will be many buttons-stratigically placed throught the sheets in the workbook. I would assume in that case all the button would have a different name.


    I need to get the cs= based on the top left corner of the button used to run the macro.


    example:


    I'll make a button that the top left corner is the cell A10 then CS would be 1.
    Then I'll copy the button and paste it where the top left corner will be in cell Z32, therfore cs would be 26.....


    All these button will run the same macro.


    Norie, Unfortunatly I have no control of where thae active call might be when the user clicks on the button.



    Ross

    Re: Select column based on macro button location


    Hi Ross,


    Why don't you add a column to the left of your spreadsheet and then select ColumnB and FreezeColumns. That way you only need one button and it will always be on screen.


    Regards,


    Bill

    Re: Select column based on macro button location


    I'll try to simplify.


    I want the buttons all over the sheet.


    For example- based on the location of the button, I canhave a button in cloumn A that sort sort column A, have another button in F that sorts column F. I can do the above for ANY column on the sheet with THE EXACT SAME Macro. All based on the stratigic location of the button.


    I can use it to set print areas, sort data, make a chart. All based on the location of the button.


    Ross

    Re: Select column based on macro button location


    Hi Ross,
    Your original code works with buttons from the Forms menu. Why not stick with that?
    [vba]Sub Mainscoresheet()
    ' Mainlineup Macro
    Dim b As Object, cs As Integer
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
    cs = .Column
    End With
    MsgBox "Column Number " & cs
    End Sub[/vba]
    If you use buttons from the Control menu, the above won't work but why use them? If you must use them for some reason then I suspect you will either need to have one macro per button tailored to the button name as others have suggested, or do something tricky with class modules (beyond my ability to help).

    Re: Select column based on macro button location


    I know this is an older thread, but almost answers my question.


    I have almost the same issue but I want to rows not columns.


    Here a mock up of what im trying to do.


    I want to be able to press the button or cell and run a macro that pull only certain information off that row and paste into a form on another sheet, got most of it worked out, but cant seem to link the button to a cetain row


    [Blocked Image: http://ourvworld.com/koepics/example.jpg]

    Re: Select column based on macro button location


    Ross,
    Thanks, i had just tried this to see what it did if i replace row with column, your link helped a ton.. ty


    Code
    Sub Mainscoresheet()
     ' Mainlineup Macro
    Dim b As Object, cs As Integer
    Set b = ActiveSheet.Buttons(Application.Caller)
    With b.TopLeftCell
        cs = .Row
    End With
    MsgBox "row Number " & cs
End Sub

    Hello Derk,



    I have tried to apply the earlier mentioned code to a button, but I keep getting the following error: run-time error '1004' Unable to get the buttons property of the Worksheet Class.


    This error is given for the line:
    Set b = ActiveSheet.Buttons(Application.Caller).


    [SIZE=12px]If I change "Application.Caller" to the name of the button, it does work, but I want to apply the same to several buttons and to avoid manual changing of the macro each time I would definitely like to use your given code. Do you know what could be the problem? [/SIZE]


    Greetings, Vincent

Participate now!

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

Register Yourself Login