Macro help: msgbox after refresh complete?

[blueangel2323]

Hi everyone, I have an Excel spreadsheet that is linked to an external data source (table on an intranet page), which is refreshed by clicking on the red exclamation mark. I was wondering if it is possble to have a message box pop up notifying the user when the refresh is complete? I want this to be as user-friendly as possible for non-technical users.

On a side note, how do I make the following code run on only a specific column (let's say A) instead of the entire worksheet, just to speed things up?

Dim cell As Range
    
For Each cell In Worksheets(1).UsedRange
    cell = Trim(Replace(cell, Chr(160), " "))
Next cell

Thanks!!

 

[AlphaFrog]

When you refresh the external data source, you should have a status message in the lower left telling you that the external data source is Connecting-Retrieving-Copying-Ready.

Dim cell As Range

With Worksheets(1)
    For Each cell In [COLOR="Red"].Range("A1", .Range("A" & Rows.Count).End(xlUp))[/COLOR]
        cell = Trim(Replace(cell, Chr(160), " "))
    Next cell
End With

 

[blueangel2323]

When you refresh the external data source, you should have a status message in the lower left telling you that the external data source is Connecting-Retrieving-Copying-Ready.

Dim cell As Range
 
With Worksheets(1)
    For Each cell In [COLOR=red].Range("A1", .Range("A" & Rows.Count).End(xlUp))[/COLOR]
        cell = Trim(Replace(cell, Chr(160), " "))
    Next cell
End With

"Compile error: Invalid or unqualified reference"

 

[blueangel2323]

Oops, never mind, it works. Thanks!

 

[blueangel2323]

I would still be interested in having the pop-up msgbox though, to make it more conspicuous for users who aren't technically inclined

 

[AlphaFrog]

This will display a message after the External Data Source has refreshed. It uses the Worksheet_Change event to trigger the message. Change the cell reference in red to the first cell in the range on the worksheet that has your External Data Source.

To install the macro...

• Right-click on the sheet tab
• Select View Code in the pop-up menu
• Paste the code below in the VBA edit window

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("[COLOR="Red"]B2[/COLOR]"), Target) Is Nothing Then
        MsgBox "Refresh complete. ", , "External Data Source Status"
    End If
End Sub

Caveat: If the user manually edits or changes the one cell in red, they will trigger the message box again.

 

[blueangel2323]

Thanks