none
command button on each row excel RRS feed

  • Question

  • Hi there
    I am assigned a task to create an excel Macros where i wanted.

    In each row cell B to be a command Button with text Sing off
    On button clicked I wanted to do some calculations.
    I am not sure how to create a command button which will repeat on each row ?

    I am using excel 2010

    Tuesday, March 27, 2012 2:29 PM

Answers

  • The only thing that I can think of is that you pasted the code into the worksheet module and not a standard module. The CreateButtons code would run from the worksheet module but the code associated with the button will not.

    When you open the VBA editor, select menu item Insert and then select Module. The code needs to be copied into the standard module because when the button is clicked, it looks in a standard module for the Form controls code.

    If you were creating these buttons manually then when you select the Developer tab and Insert there are 2 lots of controls; Form Controls and ActiveX controls. The buttons created by the code are Form Controls and the code that is assigned to them goes in a standard module.

    The reason for using the Form Controls in lieu of ActiveX Controls is because you can assign the same code to multiple buttons and that cannot be done with ActiveX controls. (Code for ActiveX controls goes in a worksheet module.)

    When the code is copied into the standard module ensure you delete it from the worksheet module.

    As a little extra, if you need to delete the buttons during testing it is a bit of a pain to do so manually so the following is code that will delete all of the controls. (Don't use on a production worksheet that has other controls on it; just in a workbook that you are testing my code because it deletes all controls on the worksheet without discrimination.)

    Sub DeleteShapes()
        Dim shp As Shape
        For Each shp In ActiveSheet.Shapes
            shp.Delete
        Next shp

    End Sub


    Regards, OssieMac


    • Edited by OssieMac Wednesday, March 28, 2012 11:38 AM
    • Marked as answer by Rushdy Najath Thursday, March 29, 2012 10:32 AM
    Wednesday, March 28, 2012 11:32 AM

All replies

  • This question was answered in "Excel for Developers"

    'Quoted answer

    Put the buttons over the cells, using the commandbutton from the controls toolbar. Double click each button, and insert this line into the click event code that appears:

    MacroToRun Me.CommandButton###.BottomRightCell.Row
    and change ### to the button number, so you end up with, for example

    Private Sub CommandButton7_Click()
    MacroToRun Me.CommandButton7.BottomRightCell.Row
    End Sub

    Then in a regular codemodule, use the passed row

    Sub MacroToRun(r As Long)
    MsgBox "I will now use data from row " & r
    'code here using Worksheets("Sheetname").Cells(r,columnnumber).Value to read the correct value

    End Sub



    HTH, Bernie

    Tuesday, March 27, 2012 3:08 PM
  • Hello Rushdy Najath,

    As an alternative you can use the Forms Controls and have them all call the one sub.

    The buttons can be created with VBA code and sized to fit the cells.

    The button clicked can be identified in the code that is called and also the row number.

    Suggest you try the code in a new workbook.

    Copy all of the the code below into a standard module.

    Run Sub CreateButtons and you will see the buttons created.

    Click any button and a msgbox will return the button name and row number.

    Sub CreateButtons()

        Dim i As Long
        Dim shp As Object
        Dim dblLeft As Double
        Dim dblTop As Double
        Dim dblWidth As Double
        Dim dblHeight As Double
           
        With Sheets("Sheet1")
            dblLeft = .Columns("B:B").Left      'All buttons have same Left position
            dblWidth = .Columns("B:B").Width    'All buttons have same Width
            For i = 2 To 20                     'Starts on row 2 and finishes row 20
                dblHeight = .Rows(i).Height     'Set Height to height of row
                dblTop = .Rows(i).Top           'Set Top top of row
                Set shp = .Buttons.Add(dblLeft, dblTop, dblWidth, dblHeight)
                shp.OnAction = "IdentifySelected"
                shp.Characters.Text = "Sing off"
            Next i
        End With
      
    End Sub


    Sub IdentifySelected()
        'NOTE: The button will always be on the active sheet
        Dim strButtonName
        Dim lngRow As Long
       
        strButtonName = ActiveSheet.Shapes(Application.Caller).Name
        lngRow = ActiveSheet.Shapes(strButtonName).TopLeftCell.Row
       
        MsgBox "Button is on row " & lngRow
    End Sub


    Regards, OssieMac

    Wednesday, March 28, 2012 4:02 AM
  • Hello Rushdy Najath,

    As an alternative you can use the Forms Controls and have them all call the one sub.

    The buttons can be created with VBA code and sized to fit the cells.

    The button clicked can be identified in the code that is called and also the row number.

    Suggest you try the code in a new workbook.

    Copy all of the the code below into a standard module.

    Run Sub CreateButtons and you will see the buttons created.

    Click any button and a msgbox will return the button name and row number.

    Sub CreateButtons()

        Dim i As Long
        Dim shp As Object
        Dim dblLeft As Double
        Dim dblTop As Double
        Dim dblWidth As Double
        Dim dblHeight As Double
           
        With Sheets("Sheet1")
            dblLeft = .Columns("B:B").Left      'All buttons have same Left position
            dblWidth = .Columns("B:B").Width    'All buttons have same Width
            For i = 2 To 20                     'Starts on row 2 and finishes row 20
                dblHeight = .Rows(i).Height     'Set Height to height of row
                dblTop = .Rows(i).Top           'Set Top top of row
                Set shp = .Buttons.Add(dblLeft, dblTop, dblWidth, dblHeight)
                shp.OnAction = "IdentifySelected"
                shp.Characters.Text = "Sing off"
            Next i
        End With
      
    End Sub


    Sub IdentifySelected()
        'NOTE: The button will always be on the active sheet
        Dim strButtonName
        Dim lngRow As Long
       
        strButtonName = ActiveSheet.Shapes(Application.Caller).Name
        lngRow = ActiveSheet.Shapes(strButtonName).TopLeftCell.Row
       
        MsgBox "Button is on row " & lngRow
    End Sub


    Regards, OssieMac

    Thanks a lot this answered the querstion

    buttons are created when i run the CreateButtons but when i clicked on the buttons it througha error telling "Cannot run the macro 'dataquery.xlms!IdefifySelected'. The macro may not be avaliable in this workbook or all macros may be dispaled"

    but i have enable all the macro from File -> Options -> Trust Center -> Trust Center Settings -> Macro Settings -> Enable all macros is selected ?

    so where i have gown worng ?

    Wednesday, March 28, 2012 10:11 AM
  • The only thing that I can think of is that you pasted the code into the worksheet module and not a standard module. The CreateButtons code would run from the worksheet module but the code associated with the button will not.

    When you open the VBA editor, select menu item Insert and then select Module. The code needs to be copied into the standard module because when the button is clicked, it looks in a standard module for the Form controls code.

    If you were creating these buttons manually then when you select the Developer tab and Insert there are 2 lots of controls; Form Controls and ActiveX controls. The buttons created by the code are Form Controls and the code that is assigned to them goes in a standard module.

    The reason for using the Form Controls in lieu of ActiveX Controls is because you can assign the same code to multiple buttons and that cannot be done with ActiveX controls. (Code for ActiveX controls goes in a worksheet module.)

    When the code is copied into the standard module ensure you delete it from the worksheet module.

    As a little extra, if you need to delete the buttons during testing it is a bit of a pain to do so manually so the following is code that will delete all of the controls. (Don't use on a production worksheet that has other controls on it; just in a workbook that you are testing my code because it deletes all controls on the worksheet without discrimination.)

    Sub DeleteShapes()
        Dim shp As Shape
        For Each shp In ActiveSheet.Shapes
            shp.Delete
        Next shp

    End Sub


    Regards, OssieMac


    • Edited by OssieMac Wednesday, March 28, 2012 11:38 AM
    • Marked as answer by Rushdy Najath Thursday, March 29, 2012 10:32 AM
    Wednesday, March 28, 2012 11:32 AM