Excel User form with 4 Combo Boxes

ca1358 · 01-16-2006, 12:15 PM

I am very new at this. I been working on this and reading up on the formulas
used. I need help.

I need to explain the columns of data .

Sheet 2 USER FORM WITH COMBO BOXES.

Sheet 1
Combo 1 is linked to Sheet2 A1:a8
Combo 2 is linked to Sheet2 B1:b3

Combo 3 is linked to Sheet 2 Range C1:F8 or Sheet 2 C9:f16 or Sheet2
C17:F24 is based on the following:

I need to have Column B1 connect to Range of C1:F8
Column B2 connect to range of C9:F16
Column B3 connect to range of C17:f24

Example: User picks Code5 in Combo 1
picks 02/06 in Combo 2 then

Combo 3 would show Range C13:f13

Row13 1/3 1/10 1/17 1/24

Combo 4 would show range G13

column G Row13 1/30

I need the combo boxes to be on a USER FORM on Sheet 2 that floats over the
data

Column Column
A B c d e
f g
Row1 CODE1 01/06 12/9 12/16 12/23 1/3 1/9
2 CODE2 02/06 12/6 12/13 12/20 12/28
1/4
3 CODE3 03/06 12/6 12/13 12/20 12/28
1/4
4 CODE4 N/A N/A 12/20 12/28
1/4
5 CODE5 11/30 12/7 12/14 12/21
12/28
6 CODE6 12/5 12/12 12/19 12/27
1/3
7 CODE7 11/30 12/7 12/14 12/21
12/28
8 CODE8 12/5 12/12 12/19 12/27
1/3

Row9 Code1 02/06 1/11 1/18 1/25 2/1 2/7
10 Code2 1/6 1/13 1/20 1/27 2/2
11 Code3 1/9 1/16 1/23 1/30 2/3
12 Code4 N/A N/A 1/20 1/27 2/2
13 Code5 1/3 1/10 1/17 1/24 1/30
14 Code6 1/6 1/13 1/20 1/27 2/2
15 Code7 1/3 1/10 1/17 1/24 1/30
16 Code8 1/6 1/13 1/20 1/27 2/2
and 03/06, 04/06 would repeat same, etc.

I have rearranged combo2 to read B1:B3, however each date needs to linked to
a range of dates to show in Combo3. Example B1 01/06 to Range C1:F8
B2 02/06 to
range C9:F16
B3 03/06 to
range C17:F24

I would greatly appreciated help!!!

--
ca1358

Tom Ogilvy · 01-16-2006, 01:00 PM

Remove links for Combo3 and Combo4 and set them with code.

Private Sub Combo2_Click()
With combo3
.RowSource = "Sheet1!C1:F8"
.RowSource = "Sheet1!C9:F16"
.RowSource = "Sheet1!C17:f24"
End With
' something similar for Combo4
End Sub

--
Regards,
Tom Ogilvy

"ca1358" <[email protected]> wrote in message
news:[email protected]...
> I am very new at this. I been working on this and reading up on the
formulas
> used. I need help.
>
> I need to explain the columns of data .
>
> Sheet 2 USER FORM WITH COMBO BOXES.
>
> Sheet 1
> Combo 1 is linked to Sheet2 A1:a8
> Combo 2 is linked to Sheet2 B1:b3
>
> Combo 3 is linked to Sheet 2 Range C1:F8 or Sheet 2 C9:f16 or Sheet2
> C17:F24 is based on the following:
>
> I need to have Column B1 connect to Range of C1:F8
> Column B2 connect to range of C9:F16
> Column B3 connect to range of C17:f24
>
>
> Example: User picks Code5 in Combo 1
> picks 02/06 in Combo 2 then
>
> Combo 3 would show Range C13:f13
>
> Row13 1/3 1/10 1/17 1/24
>
>
> Combo 4 would show range G13
>
> column G Row13 1/30
>
>
> I need the combo boxes to be on a USER FORM on Sheet 2 that floats over
the
> data
>
> Column Column
> A B c d e
> f g
> Row1 CODE1 01/06 12/9 12/16 12/23 1/3
1/9
> 2 CODE2 02/06 12/6 12/13 12/20 12/28
> 1/4
> 3 CODE3 03/06 12/6 12/13 12/20 12/28
> 1/4
> 4 CODE4 N/A N/A 12/20 12/28
> 1/4
> 5 CODE5 11/30 12/7 12/14 12/21
> 12/28
> 6 CODE6 12/5 12/12 12/19 12/27
> 1/3
> 7 CODE7 11/30 12/7 12/14 12/21
> 12/28
> 8 CODE8 12/5 12/12 12/19 12/27
> 1/3
>
> Row9 Code1 02/06 1/11 1/18 1/25 2/1 2/7
> 10 Code2 1/6 1/13 1/20 1/27
2/2
> 11 Code3 1/9 1/16 1/23 1/30
2/3
> 12 Code4 N/A N/A 1/20 1/27 2/2
> 13 Code5 1/3 1/10 1/17 1/24 1/30
> 14 Code6 1/6 1/13 1/20 1/27 2/2
> 15 Code7 1/3 1/10 1/17 1/24 1/30
> 16 Code8 1/6 1/13 1/20 1/27 2/2
> and 03/06, 04/06 would repeat same, etc.
>
>
> I have rearranged combo2 to read B1:B3, however each date needs to linked
to
> a range of dates to show in Combo3. Example B1 01/06 to Range C1:F8
> B2 02/06
to
> range C9:F16
> B3 03/06
to
> range C17:F24
>
>
> I would greatly appreciated help!!!
>
>
> --
> ca1358

