Avoid Table selection on Query Refresh - Power Query

[Anonymous]

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 `Application.Goto Sh.Cells(1,"A")` to remove the table selection, but it seems it does not work! Any idea how to avoid getting the Table selected on Query refresh?

Dim Con as String

If Target.Address = "$B$2" Or Target.Address = "$C$2" Then
    Con = "Query - Rpt5"
End If

With ThisWorkbook.Connections(Con).OLEDBConnection
    .BackgroundQuery = False
    .Refresh
End With

'Application.Goto Sh.Cells(1,"A")
Sh.Cells(1,"A").Select

[CNENFRNL]

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]

@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]

@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...😂

[Fowmy]

@Anonymous 

I tried and the table did not get selected using your code for me. I noticed that the refresh command runs regardless of your If condition. And, where does your table begin, in which address of the sheet?

Can you try the following

Dim Con as String

If Target.Address = "$B$2" Or Target.Address = "$C$2" Then
    Con = "Query - Rpt5"
With ThisWorkbook.Connections(Con).OLEDBConnection
    .BackgroundQuery = False
    .Refresh
End With

'Application.Goto Sh.Cells(1,"A")
Cells(1,"A").Select
End If

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[Anonymous]

@Fowmy  B2 & C2 are the dropdowns and the table begins from B4 onwards.

No matter what i do to move the selection object to some other cell on worksheet, whenever the query refreshes, the table rows get selected automatically.

[Fowmy]

@Anonymous 

If you can share a dummy Excel file with the same scenario, it will help me resolve it faster.

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.
How to get your questions answered quickly?
_____________________________________
Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Anonymous]

Sure @Fowmy . Here is the attached SampleBook.xlsm file for your perusal.

[Jimmy801]

Hello @Anonymous 

when I'm trying to change B2, my table it's not selected afterwards. I also never experienced that behavior. What you can try is this

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"
    
        With ThisWorkbook.Connections(Con).OLEDBConnection
            .BackgroundQuery = False
            .Refresh
        End With
        
        Application.OnTime Now() + TimeSerial(0, 0, 1), "SelectB2"
        
        'Application.Goto Sh.Cells(2,"B")
        'Sh.Cells(2, "B").Select
    End If

End Sub

this procedure you put in a new modul

Sub SelectB2()
    Sheet1.Cells(2, 2).Select
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

[Anonymous]

@Jimmy801 , unfortunately, it still selects the entire table rows. 😞

I am not sure if this behaviour is restricted to only my Excel as i have been seeing this since a long time now. Is there some setting in Excel or Power Query that could be causing this selection behaviour?

[Jimmy801 · Accepted solution]

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

[Anonymous]

@Jimmy801 thanks. This does the job for now. The table gets selected first (i think query runs before vba) and then the selection goes to cell B2 after 3 secs.

Though still i would want to know why this is happening on my Power Query in Excel 2016 Pro 64-bit vs not happening on anyone else's Excel.

[Jimmy801]

Hello @Anonymous 

then i would appreciate if you could mark it as solution.

This is no specific Excel-forum, and as this i not happening in Excel 365, seems like that this is standard behaviour of Excel 2016

BR

Jimmy

[Jimmy801]

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