Screen Refresh/Repaint in VBA

[drobles]

I have inserted several command buttons on an Excel 2003 worksheet. Each button executes a separate VBA macro. I would like one of these macros to make one of the command buttons invisible. The target button (called "Update") will become visible again if the user modifies any data in the worksheet.

Everything works fine, except that the target button remains visible - although inactive - after the code is executed. If I minimize the Excel application window and then maximize it again, the Update button vanishes, as it should. This suggests that the screen must be refreshed after the macro is executed in order to make the button invisible to the user.

I have seen other posts related to this problem (http://www.excelforum.com/excel-prog...ml#post2188184), but I'm having difficulty implementing the solution myself.

If I add the following line to my macro:

Please Login or Register  to view this content.

I get the following error box:

Compiling error. Could not find the method or data member.

It seems that the Repaint method is not available to me in Excel VBA. Have I misunderstood how to use this method? I'm not using a userform, but just working with command buttons directly in the Excel spreadsheet. Thanks in advance for any help.

[romperstomper]

Repaint does not work with worksheets. Setting Application.ScreenUpdating = True should cause a screen refresh. If you are using buttons from the Control Toolbox it might not work with those.

[drobles]

You're right, Romperstomper. Adding

Please Login or Register  to view this content.

does not refresh the buttons from the Control Toolbox.

[romperstomper]

Unless you specifically need the added functionality of the ActiveX ones, I would always use controls from the Forms toolbar in worksheets instead. They are more stable.
As a matter of interest, what code do you use to hide the buttons?

[drobles]

My codes lines for making the "Update" button invisible:

Please Login or Register  to view this content.

[romperstomper]

Is that code in the click event for the same button, or for another button?

[drobles]

It is in the click event for the same button. In other words, when the user clicks the Update button, a series of calculations are performed, the output is sent to another worksheet, and then the button goes invisible. The button becomes visible again if any data in the sheet is modified.
So, by the presence or absence of the Update button, the user knows whether the calculations are updated or not.

[DaveSA]

Hi Folks, try this it works for me on both Form and ActiveX controls:

Application.EnableEvents=true

..... make your changes to the tools on the worksheet......

DoEvents

[rcjcooke]

Hi all,

I'm afraid I've not found any of the above methods mentioned work for ActiveX controls however for run-time customisation reasons I need to use ActiveX controls over a Form control. As a result I've been struggling with this for a while and the only solution I've found to work so far is to call the "Activate" method on the control which seems to include a repaint of the control (presumably because it adds the focus box to the control). The change of focus on the worksheet isn't a problem in my context, though I appreciate that's not the case for everyone. Anyway, hope this helps anyone else still struggling with this.

The code I end up using therefore looks like:

Please Login or Register  to view this content.