Macro message / Dismiss clipboard message

[ChrisY]

I a workbook with VBA code that opens and checks the value of a cell in another worksheet located on a server. The idea is that, if I change the cell value on the server worksheet, I can compare the value to the opening worksheet and copy the new data to the opening worksheet if it has changed. All works well with that; however, I would like to give the user a message "Checking for Updates" that stays open while the VBA code is running - maybe with a progress bar(?). Is this possible?

Also, when a update is performed, I get a message to the effect that "You have placed a large amount of information on the Clipboard. Do you want the information to be available...? I want to dismiss the message with a "No" response automatically. Any way to do this?

Thanks for your help.

ChrisY

 

[Douglas]

On 2002-02-28 08:43, ChrisY wrote:
I a workbook with VBA code that opens and checks the value of a cell in another worksheet located on a server. The idea is that, if I change the cell value on the server worksheet, I can compare the value to the opening worksheet and copy the new data to the opening worksheet if it has changed. All works well with that; however, I would like to give the user a message "Checking for Updates" that stays open while the VBA code is running - maybe with a progress bar(?). Is this possible?

Also, when a update is performed, I get a message to the effect that "You have placed a large amount of information on the Clipboard. Do you want the information to be available...? I want to dismiss the message with a "No" response automatically. Any way to do this?

Thanks for your help.

ChrisY

Chris,

You can add a message to the Status bar at the bottom of the screen:

Application.StatusBar = "your message"
at the end of your code, remember to set this back to
Application.StatusBar = False

The following code will stop the other message appearing - but I don't know if it assumes a yes or a no!
Application.DisplayAlerts = False
(again, this needs to be set to true at the end of your code).

Doug

 

[ChrisY]

Douglas,

Thanks! That worked on the first part. I found a workaround for the clipboard problem - apparently it only happens when you copy over 100 cells. The fix is to tell Excel to copy "a" cell prior to closing, then the notice is not produced.

CY
This message was edited by ChrisY on 2002-02-28 11:17

 

[Guest]

On 2002-02-28 11:15, ChrisY wrote:
Douglas,

Thanks! That worked on the first part. I found a workaround for the clipboard problem - apparently it only happens when you copy over 100 cells. The fix is to tell Excel to copy "a" cell prior to closing, then the notice is not produced.

CY
This message was edited by ChrisY on 2002-02-28 11:17

Thats one way other wise just clear the clipboard via
Application.CutCopymode = false

at the end of any copy/pates routine.

Ivan