How can I ensure that a Shape will appear on top of a Command Button in my Worksheet?-CodePudding

I have a toolbar with ActiveX command buttons for automating email generation. One of the buttons, ROV, stands for “Reconsideration of Value” and has to do with appraisals. Users tend to forget what ROV stands for, so I created an ActiveX Shape as a “ToolTip” to display when the mouse hovers over the ROV button.

All is fine initially.

However, if one of the two command buttons to the right of ROV are clicked, afterwards if the mouse hovers over the ROV button, the previous command button stands out in front when the “ToolTip” Shape appears (as shown below).

I’ve toyed with the ZOrder property, which doesn’t appear to function under VBA (I’ve discovered it applies to InterOp which is part of VB.net).

Below is my code:

 Private Sub CommandButton18_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal x As Single, ByVal y As Single)
     Const lngSensitivity As Long = 5
     Dim i As Integer
     
     With CommandButton18
         If (x > lngSensitivity And x < .Width - lngSensitivity And y > lngSensitivity And y < .Height - lngSensitivity) Then
             With Worksheets("Sheet1")
                 With .Shapes("Rectangle 1")
 '                    .ZOrder (msoBringToFront)
                     .Fill.Transparency = 0
                     .Line.Transparency = 0
                 
                     For i = 1 To 200  'Fade out shape and text.
                         .Fill.Transparency = i / 200
                         .TextFrame2.TextRange.Characters.Font.Fill.Transparency = i / 200
                         .Line.Transparency = i / 200
                         DoEvents
                     Next
                 End With
             End With
         End If
    End With
 End Sub

Could someone please help?

Screen Shots

CodePudding user response：

Shape.ZOrder exists in Excel VBA. The ZOrder Position Enumeration to use with the method is listed here.

For bringing an object to the top, you should use msoBringForward (not msoBringToFront).

CodePudding user response：

After retrying the ZOrder property and not achieving my goal, I noticed that when I reinitialized the application (i.e., launched Workbook_Open) while the application was still open, it would resolve the issue and the Shape would then appear above the Command Button(s). Through trial and error I found the only line that needed to be added to effect this change.

Revisiting the following section:

             With Worksheets("Sheet1")
                 .Range("U1").Select  ‘Position cursor.  ==> ADD THIS LINE.
                 With .Shapes("Rectangle 1")
 '                    .ZOrder (msoBringToFront)          ==> Eliminate this line, as it serves no useful purpose.
                     .Fill.Transparency = 0
                     .Line.Transparency = 0
                 
                     For i = 1 To 200  'Fade out shape and text.
                         .Fill.Transparency = i / 200
                         .TextFrame2.TextRange.Characters.Font.Fill.Transparency = i / 200
                         .Line.Transparency = i / 200
                         DoEvents
                     Next
                 End With
             End With

I have no idea why this works, but it does - and, at the end of the day, that’s all that matters.