Message after Connection has refreshed

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows
I have a very simple piece of VBA which sets some slicers, refreshes a connection and puts a message on the status bar, but I have noticed one message doesn't go away.

This connection is not critical to the running of the spreadsheet as it just gives an overview when required - usually on by me.
however, I would like it to disappear when the refresh is complete but can see no way of doing this - it just stays resident on the status bar.

VBA Code:
Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)
On Error Resume Next
Dim sc1 As SlicerCache
Dim sc2 As SlicerCache
Dim SI1 As SlicerItem

' These names come from Slicer Settings dialog box
Set sc1 = ThisWorkbook.SlicerCaches("Slicer_Sales_Office")
Set sc2 = ThisWorkbook.SlicerCaches("Slicer_Customer_Region_Name")
    
sc2.ClearManualFilter

For Each SI1 In sc1.SlicerItems
sc2.SlicerItems(SI1.Name).Selected = SI1.Selected
Next SI1

Application.StatusBar = "Calculating workbook..."
Calculate

Columns("F:F").ColumnWidth = 4

Application.StatusBar = "Refreshing Data..."
ActiveWorkbook.Connections("Query - Dataset").Refresh
[B][COLOR=rgb(184, 49, 47)]'What can I here to make the message go away when the refresh has completed?[/COLOR][/B]

End Sub
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

jkpieterse

Well-known Member
Joined
Dec 3, 2007
Messages
1,100
Like so:
Code:
Application.StatusBar = "Refreshing Data..."
ActiveWorkbook.Connections("Query - Dataset").Refresh False
Application.StatusBar = False
 

MartinL

Well-known Member
Joined
Oct 16, 2008
Messages
1,110
Office Version
  1. 365
Platform
  1. Windows
Hmm didn't like the False after the ActiveWorkbook line
But not to worry Application.StatusBar = False does work
 

Watch MrExcel Video

Forum statistics

Threads
1,130,048
Messages
5,639,769
Members
417,111
Latest member
buyukbang

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top