How to run a macro by clicking a cell

Question

I am trying to run a macro by clicking in a cell A10. Getting Run-time error '1004': Select method of Range class failed.

"debug" highlights the 4th line down:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("A10"), Target) Is Nothing Then
Sheets("OT Requests & Hours").Select
**Range("B5:AA22").Select**
ActiveWorkbook.Worksheets("OT Requests & Hours").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("OT Requests & Hours").Sort.SortFields.Add2 Key:= _
    Range("C5:C22"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption _
    :=xlSortNormal
ActiveWorkbook.Worksheets("OT Requests & Hours").Sort.SortFields.Add2 Key:= _
    Range("Z5:Z22"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal
ActiveWorkbook.Worksheets("OT Requests & Hours").Sort.SortFields.Add2 Key:=
    Range ("AA5:AA22"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
    :=xlSortNormal
With ActiveWorkbook.Worksheets("OT Requests & Hours").Sort
    .SetRange Range("B5:AA22")
    .Header = xlGuess
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With
Sheets("Sun").Select
Range("A10").Select
ActiveCell.FormulaR1C1 = _
    "=IF(AND(R[2]C[4]=""OT"",'OT Requests & Hours'!R[-5]C[2]=""Mid""),'OT Requests & Hours'!R[-5]C[1],"""")"
Range("A11").Select
End If
End Sub

You should [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). — BigBen, Oct 21 '22 at 17:38
What is the worksheet (tab) name of the worksheet's sheet module containing this code? `OT Requests & Hours`, `Sun`, or something else? — VBasic2008, Oct 21 '22 at 17:44

VBasic2008 · Answer 1 · 2022-10-21T18:25:54.723

A Worksheet Change: Sorting Another Worksheet

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    
    Const otName As String = "OT Requests & Hours"

    Dim SunCell As Range: Set SunCell = Intersect(Me.Range("A10"), Target)
    If SunCell Is Nothing Then Exit Sub

    Dim wsOT As Worksheet: Set wsOT = Worksheets(otName)
    Dim rgOT As Range: Set rgOT = wsOT.Range("B4:AA22")

    With wsOT.Sort
        .SortFields.Clear
        .SortFields.Add2 Key:=rgOT.Columns(2), SortOn:=xlSortOnValues, _
            Order:=xlDescending, DataOption:=xlSortNormal
        .SortFields.Add2 Key:=rgOT.Columns(25), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortNormal
        .SortFields.Add2 Key:=rgOT.Columns(26), SortOn:=xlSortOnValues, _
            Order:=xlAscending, DataOption:=xlSortNormal
        .SetRange rgOT
        .Header = xlYes ' or 'xlNo'; if you know it, don't let Excel guess!
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Application.EnableEvents = False ' to not retrigger the event
        ' Write the formula.
        SunCell.FormulaR1C1 = "=IF(AND(R[2]C[4]=""OT"",'" & otName _
            & "'!R[-5]C[2]=""Mid""),'" & otName & "'!R[-5]C[1],"""")"
    Application.EnableEvents = True

    ' Select the cell below. This will never allow you to select 'SunCell'!
    Application.Goto SunCell.Offset(1)

End Sub

How to run a macro by clicking a cell

1 Answers1

A Worksheet Change: Sorting Another Worksheet