I am going crazy. I new to the whole user form thing. I plan on
creating userform that will use many of the options like a listbox,
combobox, radio boxes...etc. For now what I think I need is a list box
and a combo box that feed off autofilters, I think anyway that this
might be the best approach.

I have an excel list that goes from ba2 to bb6481. What I'd like to
see in the combo box is just 4 of the list found in column bb. This
list contains airport codes, the ones I want to see a jfk, ord, mia,
lax. They also appear in the list more than once. I would like to see
only one of each in the combo box.

So from a list box the user selects and ORIGINATING airport code from
column BA. The Combobox shows the DESTINATION airport codes (the for I
mentioned), then in corisponding text boxs I want a fee to show as it
would if I were to create an autofilter. The text box would display
dollar amounts found in column bi. Hope that makes sense.

This is just the beginning, once I have this in place I will be adding
other text boxes that will show calulated values based on what is
entered in the first two selections, ORG and DEST.

Tom Ogilvy wrote:
You have to loop through you source range and identify the rows you want
added to your listbox/combobox and add them using Additem. Since you say you
have repetitive entries, does that mean that the destination airfield appears
multiple times - if so, it seems like you would not show a fee until a
destination airfield were chosen and then possibly a list of flights (and
their fees).

In any event, there is no built in relationship that will respect items
returned from a filter.

You can look at this code from John Walkenbach's site that should get you
started.

http://j-walk.com/ss/excel/tips/tip47.htm

a link to all his developer tips:
http://j-walk.com/ss/excel/tips/index.htm

--
Regards,
Tom Ogilvy


" wrote:

I am going crazy. I new to the whole user form thing. I plan on
creating userform that will use many of the options like a listbox,
combobox, radio boxes...etc. For now what I think I need is a list box
and a combo box that feed off autofilters, I think anyway that this
might be the best approach.

I have an excel list that goes from ba2 to bb6481. What I'd like to
see in the combo box is just 4 of the list found in column bb. This
list contains airport codes, the ones I want to see a jfk, ord, mia,
lax. They also appear in the list more than once. I would like to see
only one of each in the combo box.

So from a list box the user selects and ORIGINATING airport code from
column BA. The Combobox shows the DESTINATION airport codes (the for I
mentioned), then in corisponding text boxs I want a fee to show as it
would if I were to create an autofilter. The text box would display
dollar amounts found in column bi. Hope that makes sense.

This is just the beginning, once I have this in place I will be adding
other text boxes that will show calulated values based on what is
entered in the first two selections, ORG and DEST.

-----------------------------

Thanks Tom for your quick response. Although some of the code like
it'll fit to what I want. Are you saying tho that a Userform can
'mimec' an auto filter? Below is an example of my data, I hope this
will help in painting a clear picture.

A B C
Org Dest Cost
ALT SAN $30.00
MEM SEA $15.00
SFO JFK $25.00
ALT BUF $20.00
ALT MIA $30.00

Say in my original question I want to see from column B only JFK and
MIA. Using a listbox for column A the user selects ALT, then in the
combobox JFK - $25.00 and MIA - $30.00 is listed. Similuar to what the
autofilter would display. Also, does the 'rowsource' play into this at
all? The actual data is in BA2:BA6481 and BB2:BB6481 and BI2:BI6481,
there is a fair amount of data. Is that clear? Or did you already
get all that from my first message...?

Thanks again, sorry, I wish I was a pro at coding in VBA....!!

No, rowsource has no role to play

You would use John's code to fill the listbox.

Then use something like this for the combobox and Textbox

Private Sub Listbox1_Click()
Combobox1.Clear
Textbox1.Value = ""
Combobox1.Value = ""
Combobox1.ListCount = 2
Combobox1.ColumnWidths = "-1;0"
for each cell in Range("BB2:BB6481")
if cell.offset(0,-1).Value = Listbox1.Value then
Combobox1.AddItem cell.Value
combobox1.List(Combobox1.ListIndex,1).Value = cell.row
end if
Next
End if

