Can I get a userform to show data from a range and give me the option to select which ones I want?

[tonywatsonhelp]

Hi Everyone,

Ok lets say i'm using userform1
Sheet "Control" range B2 to last row is my supplier list



What i would like is when we click a button the Userform pops up and has a list of all the suppliers names, and next to it a tickbox so I can select as many as I want.
then I click a button say send on Userform it takes my selection and hides all the other rows in that range. so I have just a list of the ones I want.

Please help if you can.
Thanks

Tony

 

[Fluff]

Are the suppliers in col B unique, or could they appear more than once?

 

[tonywatsonhelp]

Totally unique

 

[Fluff]

In that case create a userform with a multiselect listbox & commandbutton and use

Private Sub CommandButton1_Click()
   Dim i As Long, j As Long
   Dim Ary As Variant
   
   With Me.ListBox1
      ReDim Ary(0 To .ListCount)
      For i = 0 To .ListCount - 1
         If .Selected(i) Then Ary(j) = .List(i)
         j = j + 1
      Next i
   End With
   ReDim Preserve Ary(0 To j - 1)
   Sheets("Control").Range("B:B").AutoFilter 1, Ary, xlFilterValues
End Sub
Private Sub UserForm_Initialize()
   Me.ListBox1.List = Sheets("Control").Range("B2", Sheets("Control").Range("B" & Rows.count).End(xlUp)).Value
End Sub

 

[MickG]

Another Option
Userform has ListBox1 and Commandbutton1.

[COLOR=navy]Sub[/COLOR] MG21Aug51


Option Explicit

Private [COLOR=navy]Sub[/COLOR] CommandButton1_Click()
[COLOR=navy]Dim[/COLOR] n [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] Rng [COLOR=navy]As[/COLOR] Range
[COLOR=navy]With[/COLOR] ListBox1
[COLOR=navy]For[/COLOR] n = 0 To .ListCount - 1
    [COLOR=navy]If[/COLOR] Not .Selected(n) [COLOR=navy]Then[/COLOR]
        [COLOR=navy]If[/COLOR] Rng [COLOR=navy]Is[/COLOR] Nothing [COLOR=navy]Then[/COLOR]
            [COLOR=navy]Set[/COLOR] Rng = Range(.List(n, 1))
        [COLOR=navy]Else[/COLOR]
            [COLOR=navy]Set[/COLOR] Rng = Union(Rng, Range(.List(n, 1)))
        [COLOR=navy]End[/COLOR] If
 [COLOR=navy]End[/COLOR] If
 [COLOR=navy]Next[/COLOR] n
[COLOR=navy]End[/COLOR] With
Rng.EntireRow.Hidden = True
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]


Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
Columns("B:B").EntireRow.Hidden = False
[COLOR=navy]Set[/COLOR] Rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))
[COLOR=navy]With[/COLOR] ListBox1
  .MultiSelect = fmMultiSelectMulti
    [COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
        .AddItem Dn.Value
        .List(.ListCount - 1, 1) = Dn.Address
    [COLOR=navy]Next[/COLOR] Dn
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[tonywatsonhelp]

Hi Guys,
Thanks for your help but I have to make this idiot proof so I need check boxes they can select.
any ideas how we can change this so it does that?

Thanks
Tony

 

[Fluff]

How about

Private Sub UserForm_Initialize()
   With Me.ListBox1
      .MultiSelect = fmMultiSelectMulti
      .ListStyle = fmListStyleOption
      .List = Sheets("Control").Range("B2", Sheets("Control").Range("B" & Rows.count).End(xlUp)).Value
   End With
End Sub

 

[tonywatsonhelp]

Hi fluff,
This will Do prefect thanks for all you help
Tony

 

[Fluff]

Glad we could help & thanks for the feedback