none
read cell values on button clicked Excel (2010) RRS feed

  • Question

  • Hi there
    I am working on a excel sheet  ruing some VBA codes  there is a button on each row in cell M
    What I wanted is how can I get the value of all the cells in that row on button click
    i.e on button click I wanted to get the values in cell A,B,C,D..ect related to the row
    thanks for your helps and assistance
    Wednesday, March 28, 2012 1:39 PM

Answers

  • I assume that this is a follow on from your previous post that I answered and that you have sorted your problem with my answer.

    For benefit of Learning and Learning the previous post URL.

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/e57fa058-1912-43bf-84d0-29e32ece4bb5/

    The following is a modification of the sub that runs with click of button and returns the value in column A to the variable VarCellValue. Note: Because I have used "With ActiveSheet" there is a leading dot in command .Cells(lngRow, "A")

    If this does not answer your question, as per reply by Learning and Learning, let us know exactly what you want to do with the cells in the other columns.

    Sub IdentifySelected()
        'NOTE: The button will always be on the active sheet
        Dim lngRow As Long
        Dim strButtonName As String
        Dim varCellValue As Variant
       
        With ActiveSheet
            strButtonName = .Shapes(Application.Caller).Name
            lngRow = .Shapes(strButtonName).TopLeftCell.Row
           
            varCellValue = .Cells(lngRow, "A")      'Replace "A" with column required.

            'Added with Edit: Example to address a range of cells across multiple columns in the row _
            'Remove the comment (single quote) and insert quote previous code line to test
            'varCellValue = WorksheetFunction.Sum(.Range(.Cells(lngRow, "A"), .Cells(lngRow, "D")))
           
       End With

    End Sub


    Regards, OssieMac


    • Marked as answer by Rushdy Najath Thursday, March 29, 2012 8:51 AM
    • Edited by OssieMac Thursday, March 29, 2012 8:53 AM Added code to address range of cells in row
    Thursday, March 29, 2012 4:15 AM

All replies

  • Your question need to be clear.

    1.What you mean by "get the value of all the cells in ".Will you put in a variable/cell or show in msgbox.

    2.And how you want the value of cells to appear.Will they be summed up or shown as string separated by comma or something like,or put in an array.

    3.What type of Control ? Form Control or ActiveX Control.

    How you inserted the button (Manually ?It can be done by VBA

    Wednesday, March 28, 2012 2:18 PM
    Answerer
  • I assume that this is a follow on from your previous post that I answered and that you have sorted your problem with my answer.

    For benefit of Learning and Learning the previous post URL.

    http://social.msdn.microsoft.com/Forums/en-US/isvvba/thread/e57fa058-1912-43bf-84d0-29e32ece4bb5/

    The following is a modification of the sub that runs with click of button and returns the value in column A to the variable VarCellValue. Note: Because I have used "With ActiveSheet" there is a leading dot in command .Cells(lngRow, "A")

    If this does not answer your question, as per reply by Learning and Learning, let us know exactly what you want to do with the cells in the other columns.

    Sub IdentifySelected()
        'NOTE: The button will always be on the active sheet
        Dim lngRow As Long
        Dim strButtonName As String
        Dim varCellValue As Variant
       
        With ActiveSheet
            strButtonName = .Shapes(Application.Caller).Name
            lngRow = .Shapes(strButtonName).TopLeftCell.Row
           
            varCellValue = .Cells(lngRow, "A")      'Replace "A" with column required.

            'Added with Edit: Example to address a range of cells across multiple columns in the row _
            'Remove the comment (single quote) and insert quote previous code line to test
            'varCellValue = WorksheetFunction.Sum(.Range(.Cells(lngRow, "A"), .Cells(lngRow, "D")))
           
       End With

    End Sub


    Regards, OssieMac


    • Marked as answer by Rushdy Najath Thursday, March 29, 2012 8:51 AM
    • Edited by OssieMac Thursday, March 29, 2012 8:53 AM Added code to address range of cells in row
    Thursday, March 29, 2012 4:15 AM
  • thanks yes it works
    Thursday, March 29, 2012 8:51 AM