ca1358 · 01-16-2006, 02:10 PM

Combo 3 is doing nothing

Like to ask a question, how would know if i pick B1 to use range C1:F8 and --
pick B2 to use
range c9:f16 and
pick B3 to use
range C17:f24.

Also do you know of a book that has advance techniques and skills in using
combo boxes on a Excel User Form.

Thank you for answering my question!!!

ca1358

"Tom Ogilvy" wrote:

> Remove links for Combo3 and Combo4 and set them with code.
>
> Private Sub Combo2_Click()
> With combo3
> .RowSource = "Sheet1!C1:F8"
> .RowSource = "Sheet1!C9:F16"
> .RowSource = "Sheet1!C17:f24"
> End With
> ' something similar for Combo4
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "ca1358" <[email protected]> wrote in message
> news:[email protected]...
> > I am very new at this. I been working on this and reading up on the
> formulas
> > used. I need help.
> >
> > I need to explain the columns of data .
> >
> > Sheet 2 USER FORM WITH COMBO BOXES.
> >
> > Sheet 1
> > Combo 1 is linked to Sheet2 A1:a8
> > Combo 2 is linked to Sheet2 B1:b3
> >
> > Combo 3 is linked to Sheet 2 Range C1:F8 or Sheet 2 C9:f16 or Sheet2
> > C17:F24 is based on the following:
> >
> > I need to have Column B1 connect to Range of C1:F8
> > Column B2 connect to range of C9:F16
> > Column B3 connect to range of C17:f24
> >
> >
> > Example: User picks Code5 in Combo 1
> > picks 02/06 in Combo 2 then
> >
> > Combo 3 would show Range C13:f13
> >
> > Row13 1/3 1/10 1/17 1/24
> >
> >
> > Combo 4 would show range G13
> >
> > column G Row13 1/30
> >
> >
> > I need the combo boxes to be on a USER FORM on Sheet 2 that floats over
> the
> > data
> >
> > Column Column
> > A B c d e
> > f g
> > Row1 CODE1 01/06 12/9 12/16 12/23 1/3
> 1/9
> > 2 CODE2 02/06 12/6 12/13 12/20 12/28
> > 1/4
> > 3 CODE3 03/06 12/6 12/13 12/20 12/28
> > 1/4
> > 4 CODE4 N/A N/A 12/20 12/28
> > 1/4
> > 5 CODE5 11/30 12/7 12/14 12/21
> > 12/28
> > 6 CODE6 12/5 12/12 12/19 12/27
> > 1/3
> > 7 CODE7 11/30 12/7 12/14 12/21
> > 12/28
> > 8 CODE8 12/5 12/12 12/19 12/27
> > 1/3
> >
> > Row9 Code1 02/06 1/11 1/18 1/25 2/1 2/7
> > 10 Code2 1/6 1/13 1/20 1/27
> 2/2
> > 11 Code3 1/9 1/16 1/23 1/30
> 2/3
> > 12 Code4 N/A N/A 1/20 1/27 2/2
> > 13 Code5 1/3 1/10 1/17 1/24 1/30
> > 14 Code6 1/6 1/13 1/20 1/27 2/2
> > 15 Code7 1/3 1/10 1/17 1/24 1/30
> > 16 Code8 1/6 1/13 1/20 1/27 2/2
> > and 03/06, 04/06 would repeat same, etc.
> >
> >
> > I have rearranged combo2 to read B1:B3, however each date needs to linked
> to
> > a range of dates to show in Combo3. Example B1 01/06 to Range C1:F8
> > B2 02/06
> to
> > range C9:F16
> > B3 03/06
> to
> > range C17:F24
> >
> >
> > I would greatly appreciated help!!!
> >
> >
> > --
> > ca1358
>
>
>

