Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Avoid Table selection on Query Refresh - Power Query

I have a couple of Power Query tables that i refresh on selecting dropdowns with VBA in `Workbook_SheetChange()`. However, whenever the Query refreshes, the table gets selected. I tried using `Applic...
Need Help
Show and Tell
Tips and Tricks
  • Jimmy801's avatar
    Jimmy801
    5 years ago

    Hello  Anonymous 

     

    try this code

     

    Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

    Dim Con As String
    
    If Target.Address = "$B$2" Then
        Con = "Query - tblOriginal"

    
        Sheet1.ListObjects(1).QueryTable.Refresh BackgroundQuery:=False
        

        
        Application.OnTime Now() + TimeSerial(0, 0, 3), "SelectB2"
        
        'Application.Goto Sh.Cells(2,"B")
        'Sh.Cells(2, "B").Select
    End If

End Sub

     

     

    If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
    Kudoes are nice too

    Have fun

    Jimmy

CNENFRNL's avatar
CNENFRNL
Community Champion
5 years ago

Hi, Anonymous ,

Generally speaking, it's a common practice to embed codes in an event to prevent recursive event call,

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False

'event process

Application.EnableEvents = True

End Sub

 

Anonymous's avatar
Anonymous
Not applicable
5 years ago

CNENFRNL this is not ideal to do, as when there is an error, Application.EnableEvents being FALSE, the event code won't get triggered. One will have to manually set Application.EnableEvents to TRUE for the dropdowns to trigger the event code.

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    5 years ago

    Anonymous , since you mentioned the error-handling in VBA in this regard, here's a full pattern which I usually include in my subroutines,

     

    Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo errHdlr
Call AppToggles
Dim obj As Object

'event process

errHdlr:
    If Err.Number <> 0 Then Debug.Print Err.Description
    Call AppToggles(True, True, True, xlCalculationAutomatic)
    set obj = Nothing
End Sub

Sub AppToggles(Optional ScrUpdating As Boolean = False, _
               Optional DispAlerts As Boolean = False, _
               Optional Events As Boolean = False, _
               Optional Cal As XlCalculation = xlCalculationManual)
On Error Resume Next
With Application
   .ScreenUpdating = ScrUpdating
   .DisplayAlerts = DispAlerts
   .EnableEvents = Events
   .Calculation = Cal
End With
End Sub

     

    Seems it's a bit off track from your thread...😂