Microsoft Knowledge Base

• Microsoft Excel for Windows 95, versions 7.0, 7.0a
• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a

SYMPTOMS

In Microsoft Excel, if you create a custom dialog box, and assign a macro to a control in the dialog that changes an element in the dialog box and then displays another dialog box such as a message box, the change to the dialog box is not displayed before the message box is displayed.

CAUSE

This behavior occurs because a dialog box is not updated until the event code assigned to a control is finished running. This is true even if the event code displays a message box, input box, or another dialog box after making the change to the original dialog box.

WORKAROUND

To change an element in a dialog box, such as the text on a button, before displaying a message box or another dialog box, the command to display the message box or other dialog box must be in a different procedure than the event code to change element. Note that this means that you cannot display both the dialog box to change, and the message box on the screen at the same time.

In the following example, the procedure Main displays the dialog box on dialog sheet Dialog1. If the button "Button 4" is chosen, the message box is displayed indicating that the button text has been changed, and then the original dialog box is displayed again.

Microsoft provides examples of Visual Basic for Applications procedures for illustration only, without warranty either expressed or implied, including, but not limited to the implied warranties of merchantability and/or fitness for a particular purpose. The Visual Basic procedures in this article are provided 'as is' and Microsoft does not guarantee that they can be used in all situations. While Microsoft support engineers can help explain the functionality of a particular macro, they will not modify these examples to provide added functionality, nor will they help you construct macros to meet your specific needs. If you have limited programming experience, you may want to consult one of the Microsoft Solution Providers. Solution Providers offer a wide range of fee-based services, including creating custom macros. For more information about Microsoft Solution Providers, call Microsoft Customer Information Service at (800) 426-9400.

   Dim button_number As Integer

   Sub main()
      ' Initialize the variable button_number
      button_number = 1
      ' Display dialog box
      DialogSheets("dialog1").Show

      While button_number > 0
         ' While variable is greater than 0
         ' Display message that button is changed
         ' Then display dialog again
         MsgBox "button changed"
         DialogSheets("dialog1").Show
      Wend
   End Sub

   ' Event code assigned to Button 4 on dialog box
   Sub Button4_Click()
      ' Change property of button to Dismiss
      ActiveDialog.Buttons("Button 4").DismissButton = True
      ' Change button text
      ActiveDialog.Buttons("Button 4").Text = "Changed"
      button_number = 1
   End Sub

   ' Event code assigned to OK and Cancel buttons
   Sub OK_or_Cancel()
      button_number = 0
   End Sub

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

For more information about running a macro from a dialog box control, query on the following words in the Microsoft Knowledge Base:

   show and dialog and stack and event