Buttons that move

[RyderS]

Hi all,
I need to be able to have a user click and drag buttons, labels, etc, by click and drag. I expect I need to do this in VBA. I am open to creating this all on a UserForm if that works better.

The idea here is to allow a user to move these bits around WITHOUT using the excel object editing tools.

It's Excel, so I am sure it can be done

Thanks!
R

 

[Yongle]

This moves CommandButton1 to the top left corner after scrolling and selecting a cell

place in worksheet's code module

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    With Shapes("CommandButton1")
        .Top = Rows(ActiveWindow.ScrollRow).Top + 3
        .Left = Columns(ActiveWindow.ScrollColumn).Left + 3
    End With
End Sub

 

[Yongle]

Double click on CommandButton1, select a cell with mouse, click OK and CommandButton1 goes to that cell

Place in worksheet code module

Private Sub CommandButton1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim cel As Range
    Set cel = Application.InputBox("Select cell", , , , , , , 8)
    With Shapes("CommandButton1")
        .Top = cel.Top
        .Left = cel.Left
    End With
End Sub

 

[RyderS]

Thanks Yongle...
This seems to address part of the problem... but the solutions only do a single move from one location to another.

What I need is to *drag* the object in the same fashion as you would move an icon from one part of your desktop to another. It's essentially a drag-and-drop operation, where the drop event defines the new location of the object.

The trick here will be how to "attach" the object to the pointer... then update its location as the pointer moves... finally setting it when a mouse button is released.

I hope this improves the understanding of what I am trying to accomplish. Thank you!

R

 

[Yongle]

It's easy enough to get Picture1 to drag and drop...

Place in worksheet code module

Private Sub Picture1_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
    a = t
    b = l
End Sub
     
Private Sub Picture1_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    EnterExitDesignMode
    If Button = 1 Then
        Picture1.Top = Picture1.Top - (a - t)
        Picture1.Left = Picture1.Left - (b - t)
    End If
End Sub

 

[Jaafar Tribak]

Hi all,
I need to be able to have a user click and drag buttons, labels, etc, by click and drag. I expect I need to do this in VBA. I am open to creating this all on a UserForm if that works better.

The idea here is to allow a user to move these bits around WITHOUT using the excel object editing tools.

It's Excel, so I am sure it can be done

Thanks!
R

If these are controls on a userform, you could use a class module to sink the Mousemove event of each control :

The mousemove event handler would have the following generic code :

Private Sub MoveHandler(ByVal Ctrl As Object, ByVal Button As Integer, ByVal X As Single, ByVal Y As Single)

    With Ctrl
        If Button = 1 Then .Move (X + .Left - (.width / 2)), (Y + .Top - (.height / 2))
    End With

End Sub

Alternatively, you could just edit the DesignMode Property of the userform as follows :

Code in the UserForm Module:

Option Explicit

Private Sub UserForm_Initialize()
    Me.DesignMode = fmModeOn
End Sub

When done, just toggle the Propert value back :
Me.DesignMode = fmModeOff

As for controls embeeded on a worksheet, I guess it can also be done but it would be more code-involved.

 

[Yongle]

Private Sub UserForm_Initialize()
    Me.DesignMode = fmModeOn
End Sub

When done, just toggle the Propert value back :
Me.DesignMode = fmModeOff

@Jaafar Tribak

that is nice!

 

[Jaafar Tribak]

@Jaafar Tribak

that is nice!

Yes, that's a little known handy Property .