Create A New Book From Selected Sheets

Create A New WorkBook From Selected Sheets On Userform -  Listbox Drag And Drop Event


        In this study, we have userforms containing 3 listboxes..The sheets that make up the workbook are listed in the first listbox(Listbox1). When clicked the sheet names in the listbox , content of the sheets are displayed in another listbox (Listbox3).

       To create a new workbook from selected sheets, ago we drag sheet names from listbox(Listbox1) to other listbox control(Listbox2) and we drop (Listbox drag and drop) . Later when we press the button bottom the userform, a new workbook is created by VBA codes containing the sheets in Listbox2.

listbox drag drop

VBA codes for listbox drag and drop :
Private Sub ListBox1_MouseMove(ByVal Button As _
     Integer, ByVal Shift As Integer, ByVal X As _
     Single, ByVal Y As Single)
    Dim MyDataObject As DataObject
    If Button = 1 Then
        On Error Resume Next
        Set MyDataObject = New DataObject
        Dim Effect As Integer
        MyDataObject.SetText ListBox1.Value
        Effect = MyDataObject.StartDrag
    End If
End Sub

Private Sub ListBox3_BeforeDragOver(ByVal Cancel As MSForms.ReturnBoolean, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal DragState As MSForms.fmDragState, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
Cancel = True
Effect = 1
End Sub

Private Sub ListBox3_BeforeDropOrPaste(ByVal Cancel As MSForms.ReturnBoolean, ByVal Action As MSForms.fmAction, ByVal Data As MSForms.DataObject, ByVal X As Single, ByVal Y As Single, ByVal Effect As MSForms.ReturnEffect, ByVal Shift As Integer)
Cancel = True
Effect = 1
ListBox3.AddItem Data.GetText
End Sub

As seen in the above codes, the listbox drag and drop event occurs between Listbox1 and Listbox3.

Also ,we created a listbox popup menu that working on the Listbox3  .There are two menu items in the pop-up menu that opened when right-clicking on an item in Listbox2 :
Remove From List
✅ Remove All


Codes in Userform_Initialize procedure for creating a listbox popup menu:
Set PopupMenum = New evnClass
Application.CommandBars(evnPopupMenu).Delete
With CommandBars.add(evnPopupMenu, Position:=msoBarPopup)
With .Controls.add(Type:=msoControlButton)
.Caption = "Remove From List"
.BeginGroup = True
.FaceId = 2087
End With

With .Controls.add(Type:=msoControlButton)
.Caption = "Remove All"
.BeginGroup = False
.FaceId = 1019
End With

Set PopupMenum.ListedenKaldir = .Controls(1)
Set PopupMenum.TumunuTemizle = .Controls(2)
Set PopupMenum.LBox = Me.ListBox3
End With


No comments:

Post a Comment