Excel User Forms

[CRUTHERFORD]

Hi,

So I am creating this userform that will pull data from other tabs depending on the selection made by the user.

1st issue: How do you create a drop down list in a user form?

2nd issue: My current user form contains the following...
PartnerName: <dropdownlist>
Initials: <textbox>

Brands: <4 Check boxes each with a different brand name>

status: <3 check boxes each with a different status>

Booked Year: <3 check boxes each with a different year>

How do i get it so once the command button 'OK' has been selected it will filter through tabs 1,2 and 3 and return all data that is relevant to the above selections in tab 4? Each selection in the user form has the same header in the other tabs so hopefully that will make it easier to filter?!

Cheers,
Cam

 

[dmt32]

Hi,
If you have a limited understanding of Userforms then you may find this site helpful:http://www.contextures.com/xlUserForm02.html

Site provides step by step instruction & you can download sample workbook.

Dave

 

[mrmmickle1]

One way to create a dropdown list is to use a combobox. So for example if you had a list on your spreadsheet:

Excel 2012
	A
1	SendOut Companies
2	Company A
3	Company B
4	Company C
ValidationLists

You would Go To Formulas > Name Manager > New

Then Name the Range (For this Ex. Let's Name it: Companies) and enter this formula:
=OFFSET(ValidationLists!$A$2, 0, 0, COUNTA(ValidationLists!$A:$A)-1,1)

The above formula will do several things:

1. It will not put the header in the list
2. It will make a Dynamic List (If you add a company like: Company D it will automatically be part of your named range. Therefore you do not constantly have to redefine the Named Range after adding new entries)

(This is possible because of the combination of the CountA and Offset Function)

So after you add the ComboBox to Your User Form . Select the ComboBox. Go To ComboBox Properties (Ctrl + R if not already visible) Then beside Rowsource enter Companies

This will create the dropdownlist.

Depending on how your data is set up moving Data around can be complex. It Sounds like you are creating some sort of reporting tool. Here is something that I worked on that may help a little bit. You will have to adjust it for your needs significantly though. Someone helped me with this code a while back:

[COLOR=#0000ff]Sub[/COLOR] GetPendingPlatesFromAllSheets()

    [COLOR=#0000ff]Dim [/COLOR]LR [COLOR=#0000ff]As Long[/COLOR]
    [COLOR=#0000ff]Dim [/COLOR]R A[COLOR=#0000ff]s Range[/COLOR]
  [COLOR=#0000ff]  Dim [/COLOR]nR A[COLOR=#0000ff]s Long[/COLOR]
   [COLOR=#0000ff] Dim[/COLOR] ws [COLOR=#0000ff]As [/COLOR]Worksheet
    [COLOR=#0000ff]Dim[/COLOR] Criteria1 [COLOR=#0000ff]As String[/COLOR]

  [COLOR=#0000ff] If [/COLOR]CheckBox1.Value =[COLOR=#0000ff] True Then  [/COLOR][COLOR=#008000]'Adjust this to multiple CheckBox Variables and Criteria[/COLOR]
  [COLOR=#ff0000] Criteria1[/COLOR] = "YourCriteria"
   [COLOR=#0000ff]End If[/COLOR]

[COLOR=#0000ff]    For Each[/COLOR] ws [COLOR=#0000ff]In[/COLOR] Worksheets
     [COLOR=#0000ff]  If[/COLOR] ws.Name = "Sheet1" [COLOR=#0000ff]Or[/COLOR] ws.Name = "Sheet2" [COLOR=#0000ff]Or[/COLOR] ws.Name = "Sheet3" [COLOR=#0000ff]Then[/COLOR]
        
         [COLOR=#0000ff]   With[/COLOR] Sheets(ws.Name)
                LR = .Range("A" & Rows.Count).End(xlUp).Row
                  [COLOR=#0000ff]Set[/COLOR] R = .Range("A1:F" & LR) [COLOR=#0000ff]'[/COLOR][COLOR=#008000]Your Filter Range[/COLOR]
                If LR = 1 Or Application.WorksheetFunction.CountIf(R, [COLOR=#ff0000][B]Criteria1[/B][/COLOR]) = 0 [COLOR=#0000ff]Then GoTo [/COLOR]Skip [COLOR=#008000]'This is in case there is only one row. Ex. Only the Header[/COLOR]
                R.AutoFilter Field:=6, Criteria1:= [B][COLOR=#ff0000]Criteria1[/COLOR][/B]
                R.Rows("2:" & LR).Copy
[COLOR=#0000ff]            End With[/COLOR]
            [COLOR=#0000ff]With [/COLOR]Sheets("[B][COLOR=#ff0000]YourDestinationSheet[/COLOR][/B]")
                nR = .Range("B" & Rows.Count).End(xlUp).Row + 1
                .Range("B" & nR).PasteSpecial Paste:=xlPasteValues
[COLOR=#0000ff]            End With
[/COLOR]   
       Sheets(ws.Name).Range("F1").AutoFilter
[COLOR=#0000ff]       End If[/COLOR]
Skip:
   [COLOR=#0000ff] Next[/COLOR] ws
[COLOR=#0000ff]End Sub[/COLOR]

 

[CRUTHERFORD]

Thanks to both of you!

I will take a look at that link dmt32.

mrmmickle1 - thanks for this.