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

Fowmy's avatar
Fowmy
Super User
5 years ago

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's avatar
Anonymous
Not applicable
5 years ago

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

 

  • Jimmy801's avatar
    Jimmy801
    Community Champion
    5 years ago

    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        Jimmy801
        Community Champion
        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