Post
Ask Question
First Last

First Last

  • NA
  • 648
  • 70k

Want to capture the cell letter of the cell a button is sitting on

Apr 14 2023 7:26 PM

I want to capture the cell letter of the cell a button is sitting on.

I find that I have to click the cell first BEFORE I then click the button to get then get the cell letter.

    location = ActiveCell.Address

Is there away to get the 'cell letter' of where the button sits when just (only) clicking the button (regardless of the active cell)?

 

Sub Button_Click()
    Dim location As String
    Dim cellLetter As String
    Dim CalculateTaxes As Workbook
    Dim incomeCell As Range
    Dim fedTaxCell As Range
    Dim stateTaxCell As Range
    Dim medicareCell As Range
    Dim ssCell As Range

    ' Get the location of where the button is that was clicked.

    ' BUT THIS IS NOT WHAT I WANT AS IT TAKES THE ACTIVE CELL WHICH MIGHT NOT BE THE CELL OF THE BUTTON I CLICK.

    location = ActiveCell.Address

    ' Get the cell letter from the location.
    cellLetter = Mid(location, 2, 1)

    ' Open the child "2023 - Calculate taxes" workbook.
    Set CalculateTaxes = Workbooks.Open("C:\Dans\Personal\Sergio\Business\Income and expense\2023\2023 - Calculate taxes.xlsm")

    ' Get references to the cells in the child "2023 - Calculate taxes" workbook.
    ' Get the values from the cells.
    Set incomeCell = CalculateTaxes.Worksheets("CalcTaxes").Range("G5")
    Set fedTaxCell = CalculateTaxes.Worksheets("CalcTaxes").Range("B30")
    Set stateTaxCell = CalculateTaxes.Worksheets("CalcTaxes").Range("B31")
    Set ssCell = CalculateTaxes.Worksheets("CalcTaxes").Range("B32")
    Set medicareCell = CalculateTaxes.Worksheets("CalcTaxes").Range("B33")
       
    ' Set the child's "Total Income" cell's amount to the parent's value in the parent workbook's sheet.
    incomeCell.Value = ThisWorkbook.Worksheets("ForIrsIncomeAndExpenses").Range(cellLetter & "12").Value

     ' Get the calculated values from the child "2023 - Calculate taxes" workbook and set the values in the parent workbook's sheet.
    ThisWorkbook.Worksheets("ForIrsIncomeAndExpenses").Range(cellLetter & "17").Value = fedTaxCell.Value
    ThisWorkbook.Worksheets("ForIrsIncomeAndExpenses").Range(cellLetter & "18").Value = stateTaxCell.Value
    ThisWorkbook.Worksheets("ForIrsIncomeAndExpenses").Range(cellLetter & "19").Value = ssCell.Value
    ThisWorkbook.Worksheets("ForIrsIncomeAndExpenses").Range(cellLetter & "20").Value = medicareCell.Value

    ' Force the child "2023 - Calculate taxes" to not save any changes and not prompt for it.
    CalculateTaxes.Saved = True
        
    ' Close the child "2023 - Calculate taxes" workbook.
    CalculateTaxes.Close
End Sub


Answers (5)