MXL5: Option Button and Check Box Labels Don't Update Properly

ID: Q147316

The information in this article applies to:

SYMPTOMS

When you create a dynamic custom dialog box in Microsoft Excel for the Macintosh, versions 5.0 and 5.0a, and one action of the dynamic trigger control changes labels on option buttons or check boxes, you may notice that text from a previous label is not replaced by the new label.

WORKAROUND

Microsoft provides programming examples 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. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft Support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web: 

   http://www.microsoft.com/support/supportnet/refguide/default.asp
To work around this problem, have your dynamic dialog box redrawing Sub procedures call the following Visual Basic for Applications routine (or something similar) whenever you need to rewrite an option button or check box label: 

   ' Make sure to call this procedure after assigning the new labels to the
   ' controls. This Sub Procedure has two arguments: Ctrl is the control 
   ' and NumToBlank is the number of spaces necessary to cover up the 
   ' longest possible caption for the control.

   Sub ClearLabels(Ctrl as Object, NumToBlank as Integer)
       ' Follow the current label with a lot of spaces.
       Ctrl.Caption = Ctrl.Caption & _
           String(NumToBlank - Len(Ctrl.Caption), "    ")
   End Sub
To call this procedure within a Visual Basic for Applications module and have it apply to an option button named "Option1" (without the quotation marks) on the Active Dialog, and to pad the label with 30 spaces, include a call in the procedure that writes the labels as follows: 

   ClearLabels ActiveDialog.OptionButtons("Option1"), 30
Keep in mind that with proportional fonts, it may take more than 10 spaces to obscure 10 wider characters (such as the "#" character). Also keep in mind that the ClearLabels procedure must be called after making the assignment of the new label caption.

If you prefer to have one procedure fix all of the option buttons at once by looping through them, you can create a procedure such as the following: 

   ' Make sure to call this after assigning the new labels to the controls.

   Sub Clear_Labels()
       ' Dimension a variable to hold the objects.
       Dim Control As Object
       ' Sets up a loop to run through every option button in the active
       ' dialog. Change ".OptionButtons" to ".CheckBoxes" if working with
       ' check boxes.
       For Each Control In ActiveDialog.OptionButtons
           ' Set the caption equal to itself with 35 spaces after it.
           ' You may need to edit this line to increase or decrease the
           ' number of characters in the final label. Keep in mind that
           ' with proportional fonts, it may take more than 10 spaces to
           ' cover up 10 other characters.
           Control.Caption = Control.Caption & _
               String(35 - Len(Control.Caption), " ")
       ' Loop through the code.
       Next Control
   End Sub

STATUS

Microsoft has confirmed this to be a problem in the products listed at the beginning of this article. This problem was corrected in Microsoft Excel 98 Macintosh Edition.

REFERENCES

For more information about affecting controls and dialog boxes in Visual Basic for Applications, consult the "Visual Basic User's Guide," Chapter 11 "Controls and Dialog Boxes."

Additional query words: 5.00 5.00a CheckBoxes Check Boxes Mac XL5 

Keywords          : kbcode kbprg PgmOthr 
Version           : MACINTOSH:5.0,5.0a
Platform          : MACINTOSH
Issue type        : kbbug
Solution Type     : kbpending

Last Reviewed: May 19, 1999