Private Sub Combobox1_Click()
With Combobox1
rw = .List(.ListIndex,1)
End with
Textbox1.Value = Worksheets("Data").Cells(rw,"BI").Text
End sub

--
Regards,
Tom Ogilvy




wrote in message
oups.com...

Tom Ogilvy wrote:
You have to loop through you source range and identify the rows you want
added to your listbox/combobox and add them using Additem. Since you say
you
have repetitive entries, does that mean that the destination airfield
appears
multiple times - if so, it seems like you would not show a fee until a
destination airfield were chosen and then possibly a list of flights (and
their fees).

In any event, there is no built in relationship that will respect items
returned from a filter.

You can look at this code from John Walkenbach's site that should get you
started.

http://j-walk.com/ss/excel/tips/tip47.htm

a link to all his developer tips:
http://j-walk.com/ss/excel/tips/index.htm

--
Regards,
Tom Ogilvy


" wrote:

I am going crazy. I new to the whole user form thing. I plan on
creating userform that will use many of the options like a listbox,
combobox, radio boxes...etc. For now what I think I need is a list box
and a combo box that feed off autofilters, I think anyway that this
might be the best approach.

I have an excel list that goes from ba2 to bb6481. What I'd like to
see in the combo box is just 4 of the list found in column bb. This
list contains airport codes, the ones I want to see a jfk, ord, mia,
lax. They also appear in the list more than once. I would like to see
only one of each in the combo box.

So from a list box the user selects and ORIGINATING airport code from
column BA. The Combobox shows the DESTINATION airport codes (the for I
mentioned), then in corisponding text boxs I want a fee to show as it
would if I were to create an autofilter. The text box would display
dollar amounts found in column bi. Hope that makes sense.

This is just the beginning, once I have this in place I will be adding
other text boxes that will show calulated values based on what is
entered in the first two selections, ORG and DEST.

-----------------------------

Thanks Tom for your quick response. Although some of the code like
it'll fit to what I want. Are you saying tho that a Userform can
'mimec' an auto filter? Below is an example of my data, I hope this
will help in painting a clear picture.

A B C
Org Dest Cost
ALT SAN $30.00
MEM SEA $15.00
SFO JFK $25.00
ALT BUF $20.00
ALT MIA $30.00

Say in my original question I want to see from column B only JFK and
MIA. Using a listbox for column A the user selects ALT, then in the
combobox JFK - $25.00 and MIA - $30.00 is listed. Similuar to what the
autofilter would display. Also, does the 'rowsource' play into this at
all? The actual data is in BA2:BA6481 and BB2:BB6481 and BI2:BI6481,
there is a fair amount of data. Is that clear? Or did you already
get all that from my first message...?

Thanks again, sorry, I wish I was a pro at coding in VBA....!!

Tom:

Thanks again. I've been working with the code you sent. It did not
work at first, I was having trouble, it seemed with rw =
..List(.ListIndex,1). I had a combo box that had the rawsourse as
data!ba2:ba6481. Then after testing it seemed that if I selected the
very first cell in BA, BA2, I would get an error. If I selected the
value (via the dropdown from the Combobox) I would get a return from BI
from BI1, the header, if I selected the next value, BA3, then I got a
dollar value from BI but from BI2. I tried different numbers in the
listindex porotion, 0, -1 and so on...but nothing seem to work. When I
added a +2 to the end, that worked. So it looks like: rw =
..List(.ListIndex,- 1) +2. I'm guessing this is not the best syntax.

So, that may or maynot bring me to the next problem. So now with this
code in Sub Combobox1_click:

Dim rw As Single
Dim j As Integer

With ComboBox1
rw = .List(.ListIndex, -1) + 2
j = .List(.ListIndex, -1) + 2
End With
TextBox1.Value = Worksheets("Data").Cells(rw, "BI").Text
TextBox2.Value = Worksheets("Data").Cells(j, "BB").Value

