Forms

[Steved]

Hello I was given the below

This is what I have Done.
I created a user form and created and put in it 2
comboboxes side by side and Label1.

This is my first attempt, my understanding is that I have
2 worksheets labeled TableWks and Table.
Table will have the data.
TableWks wil have the form

Ok is What I am supposed to do is put part of the code in
TableWks and Table, if so where do I split, please.

Thankyou.

Create a userform with 2 comboboxes on it (combobox1 and
combobox2).
Add a label (label1) (LABEL1 <-- Final character is one)

Then put this behind the userform:

Option Explicit
Dim FirstCityRng As Range
Dim SecondCityRng As Range
Dim MileageRng As Range

Private Sub ComboBox1_Change()
Dim myCell As Range

Me.Label1.Caption = "Select Two Cities"
Me.ComboBox2.Clear

If Me.ComboBox1.Value = "" Then
Exit Sub
End If

For Each myCell In FirstCityRng.Cells
If myCell.Value = Me.ComboBox1.Value Then
Me.ComboBox2.AddItem myCell.Offset(0, 1).Value
End If
Next myCell

End Sub
Private Sub ComboBox2_Change()

Dim res As Variant
Dim myFormula As String

Me.Label1.Caption = "Select Two Cities"

If Me.ComboBox1.Value = "" _
Or Me.ComboBox2.Value = "" Then
Exit Sub
End If

myFormula = "=Match(1, (" & FirstCityRng.Address
(external:=True) _
& "=""" & Me.ComboBox1.Value & """)" _
& "*(" & SecondCityRng.Address(external:=True) _
& "=""" & Me.ComboBox2.Value & """),0)"

res = Application.Evaluate(myFormula)

If IsError(res) Then
'shouldn't happen!
MsgBox "Design error!"
Else
Me.Label1.Caption = "Total Mileage = " & MileageRng(res)
End If

End Sub

Private Sub UserForm_Initialize()

Dim tableWks As Worksheet
Dim myCell As Range

Me.Label1.Caption = "Select Two Cities"

Set tableWks = Worksheets("table")

Me.ComboBox1.Style = fmStyleDropDownList
Me.ComboBox2.Style = fmStyleDropDownList

With tableWks
If .FilterMode Then
..ShowAllData
End If
Set FirstCityRng = .Range("A1", .Cells
(.Rows.Count, "A").End(xlUp))
Set SecondCityRng = FirstCityRng.Offset(0, 1)
Set MileageRng = FirstCityRng.Offset(0, 2)

FirstCityRng.AdvancedFilter Action:=xlFilterInPlace,
unique:=True

With FirstCityRng
For Each myCell In .Offset(1, 0).Resize(.Rows.Count - 1,
1) _
..Cells.SpecialCells(xlCellTypeVisible)
With Me.ComboBox1
..AddItem myCell.Value
End With
Next myCell
End With
..ShowAllData
End With

End Sub

Then create a worksheet Named Table.

Put your values in A2:C2000 (headers in row 1 are
required).

 

[Dave Peterson]

This code was for use with a userform. Kind of like those built in dialogs that
excel uses (like under Tools|options).

Put your data on a worksheet named Table, but then create a userform with those
two comboboxes and one label.

You might want to read this from MS:
http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm

It's a tutorial for getting started with userforms.