selecting and naming multiple workbooks from listbox

helpexcel

Well-known Member
Joined
Oct 21, 2009
Messages
656
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
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
What are you trying to do ? :confused:

They already have a name. Are you wanting to save them with a new name ?
 
Upvote 0
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.
 
Upvote 0
Exactly how should the sheets be renamed ?
- is the current date appended to each sheet name ?
- should renaming happen when sheets are selected ?
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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!"



 
Upvote 0
It doesn't look like RenameWorkbooks is being called

Are BOTH procedures in the UserForm code window ?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,564
Messages
6,114,334
Members
448,567
Latest member
Kuldeep90

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top