Asp Forum - Autofilters and Userforms - microsoft.public.excel.programming

jeff.white

12/15/2006 4:49:00 PM

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 are: 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.

3 Answers

jeff.white

12/15/2006 9:17:00 PM

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...
>
> a link to all his developer tips:
> http://j-walk.com/ss/excel/tips...
>
> --
> Regards,
> Tom Ogilvy
>
>
> "jeff.white@sekoworldwide.com" 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 are: 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 Ogilvy

12/15/2006 11:13:00 PM

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

<jeff.white@sekoworldwide.com> wrote in message
news:1166217397.889789.165590@l12g2000cwl.googlegroups.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...
>>
>> a link to all his developer tips:
>> http://j-walk.com/ss/excel/tips...
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>>
>> "jeff.white@sekoworldwide.com" 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 are: 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....!!
>

jeff.white

12/19/2006 1:32:00 PM

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
>
>
>
>
> <jeff.white@sekoworldwide.com> wrote in message
> news:1166217397.889789.165590@l12g2000cwl.googlegroups.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...
> >>
> >> a link to all his developer tips:
> >> http://j-walk.com/ss/excel/tips...
> >>
> >> --
> >> Regards,
> >> Tom Ogilvy
> >>
> >>
> >> "jeff.white@sekoworldwide.com" 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 are: 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....!!
> >

microsoft.public.excel.programming

Autofilters and Userforms

jeff.white

jeff.white

Tom Ogilvy

jeff.white

x Login to ForumsZone