selecting and naming multiple workbooks from listbox

[helpexcel]

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

Dim WB As Workbook
 For Each WB In Workbooks
     UserForm5.ListBox1.AddItem WB.Name
   Next WB
UserForm1.Show

Code in Form

Private Sub ListBox1_AfterUpdate()


listchoice = ListBox1.Text


End Sub

 

[Yongle]

What are you trying to do ?

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

 

[helpexcel]

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]

Exactly how should the sheets be renamed ?
- is the current date appended to each sheet name ?
- should renaming happen when sheets are selected ?

 

[Yongle]

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

[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 ?

 

[helpexcel]

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]

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]

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

MsgBox "Hello - you called!"

 

[Yongle]

It doesn't look like RenameWorkbooks is being called

Are BOTH procedures in the UserForm code window ?

 

[helpexcel]

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.