selecting and naming multiple workbooks from listbox

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
515
Hi - i'm using the code below to show open workbooks which the user can then select one. How do I update the code to allow the user to select multiple workbooks and how do I name them?

Code in a Module
Code:
Dim WB As Workbook
 For Each WB In Workbooks
     UserForm5.ListBox1.AddItem WB.Name
   Next WB
UserForm1.Show
Code in Form
Code:
Private Sub ListBox1_AfterUpdate()


listchoice = ListBox1.Text


End Sub
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,259
What are you trying to do ? :confused:

They already have a name. Are you wanting to save them with a new name ?
 

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
515
I want to pick 2 open worksheets and then reference them in my code. When I run updates on these sheets the name changes because the date is added, which is why I use the listbox.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,259
Exactly how should the sheets be renamed ?
- is the current date appended to each sheet name ?
- should renaming happen when sheets are selected ?
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,259
You have confused sheets and workbooks in post#3, so I am disregarding that post
I still do not undestand precisely how or when the workbooks are being renamed

Hopefully this will get you started and we can take it from there.
The code goes in userform module

Amend the multi-select property of the ListBox to fmMultiSelectMulti to allow more than one item to be selected

The code below provides you with the strings required to identify the names of the workbooks, but I am not sure where you want to go from this point


Code:
[COLOR=#006400]This limits selection to 2 names[/COLOR]
Private Sub ListBox1_Change()
    Dim x As Long, c As Long
    With ListBox1
        For x = 0 To .ListCount - 1
            If .Selected(x) = True Then
                c = c + 1
                If c = 2 Then Call RenameWorkbooks
            End If
        Next
    End With
End Sub

[COLOR=#006400]This returns N1 and N2 = selected items[/COLOR]
Private Sub RenameWorkbooks()
    Dim N1 As String, N2 As String, x As Long
    With ListBox1
        For x = 0 To .ListCount - 1
            If .Selected(x) = True Then
                If N2 <> "" Then Exit For
                If N1 = "" Then N1 = .List(x) Else N2 = .List(x)
                .Selected(x) = False
            End If
        Next
    End With
    MsgBox N1 & vbCr & N2
End Sub
What needs to happen next ?
 
Last edited:

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
515
Thanks! It doesn't look like RenameWorkbooks is being called. Once the workbooks have been selected, I want to copy A5:10 from the 1st one selected to A5 on workbook3 and A5:10 from the 2nd one selected to A11 on workbook3.
 

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
515
I think I want to say N1=listbox1.value and then use that to define ws1 as workbooks(N1).sheet1 and then N2=listbox1.value as ws2=workbooks(N2).sheet1
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
4,259
It doesn't look like RenameWorkbooks is being called
The code is doing nothing other than looping to see if anything is selected - so I would expect it to call the other macro if anything is selected

Prove it by inserting a message box as the first line of RenameWorkbooks
Code:
MsgBox "Hello - you called!"


 

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
515
I got it working, needed to update ListBox1 to ListBox2. I basically want to be able to select 1,2, or 3 or how ever many sheets are sleeted from the list box and then copy range A11 & LastRow on those sheets to A11 on ws3. I just don't know how to identify the sheets selected in the ListBox.
 

Forum statistics

Threads
1,078,154
Messages
5,338,537
Members
399,241
Latest member
mattyoxford

Some videos you may like

This Week's Hot Topics

Top