Excel VBA Implementing drag and drop functionality on buttons on a user form to change the order of the data
In a simple VBA App I am trying to implement Drag and Drop functionality on a a user form on which I have some data which comes from a Excel Workbook displayed on some buttons.
I would like to be able to change the order of the data in the underlying Excel Worksheet by dragging a button on this form and dropping on it on another button. I load data on the buttons on this form such that the caption of a button has the value of a string in a row in the Excel sheet and the ControlTipText of the button has the row number of the excel sheet from which the data is coming. In this way when I click on any button I have access to two properties of my data - the value of the string in a row and as well as the row number.
For example the image attached shows a string "brandished" displayed on one of the buttons on my user form and you can see it is coming from the row number 12 in the Excel sheet. I have row number 12 linked to the ControlTipText of each button.
The functionality I would like to implement will be able to do the following.
If I put my cursor on the button showing "brandished", and drop it on the button with caption "break", then the string "brandished" should move to the row above the row containing string "break", and all the data including "break" will move one row down ward. So after I drop "brandished" on "break", the word "brandished" will moved to the 7th row in the Excel sheet and "break" will moved to the 8th row and all the other data will below "break" will also move down in the same way. Hope this is clear.
I include the code I am using to display the data from the Excel Worksheets on the buttons on the form.
When this user form activates it loads data from an Excel Workbook named data. The data comes from a Collection which I have named Statements in this App. This collection is based on a simple class named Element in my application which only has 2 properties, ID and Caption.
The code I used to load data on the user form buttons
Public Statements As New Collection
Sub GetData(startsWith As String)
Set Statements = Nothing
Dim Caption As String
ThisWorkbook.Sheets("data").Activate
Dim stat As Element
Dim i As Long
i = 1
For i = 1 To 20
If InStr(1, Cells(i, 3).Value, startsWith) = 1 Then
Set stat = New Element
stat.Id = i
stat.Caption = Trim(Cells(i, 2).Value)
Statements.Add stat
Set book = Nothing
End If
Next i
Call LoadDataOnButton
End Sub
Sub LoadDataOnButton()
Dim stat As Element
Dim row As Integer
Dim contName As String
Dim idControl As String
row = 1
For Each stat In Statements
contName = "cmdD"
contName = contName & row
Me.Controls(contName).Caption = stat.Caption
Me.Controls(contName).ControlTipText = stat.Id
row = row + 1
If row > 9 Then
Exit Sub
End If
Next stat
Set Statements = Nothing
End Sub
Private Sub UserForm_Initialize()
Call GetData("br")
End Sub
The code for my class Element
Option Explicit
Public Id As Long
Public Caption As StringSimple Drag and Drop.xlsm
ASKER
Thank you experts for your comments.
My original problem is more complex than that what I have shown in my example, in my App I am displaying data on buttons based on some logic and then changing the order of the data displayed on the buttons. The data base (the Excel sheets that provides this data has more than a 100, 000 records. I need to change the order of the records so that the record for example in row number 10000 can be moved to row number 2 and the the record on row 2 and all the records below this move downwards by one row. I need to do this again and again for so many different records in different rows in a time efficient way. I do not know how I can do that easily directly in Excel when there are several hundred thousand records. Hope this makes sense.
29251775.xlsm
ASKER
Thank you for your comment Martin. The user form does not load all data. The user form only loads the first 18 records that match a certain criteria. For example in the image that I have provided I am loading the top 8 records where the that start with letters “br”. And in the sample data I have provided I would like to drag the string ”brandhished“ which is displayed on button 4 to to a different position. For example if I start dragging from the 4th button and drop it on the 2nd button, I would like the data in the underlying sheet to change so that a new row is created above row 7 and its value is set to ”brandished” and all the rows below will get pushed down.
So if I was doing it directly on the Excel sheet what I would be doing is cutting the row 12 and pasting it just above 7. I want to be able to do it through the user form because my actually worksheet contains more than a 10000 thousand records.
I think it should be possible through drag and drop because the information I need for this drag drop is stored on the buttons - as the value of the row is stored on the caption of the button and the value of the row number is stored on the ControlTipText.
After I have dropped the “brandished“ on “break” and after the data in the excel sheet has been reordered I would like to display it using the same method that I have used to load it at the start.
ASKER
Thank you Martin, you code is working and doing exactly what I want to do it, but the difficulty is my data base is very large - with a few hundred thousand records, the records I need to move around are not known to me in advance. I see their order only when they have been displayed on a user form using certain criteria. If you trust me, I have been doing this manually for more than a few weeks, finding a record in the data the determining where it should go in the Excel then cutting and pasting from it from one location to another and that is taking a huge amount of time. I need to do a lot of that, that is the reason I thought if I could do it through drag and drop that would be the most efficient way of doing what I need to do.
ASKER
Many thanks. Greatly appreciated!
If you expand the “Full Biography" section of my profile you’ll find links to some articles I’ve written that may interest you.
Marty - Microsoft MVP 2009 to 2017
Experts Exchange Most Valuable Expert (MVE) 2015 and 2017
Experts Exchange Distinguished Expert in Excel 2018 and 2021
Experts Exchange Top Expert Visual Basic Classic 2012 to 2021
Experts Exchange Top Expert VBA 2018 to 2021
I've never seen drag & drop on an Excel UserForm and I don't think it is possible.
Attach an example of your workbook instead of a picture.