Tom Ogilvy · 01-16-2006, 02:20 PM

Since you said on Sheet2, then my response should have said Sheet2 as well
rather than Sheet1. My error.

--
Regards,
Tom Ogilvy

"Tom Ogilvy" <[email protected]> wrote in message
news:%[email protected]...
> Remove links for Combo3 and Combo4 and set them with code.
>
> Private Sub Combo2_Click()
> With combo3
> .RowSource = "Sheet1!C1:F8"
> .RowSource = "Sheet1!C9:F16"
> .RowSource = "Sheet1!C17:f24"
> End With
> ' something similar for Combo4
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
> "ca1358" <[email protected]> wrote in message
> news:[email protected]...
> > I am very new at this. I been working on this and reading up on the
> formulas
> > used. I need help.
> >
> > I need to explain the columns of data .
> >
> > Sheet 2 USER FORM WITH COMBO BOXES.
> >
> > Sheet 1
> > Combo 1 is linked to Sheet2 A1:a8
> > Combo 2 is linked to Sheet2 B1:b3
> >
> > Combo 3 is linked to Sheet 2 Range C1:F8 or Sheet 2 C9:f16 or Sheet2
> > C17:F24 is based on the following:
> >
> > I need to have Column B1 connect to Range of C1:F8
> > Column B2 connect to range of C9:F16
> > Column B3 connect to range of C17:f24
> >
> >
> > Example: User picks Code5 in Combo 1
> > picks 02/06 in Combo 2 then
> >
> > Combo 3 would show Range C13:f13
> >
> > Row13 1/3 1/10 1/17 1/24
> >
> >
> > Combo 4 would show range G13
> >
> > column G Row13 1/30
> >
> >
> > I need the combo boxes to be on a USER FORM on Sheet 2 that floats over
> the
> > data
> >
> > Column Column
> > A B c d e
> > f g
> > Row1 CODE1 01/06 12/9 12/16 12/23 1/3
> 1/9
> > 2 CODE2 02/06 12/6 12/13 12/20 12/28
> > 1/4
> > 3 CODE3 03/06 12/6 12/13 12/20 12/28
> > 1/4
> > 4 CODE4 N/A N/A 12/20 12/28
> > 1/4
> > 5 CODE5 11/30 12/7 12/14 12/21
> > 12/28
> > 6 CODE6 12/5 12/12 12/19 12/27
> > 1/3
> > 7 CODE7 11/30 12/7 12/14 12/21
> > 12/28
> > 8 CODE8 12/5 12/12 12/19 12/27
> > 1/3
> >
> > Row9 Code1 02/06 1/11 1/18 1/25 2/1 2/7
> > 10 Code2 1/6 1/13 1/20 1/27
> 2/2
> > 11 Code3 1/9 1/16 1/23 1/30
> 2/3
> > 12 Code4 N/A N/A 1/20 1/27 2/2
> > 13 Code5 1/3 1/10 1/17 1/24 1/30
> > 14 Code6 1/6 1/13 1/20 1/27 2/2
> > 15 Code7 1/3 1/10 1/17 1/24 1/30
> > 16 Code8 1/6 1/13 1/20 1/27 2/2
> > and 03/06, 04/06 would repeat same, etc.
> >
> >
> > I have rearranged combo2 to read B1:B3, however each date needs to
linked
> to
> > a range of dates to show in Combo3. Example B1 01/06 to Range
C1:F8
> > B2
02/06
> to
> > range C9:F16
> > B3
03/06
> to
> > range C17:F24
> >
> >
> > I would greatly appreciated help!!!
> >
> >
> > --
> > ca1358
>
>

Tom Ogilvy · 01-16-2006, 03:10 PM

Sorry, got distracted and didn't finish fleshing this out:

