Wandering shapes

[jkmblogs]

Hoping for some help here.
I have checkboxes & buttons on a spread sheet.
If I insert an entire row from above the controls (say from row 1) the controls lose there place and in some cases pile on top of one another.

What makes this impossible to solve is for the same file...
No issues on my laptop running Excel v2019
But on my PC (which has both v2010 & v2019 installed
- v2010: No issues
- v2019: The controls wander!

Any suggestions?

 

[rlv01]

You should inspect the Object Position property for the button or checkbox.

 

[jkmblogs]

Hi,
Thanks for your reply but that is not the issue.
I think it is a registry/installation issue as it doesn't arise on other PC's or other Excel installations.

To confirm issue the issue I:

• Created a new blank workbook
• Inserted a checkbox in a cell on row 10 (to move but don't size with cells). It functions as you would expect
• I then insert an entire row above. Everything looks as you would expect as it has moved to row 11... but when you click it, it functions but while mouse button is down a second checkbox appears in the row below (without text label). Let's call this 2nd checkbox the "anchor".
• If file is saved and reopened, nothing appears on line 11... Both checkbox & label are positioned on row 12. It functions normally and there is only one checkbox.
• Interestingly, if I insert say five rows at once then from line 10 the checkbox moves to row 15 but the "anchor" is on row 20. If I then insert a single row, the checkbox is on row16 and the "anchor" jumps to row 17.

This behavior also happens for other form controls and also for inserting columns.

I have done a quick repair of office but it didn't fix the issue.
I might have to try an online repair but don't really want to as I would loose my settings .

Am open to ideas (especially a fix)!

Has anyone else ever experienced this???

 

[rlv01]

Are these checkboxes form controls or Active-X controls?

 

[jkmblogs]

Are these checkboxes form controls or Active-X controls?

Form controls.
BUT...
Just tested with ActiveX and that exhibits same behavior.

 

[rlv01]

<<If file is saved and reopened, nothing appears on line 11>>

That suggests it might be a screen artifact. Since you say it does the same thing with an active-X checkbox, I'd use that to add some test code to a new worksheet with just one checkbox and the following event code

Option Explicit

Dim Msg As String


Private Sub CheckBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim T, L, W, H
    Dim S As String
    
    With Me.CheckBox1
        T = .Top
        L = .Left
        W = .Width
        H = .Height
    End With
    
    S = "MouseDown" & " (Top: " & T & ", Left: " & L & ")"
    Msg = S
End Sub

Private Sub CheckBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim T, L, W, H
    Dim S As String
    
    With Me.CheckBox1
        T = .Top
        L = .Left
        W = .Width
        H = .Height
    End With
    
    S = "MouseUp" & " (Top: " & T & ", Left: " & L & ")"
    Msg = Msg & vbCr & S
End Sub

Private Sub CheckBox1_Click()
    Dim T, L, W, H
    Dim S As String
    Dim OLEObj As OLEObject
    Dim CCnt As Integer
    
    With Me.CheckBox1
        T = .Top
        L = .Left
        W = .Width
        H = .Height
    End With
    
    S = "Click" & " (Top: " & T & ", Left: " & L & ")"
    Msg = Msg & vbCr & S
    
    For Each OLEObj In Me.OLEObjects
        If Left(OLEObj.Name, 8) = "CheckBox" Then
        CCnt = CCnt + 1
        End If
    Next OLEObj
    
    MsgBox Msg & vbCr & vbCr & "Checkboxes in worksheet: " & CCnt
End Sub

 

[jkmblogs]

<<If file is saved and reopened, nothing appears on line 11>>

That suggests it might be a screen artifact. Since you say it does the same thing with an active-X checkbox, I'd use that to add some test code to a new worksheet with just one checkbox and the following event code

Option Explicit

Dim Msg As String


Private Sub CheckBox1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim T, L, W, H
    Dim S As String
   
    With Me.CheckBox1
        T = .Top
        L = .Left
        W = .Width
        H = .Height
    End With
   
    S = "MouseDown" & " (Top: " & T & ", Left: " & L & ")"
    Msg = S
End Sub

Private Sub CheckBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Dim T, L, W, H
    Dim S As String
   
    With Me.CheckBox1
        T = .Top
        L = .Left
        W = .Width
        H = .Height
    End With
   
    S = "MouseUp" & " (Top: " & T & ", Left: " & L & ")"
    Msg = Msg & vbCr & S
End Sub

Private Sub CheckBox1_Click()
    Dim T, L, W, H
    Dim S As String
    Dim OLEObj As OLEObject
    Dim CCnt As Integer
   
    With Me.CheckBox1
        T = .Top
        L = .Left
        W = .Width
        H = .Height
    End With
   
    S = "Click" & " (Top: " & T & ", Left: " & L & ")"
    Msg = Msg & vbCr & S
   
    For Each OLEObj In Me.OLEObjects
        If Left(OLEObj.Name, 8) = "CheckBox" Then
        CCnt = CCnt + 1
        End If
    Next OLEObj
   
    MsgBox Msg & vbCr & vbCr & "Checkboxes in worksheet: " & CCnt
End Sub

Thanks so much for taking the time & effort to respond.
I haven't tried your code but suspect it would work as when I select the original shape (not the "anchor") and move it slightly it remembers where it's supposed to be and the anchor is gone.
The sad thing with a vba fix is that I suspect it may have to be run for all controls on all spreadsheet changes.

I came to work today to try an online repair but first decided to check the behavior was still occurring.
Couldn't believe it... it is now behaving as it should!!!
No idea why as the errant behavior was evidenced over many days!

Obvious thought was to view my update history...
Only Microsoft Defender Antivirus.

Then had a look at recent installs per Revo... only
Microsoft Edge WebView2 Runtime
Microsoft Edge
Ashampoo Snap

Hopefully now happy days, but if it starts happening again, will try your code.

Thanks again