Multi Select List Box Userform

[alyssa75]

Hi guys - I'd like to create a userform to do the following:

I have values stored in a named range..call it..."myNamedRange". I want to create a macro to show a userform where the user selects as many of those as he/she wants. I then want that when they click OK, the macro looks for the value two cells to the right to find the tab name and print that sheet. Clicking Cancel exits the form.

I am familiar with For Next looping, If statements and the Offset statement to include the list. I am now at all familiar with userforms and am therefore not having any lucky weaving this together.

Any help would be greatly appreciated.


Thanks!

 

[bertie]

Hi Alyssa,


Open Excel
Select the values for your named range and click Insert => Name => Define
Assign the name myNamedRange

Press Alt + F11
Click Insert => UserForm
From the Toolbox select a ListBox control and place it on the form
Right click the ListBox control and select Properties
Set MultiSelect to 1 fmMultiSelectMulti

Place a command button on the form, right click the command button, and name it cmdCancel
Place a command button on the form and name it cmdOk

Right click the form and select View Code
Insert the code below.
Edit the sheet name where highlighted.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdCancel_Click()
   Unload Me
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdOk_Click()
   [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Integer[/color]        [color=green]'loop index[/color]
   [color=darkblue]Dim[/color] sName [color=darkblue]As[/color] [color=darkblue]String[/color]     'sheet name
   [color=darkblue]Dim[/color] iRow [color=darkblue]As[/color] [color=darkblue]Long[/color]        [color=green]'row containing sheet name[/color]
   
   [color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] ListBox1.ListCount - 1
      [color=darkblue]If[/color] ListBox1.Selected(i) = [color=darkblue]True[/color] [color=darkblue]Then[/color]
         
         [color=green]'listBoxes start at base zero, add 1 to the row count[/color]
         [color=green]'add 1 for each blank row before the list starts[/color]
         iRow = ListBox1.ListIndex + i + [COLOR="Red"]1[/COLOR]
         
         [color=green]'you may need to adjust the column value, currently set at 3[/color]
         sName = Sheets("[COLOR="Red"]Sheet1[/COLOR]").Cells(iRow, 3).Value
         Debug.Print sName
         [color=green]'Sheets(sName).PrintOut[/color]
      [color=darkblue]End[/color] [color=darkblue]If[/color]
   [color=darkblue]Next[/color] i
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] UserForm_Initialize()
   ListBox1.List = Range("myNamedRange").Value
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

I have commented out the print statement. For testing I have used Debug.Print to output the sheet name to the Immediate Window, click View=>Immediate Window.

 

[alyssa75]

Thanks so much!! This is wonderful!! One followup - and I am so angry at myself that I can't figure this out - but how do I get this to run. That is, I want to "Assign" a macro to a button in my workbook that when clicked...runs the macro that loads this Userform.


Also - and this is less important but would be so nice - can I add a user input field to the userform that defaults to 1 but can be overwritten by the user to capture # of copies they want to print?

Thanks so much again!!

 

[bertie]

To set up a command button;
Select the worksheet you want the command button on.
Select View=>Toolbars=>Control Toolbox.
Select a command button and place it on the worksheet.
Double click the command button and insert this code.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] CommandButton1_Click()
   Load UserForm1
   UserForm1.Show
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

On the Control Toolbox toolbar click the "Exit design mode" icon, top left.
Click on the button to test.

====================
For printing multiple copies try making these amendments to the OK button on the form.

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] cmdOk_Click()
   [color=darkblue]Dim[/color] i [color=darkblue]As[/color] [color=darkblue]Integer[/color]        [color=green]'loop index[/color]
   [COLOR="Red"][color=darkblue]Dim[/color] j [color=darkblue]As[/color] [color=darkblue]Integer[/color]        'loop index[/COLOR]
   [color=darkblue]Dim[/color] sName [color=darkblue]As[/color] [color=darkblue]String[/color]     [color=green]'sheet name[/color]
   [color=darkblue]Dim[/color] iRow [color=darkblue]As[/color] [color=darkblue]Long[/color]        'row containing sheet name
[COLOR="Red"]   [color=darkblue]Dim[/color] numCopies [color=darkblue]As[/color] [color=darkblue]Integer[/color][/COLOR]
   
   [color=darkblue]For[/color] i = 0 [color=darkblue]To[/color] ListBox1.ListCount - 1
      [color=darkblue]If[/color] ListBox1.Selected(i) = [color=darkblue]True[/color] [color=darkblue]Then[/color]
         
         [color=green]'listBoxes start at base zero, add 1 to the row count[/color]
         [color=green]'add 1 for each blank row in your worksheet[/color]
         iRow = ListBox1.ListIndex + i + 1
         
         [color=green]'you may need to adjust the column value, currently set at 3[/color]
         sName = Sheets("Sheet1").Cells(iRow, 3).Value
         
    [COLOR="red"]     numCopies = Application.InputBox("How many copies?", Type:=1)
         [color=darkblue]For[/color] j = 1 [color=darkblue]To[/color] numCopies
            MsgBox "Print: " & j
            [color=green]'Sheets(sName).PrintOut[/color]
         [color=darkblue]Next[/color] j[/COLOR]

      [color=darkblue]End[/color] [color=darkblue]If[/color]
   [color=darkblue]Next[/color] i
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

NB I have commented out the Print statement for testing.

 

[sujon]

helpful to me