Private Sub Combo2_Click()
With combo3
Select Case Combo2.ListIndex
Case 0
.RowSource = "Sheet2!C1:F8"
Case 1
.RowSource = "Sheet2!C9:F16"
Case 2
.RowSource = "Sheet2!C17:f24"
End Select
End With
' something similar for Combo4
End Sub

--
Regards,
Tom Ogilvy

"ca1358" <[email protected]> wrote in message
news:[email protected]...
>
> Combo 3 is doing nothing
>
> Like to ask a question, how would know if i pick B1 to use range C1:F8
and --
> pick B2 to
use
> range c9:f16 and
> pick B3 to
use
> range C17:f24.
>
> Also do you know of a book that has advance techniques and skills in
using
> combo boxes on a Excel User Form.
>
> Thank you for answering my question!!!
>
> ca1358
>
>
> "Tom Ogilvy" wrote:
>
> > Remove links for Combo3 and Combo4 and set them with code.
> >
> > Private Sub Combo2_Click()
> > With combo3
> > .RowSource = "Sheet1!C1:F8"
> > .RowSource = "Sheet1!C9:F16"
> > .RowSource = "Sheet1!C17:f24"
> > End With
> > ' something similar for Combo4
> > End Sub
> >
> > --
> > Regards,
> > Tom Ogilvy
> >
> > "ca1358" <[email protected]> wrote in message
> > news:[email protected]...
> > > I am very new at this. I been working on this and reading up on the
> > formulas
> > > used. I need help.
> > >
> > > I need to explain the columns of data .
> > >
> > > Sheet 2 USER FORM WITH COMBO BOXES.
> > >
> > > Sheet 1
> > > Combo 1 is linked to Sheet2 A1:a8
> > > Combo 2 is linked to Sheet2 B1:b3
> > >
> > > Combo 3 is linked to Sheet 2 Range C1:F8 or Sheet 2 C9:f16 or Sheet2
> > > C17:F24 is based on the following:
> > >
> > > I need to have Column B1 connect to Range of C1:F8
> > > Column B2 connect to range of C9:F16
> > > Column B3 connect to range of C17:f24
> > >
> > >
> > > Example: User picks Code5 in Combo 1
> > > picks 02/06 in Combo 2 then
> > >
> > > Combo 3 would show Range C13:f13
> > >
> > > Row13 1/3 1/10 1/17 1/24
> > >
> > >
> > > Combo 4 would show range G13
> > >
> > > column G Row13 1/30
> > >
> > >
> > > I need the combo boxes to be on a USER FORM on Sheet 2 that floats
over
> > the
> > > data
> > >
> > > Column Column
> > > A B c d e
> > > f g
> > > Row1 CODE1 01/06 12/9 12/16 12/23 1/3
> > 1/9
> > > 2 CODE2 02/06 12/6 12/13 12/20 12/28
> > > 1/4
> > > 3 CODE3 03/06 12/6 12/13 12/20 12/28
> > > 1/4
> > > 4 CODE4 N/A N/A 12/20 12/28
> > > 1/4
> > > 5 CODE5 11/30 12/7 12/14
12/21
> > > 12/28
> > > 6 CODE6 12/5 12/12 12/19 12/27
> > > 1/3
> > > 7 CODE7 11/30 12/7 12/14 12/21
> > > 12/28
> > > 8 CODE8 12/5 12/12 12/19
12/27
> > > 1/3
> > >
> > > Row9 Code1 02/06 1/11 1/18 1/25 2/1 2/7
> > > 10 Code2 1/6 1/13 1/20 1/27
> > 2/2
> > > 11 Code3 1/9 1/16 1/23 1/30
> > 2/3
> > > 12 Code4 N/A N/A 1/20 1/27
2/2
> > > 13 Code5 1/3 1/10 1/17 1/24 1/30
> > > 14 Code6 1/6 1/13 1/20 1/27 2/2
> > > 15 Code7 1/3 1/10 1/17 1/24 1/30
> > > 16 Code8 1/6 1/13 1/20 1/27 2/2
> > > and 03/06, 04/06 would repeat same, etc.
> > >
> > >
> > > I have rearranged combo2 to read B1:B3, however each date needs to
linked
> > to
> > > a range of dates to show in Combo3. Example B1 01/06 to Range
C1:F8
> > > B2
02/06
> > to
> > > range C9:F16
> > > B3
03/06
> > to
> > > range C17:F24
> > >
> > >
> > > I would greatly appreciated help!!!
> > >
> > >
> > > --
> > > ca1358
> >
> >
> >