Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select one or more worksheets from a listbox
Hello,
I want to present a dialog box (I assume a ListBox) to the user. That dialog box should contain a listing of all the worksheets in the workbook. I'd like the user to select one or more worksheets. How would I construct this listbox? I have experince with Excel Macro's, but haven't used listboxes before. Many Thanks. -- Programmer on Budget |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select one or more worksheets from a listbox
see attached. Note the code in -Userform1 -code module and that the Listbox's *Multiselect *property is set to -1 - fmMultiSelectMulti- +-------------------------------------------------------------------+ |Filename: SelectWorksheetsInListBox.xls | |Download: http://www.thecodecage.com/forumz/attachment.php?attachmentid=227| +-------------------------------------------------------------------+ -- p45cal *p45cal* ------------------------------------------------------------------------ p45cal's Profile: http://www.thecodecage.com/forumz/member.php?userid=558 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=126442 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select one or more worksheets from a listbox
I did something pretty similar recently. In my case I allowed the user to
print multiple sheets to print. After adding the listbox to the userform, go to the listbox properties and set MultiSelect = 1 - fmMultiSelectMulti. Then add the following code to the userform module: Option Explicit Private Sub UserForm_Initialize() Dim SheetCount As Integer SheetCount = ThisWorkbook.Sheets.Count Dim i As Integer For i = 1 To SheetCount lstSheets.AddItem (Sheets(i).Name) Next i End Sub Private Sub btnPrint_Click() Application.ScreenUpdating = False Dim k As Integer With lstSheets For k = 0 To .ListCount - 1 If .Selected(k) Then Sheets(k + 1).PrintOut End If Next k End With Unload UserForm1 End Sub And in a general module I have a simple button click event to show the form: Sub CallPrint() UserForm1.Show End Sub Note: I haven't used default names for the listbox & print button in my example. But it's simple to just change the name property. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select one or more worksheets from a listbox
Hi Arjen,
Thanks for the reply. Where does lstSheets come in? I tried declaring it as an object and as a worksheet. Thanks for your help. Regards, Phil -- Programmer on Budget "arjen van..." wrote: I did something pretty similar recently. In my case I allowed the user to print multiple sheets to print. After adding the listbox to the userform, go to the listbox properties and set MultiSelect = 1 - fmMultiSelectMulti. Then add the following code to the userform module: Option Explicit Private Sub UserForm_Initialize() Dim SheetCount As Integer SheetCount = ThisWorkbook.Sheets.Count Dim i As Integer For i = 1 To SheetCount lstSheets.AddItem (Sheets(i).Name) Next i End Sub Private Sub btnPrint_Click() Application.ScreenUpdating = False Dim k As Integer With lstSheets For k = 0 To .ListCount - 1 If .Selected(k) Then Sheets(k + 1).PrintOut End If Next k End With Unload UserForm1 End Sub And in a general module I have a simple button click event to show the form: Sub CallPrint() UserForm1.Show End Sub Note: I haven't used default names for the listbox & print button in my example. But it's simple to just change the name property. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select one or more worksheets from a listbox
Hi Phil, lstSheets is actually the name I gave the ListBox, instead of the default name (which would be ListBox1). So there's no need to declare anything for it. Likewise btnPrint is just the CommandButton with a name change. Hopefully that clarifies things a bit. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select one or more worksheets from a listbox
Should have guessed that. Thanks.
-- Programmer on Budget "arjen van..." wrote: Hi Phil, lstSheets is actually the name I gave the ListBox, instead of the default name (which would be ListBox1). So there's no need to declare anything for it. Likewise btnPrint is just the CommandButton with a name change. Hopefully that clarifies things a bit. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot select items in listbox | Excel Programming | |||
Listbox-2 entires should select by Clicking Listbox-1 entires. How | Excel Programming | |||
Double click item in Listbox to select item and close Listbox | Excel Programming | |||
Listbox Multi Select | Excel Programming | |||
multi select listbox | Excel Programming |