I get in the Combobox on the form, BOS (for example), in the Textbox1:
SDF and in textbox2 I get $30.00. These are all in row 2. If i select
the value from BA100, for example, I get the value from BB100 and
BI100. Which is a good start. What I would like from column BB to
only return 4 possible values, JFK, ORD, LAX and MIA. I'm not sure if
a listbox is a way to go or should I add 4 textboxes, one for each. In
any case, how do I get these 4 items to be displayed and the must
coorispond with the value from BA. and BI. Like they might if I did a
straight autofilter. I notice to that with Autofilter there are only 2
available options to sort by via customs...is that a restriction in VBA
as well....I'm guessing not, but how do I get around that.

Thanks again, I've been searching and this sight and see you have been
helpfull to many folks...

Does that make sense?




Tom Ogilvy wrote:
No, rowsource has no role to play

You would use John's code to fill the listbox.

Then use something like this for the combobox and Textbox

Private Sub Listbox1_Click()
Combobox1.Clear
Textbox1.Value = ""
Combobox1.Value = ""
Combobox1.ListCount = 2
Combobox1.ColumnWidths = "-1;0"
for each cell in Range("BB2:BB6481")
if cell.offset(0,-1).Value = Listbox1.Value then
Combobox1.AddItem cell.Value
combobox1.List(Combobox1.ListIndex,1).Value = cell.row
end if
Next
End if

Private Sub Combobox1_Click()
With Combobox1
rw = .List(.ListIndex,1)
End with
Textbox1.Value = Worksheets("Data").Cells(rw,"BI").Text
End sub

--
Regards,
Tom Ogilvy




wrote in message
oups.com...

Tom Ogilvy wrote:
You have to loop through you source range and identify the rows you want
added to your listbox/combobox and add them using Additem. Since you say
you
have repetitive entries, does that mean that the destination airfield
appears
multiple times - if so, it seems like you would not show a fee until a
destination airfield were chosen and then possibly a list of flights (and
their fees).

In any event, there is no built in relationship that will respect items
returned from a filter.

You can look at this code from John Walkenbach's site that should get you
started.

http://j-walk.com/ss/excel/tips/tip47.htm

a link to all his developer tips:
http://j-walk.com/ss/excel/tips/index.htm

--
Regards,
Tom Ogilvy


" wrote:

I am going crazy. I new to the whole user form thing. I plan on
creating userform that will use many of the options like a listbox,
combobox, radio boxes...etc. For now what I think I need is a list box
and a combo box that feed off autofilters, I think anyway that this
might be the best approach.

I have an excel list that goes from ba2 to bb6481. What I'd like to
see in the combo box is just 4 of the list found in column bb. This
list contains airport codes, the ones I want to see a jfk, ord, mia,
lax. They also appear in the list more than once. I would like to see
only one of each in the combo box.

So from a list box the user selects and ORIGINATING airport code from
column BA. The Combobox shows the DESTINATION airport codes (the for I
mentioned), then in corisponding text boxs I want a fee to show as it
would if I were to create an autofilter. The text box would display
dollar amounts found in column bi. Hope that makes sense.

This is just the beginning, once I have this in place I will be adding
other text boxes that will show calulated values based on what is
entered in the first two selections, ORG and DEST.

-----------------------------

Thanks Tom for your quick response. Although some of the code like
it'll fit to what I want. Are you saying tho that a Userform can
'mimec' an auto filter? Below is an example of my data, I hope this
will help in painting a clear picture.

A B C
Org Dest Cost
ALT SAN $30.00
MEM SEA $15.00
SFO JFK $25.00
ALT BUF $20.00
ALT MIA $30.00

Say in my original question I want to see from column B only JFK and
MIA. Using a listbox for column A the user selects ALT, then in the
combobox JFK - $25.00 and MIA - $30.00 is listed. Similuar to what the
autofilter would display. Also, does the 'rowsource' play into this at
all? The actual data is in BA2:BA6481 and BB2:BB6481 and BI2:BI6481,
there is a fair amount of data. Is that clear? Or did you already
get all that from my first message...?

Thanks again, sorry, I wish I was a pro at coding in VBA....!!