I have seen a spreadsheet that has a drop-down box (combo box). When being
clicked on, it shows data in multiple columns. When selecting a value from
any of the default values, their associated information is automatically
placed into adjacent cells.

Pls see http://www.microsheet.com/Downloads.htm and download the Time &
Expense Manager example. On the INVOICE tab, you'll see combos under Task
Description. I'm trying to built such drop-down menu.

How can I create such drop-down menu from which I select a value and a few
other columns are automatically filled?

Tom

I don't open attachments or download files.

But I put a list of stuff on Sheet2 in A1:C10.

I added a combobox from the control toolbar to sheet1.

While still in design mode, I rightclicked on the combobox and selected
properties.

Look for ColumnCount. I used 3.
(you may want to play around with the ColumnWidths property later)

Look for LinkedCell. I typed in: A1 (on sheet1)

Look for ListFillRange. I typed in: sheet2!a1:c10

Then in B1, I used this formula:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,2,FALSE))
In C1:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,3,FALSE))

Notice the =vlookup() range is the same as the ListFillRange.

And one more property to change:
look for: Style. I used fmStyleDropDownList
That means I can't type something in that isn't on the list.

After you get the basics running, take a closer look at all those properties.
(Save your workbook before you break anything, though!)

Tom wrote:

I have seen a spreadsheet that has a drop-down box (combo box). When being
clicked on, it shows data in multiple columns. When selecting a value from
any of the default values, their associated information is automatically
placed into adjacent cells.

Pls see http://www.microsheet.com/Downloads.htm and download the Time &
Expense Manager example. On the INVOICE tab, you'll see combos under Task
Description. I'm trying to built such drop-down menu.

How can I create such drop-down menu from which I select a value and a few
other columns are automatically filled?

Tom

--

Dave Peterson

Dave:

Thanks for your reply... this seems very promising... unfortunately, I
believe I'm missing something here.

I believe I followed all of your instructions. I found all the properties
and entered your suggested data/values.

Now, in Sheet1, the combo box (which I added from the Control Toolbox is
"free-floating" on Sheet1. At this time when I click on the combo, I still
the arrowed-crossed and 6 "circles". Although I closed the Control Toolbox
window, it appears that I'm still in some design mode.

Also, in the formula bar, I see the following:
"=EMBED("Forms.ComboBox.1","").

Again, clicking on the combo right now does not bring up any values that I
entered in A1:C10 on Sheet2.

What am I doing wrong?


--
Thanks,
Tom


"Dave Peterson" wrote in message
...
I don't open attachments or download files.

But I put a list of stuff on Sheet2 in A1:C10.

I added a combobox from the control toolbar to sheet1.

While still in design mode, I rightclicked on the combobox and selected
properties.

Look for ColumnCount. I used 3.
(you may want to play around with the ColumnWidths property later)

Look for LinkedCell. I typed in: A1 (on sheet1)

Look for ListFillRange. I typed in: sheet2!a1:c10

Then in B1, I used this formula:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,2,FALSE))
In C1:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,3,FALSE))

Notice the =vlookup() range is the same as the ListFillRange.

And one more property to change:
look for: Style. I used fmStyleDropDownList
That means I can't type something in that isn't on the list.

After you get the basics running, take a closer look at all those
properties.
(Save your workbook before you break anything, though!)

Tom wrote:

I have seen a spreadsheet that has a drop-down box (combo box). When
being
clicked on, it shows data in multiple columns. When selecting a value
from
any of the default values, their associated information is automatically
placed into adjacent cells.

Pls see http://www.microsheet.com/Downloads.htm and download the Time &
Expense Manager example. On the INVOICE tab, you'll see combos under
Task
Description. I'm trying to built such drop-down menu.

How can I create such drop-down menu from which I select a value and a
few
other columns are automatically filled?

Tom

--

Dave Peterson

Dave:

Never mind... I think I got it. After I closed the file and reopened it,
the combo worked fine.

Just one more question though. Right now, the Combo Box is "floating"
around on Sheet1 (even though it's locked in its position).

Instead of placing it ontop of A1, is there a way to actually have cell A1
turned into a combo box?

Finally, how to I change the FontSize property. I didn't see it listed in
the Properties nor could I actually change it from the Excel toolbar.



--
Thanks,
Tom


"Dave Peterson" wrote in message
...
I don't open attachments or download files.

But I put a list of stuff on Sheet2 in A1:C10.

I added a combobox from the control toolbar to sheet1.

While still in design mode, I rightclicked on the combobox and selected
properties.

Look for ColumnCount. I used 3.
(you may want to play around with the ColumnWidths property later)

Look for LinkedCell. I typed in: A1 (on sheet1)

Look for ListFillRange. I typed in: sheet2!a1:c10

Then in B1, I used this formula:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,2,FALSE))
In C1:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,3,FALSE))

Notice the =vlookup() range is the same as the ListFillRange.

And one more property to change:
look for: Style. I used fmStyleDropDownList
That means I can't type something in that isn't on the list.

After you get the basics running, take a closer look at all those
properties.
(Save your workbook before you break anything, though!)

Tom wrote:

I have seen a spreadsheet that has a drop-down box (combo box). When
being
clicked on, it shows data in multiple columns. When selecting a value
from
any of the default values, their associated information is automatically
placed into adjacent cells.

Pls see http://www.microsheet.com/Downloads.htm and download the Time &
Expense Manager example. On the INVOICE tab, you'll see combos under
Task
Description. I'm trying to built such drop-down menu.

How can I create such drop-down menu from which I select a value and a
few
other columns are automatically filled?

Tom

--

Dave Peterson

These objects float over the worksheet cells in a separate layer.

If you really want to use a cell, you could look at Data|Validation, but you
won't get that same multi-column effect within the combobox. But you could
concatenate your range:

=a1&" "&b1&" "&c1

But that looks pretty ugly, too.

You saw the Font property. Double click on it and you'll see a familiar dialog.

Tom wrote:

Dave:

Never mind... I think I got it. After I closed the file and reopened it,
the combo worked fine.

Just one more question though. Right now, the Combo Box is "floating"
around on Sheet1 (even though it's locked in its position).

Instead of placing it ontop of A1, is there a way to actually have cell A1
turned into a combo box?

Finally, how to I change the FontSize property. I didn't see it listed in
the Properties nor could I actually change it from the Excel toolbar.

--
Thanks,
Tom

"Dave Peterson" wrote in message
...
I don't open attachments or download files.

But I put a list of stuff on Sheet2 in A1:C10.

I added a combobox from the control toolbar to sheet1.

While still in design mode, I rightclicked on the combobox and selected
properties.

Look for ColumnCount. I used 3.
(you may want to play around with the ColumnWidths property later)

Look for LinkedCell. I typed in: A1 (on sheet1)

Look for ListFillRange. I typed in: sheet2!a1:c10

Then in B1, I used this formula:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,2,FALSE))
In C1:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,3,FALSE))

Notice the =vlookup() range is the same as the ListFillRange.

And one more property to change:
look for: Style. I used fmStyleDropDownList
That means I can't type something in that isn't on the list.

After you get the basics running, take a closer look at all those
properties.
(Save your workbook before you break anything, though!)

Tom wrote:

I have seen a spreadsheet that has a drop-down box (combo box). When
being
clicked on, it shows data in multiple columns. When selecting a value
from
any of the default values, their associated information is automatically
placed into adjacent cells.

Pls see http://www.microsheet.com/Downloads.htm and download the Time &
Expense Manager example. On the INVOICE tab, you'll see combos under
Task
Description. I'm trying to built such drop-down menu.

How can I create such drop-down menu from which I select a value and a
few
other columns are automatically filled?

Tom

--

Dave Peterson

--

Dave Peterson

Dave,

ah, I see... that works.

Truly final questions now (at least I hope).

I need to combine your solution with the INDIRECT method (value dependencies
between 2 combos).

For instance, I have 2 combos on another spreadsheet. In that spreadsheet,
Sheet2 contains the NameRange "EmployeeName" (A1:C3).

The value of cell A1 (Sheet2) is = "Tom", B1 = "Rich", C1 = "Tony".

Then, I entered a bunch of job duties into A2:A10. I named that range
"Tom". The same for B2:B10 (Name = "Rich") & for C2:C10 (Name = "Tony").

Now, back on Sheet1, cell A1, I added a combo (List) with source
"=EmployeeName".

Also, on Sheet1, cell A2, I added a 2nd combo (List) with source
"=Indirect(A1)".

So, dependent on the value in A1, I will bring up only those job
descriptions for the selected employee.

Again, I need to tie in the spreadsheet w/ the dependency combo into your
solution. I'm not sure how to create the dependencies plus listing
multiple column into the 2nd combo.

Do you have any suggestions as to how I can achieve this?

--
Thanks,
Tom


"Dave Peterson" wrote in message
...
These objects float over the worksheet cells in a separate layer.

If you really want to use a cell, you could look at Data|Validation, but
you
won't get that same multi-column effect within the combobox. But you
could
concatenate your range:

=a1&" "&b1&" "&c1

But that looks pretty ugly, too.

You saw the Font property. Double click on it and you'll see a familiar
dialog.

Tom wrote:

Dave:

Never mind... I think I got it. After I closed the file and reopened it,
the combo worked fine.

Just one more question though. Right now, the Combo Box is "floating"
around on Sheet1 (even though it's locked in its position).

Instead of placing it ontop of A1, is there a way to actually have cell
A1
turned into a combo box?

Finally, how to I change the FontSize property. I didn't see it listed
in
the Properties nor could I actually change it from the Excel toolbar.

--
Thanks,
Tom

"Dave Peterson" wrote in message
...
I don't open attachments or download files.

But I put a list of stuff on Sheet2 in A1:C10.

I added a combobox from the control toolbar to sheet1.

While still in design mode, I rightclicked on the combobox and selected
properties.

Look for ColumnCount. I used 3.
(you may want to play around with the ColumnWidths property later)

Look for LinkedCell. I typed in: A1 (on sheet1)

Look for ListFillRange. I typed in: sheet2!a1:c10

Then in B1, I used this formula:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,2,FALSE))
In C1:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,3,FALSE))

Notice the =vlookup() range is the same as the ListFillRange.

And one more property to change:
look for: Style. I used fmStyleDropDownList
That means I can't type something in that isn't on the list.

After you get the basics running, take a closer look at all those
properties.
(Save your workbook before you break anything, though!)

Tom wrote:

I have seen a spreadsheet that has a drop-down box (combo box). When
being
clicked on, it shows data in multiple columns. When selecting a
value
from
any of the default values, their associated information is
automatically
placed into adjacent cells.

Pls see http://www.microsheet.com/Downloads.htm and download the Time
&
Expense Manager example. On the INVOICE tab, you'll see combos under
Task
Description. I'm trying to built such drop-down menu.

How can I create such drop-down menu from which I select a value and a
few
other columns are automatically filled?

Tom

--

Dave Peterson

--

Dave Peterson

I'm not sure I understand, but I think I'd fix the listfillrange in code when
the other comboboxes change.

You could use this kind of code (which is kind of similar to the =indirect()
suggestion.

Option Explicit
Private Sub ComboBox1_Change()

If Me.ComboBox1.Value = "" Then
Sheet2.ComboBox1.Clear
Else
Sheet2.ComboBox1.List = Me.Range(Me.ComboBox1.Value).Value
End If

End Sub

Or you could just look at the value and set the range yourself:

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range

Set myRng = Nothing

Select Case LCase(Me.ComboBox1.Value)
Case Is = "tom": Set myRng = Me.Range("a1:c9")
Case Is = "dick": Set myRng = Me.Range("d1:f12")
Case Is = "harry": Set myRng = Me.Range("g1:i4")
End Select

If myRng Is Nothing Then
Sheet2.ComboBox1.Clear
Else
Sheet2.ComboBox1.List = myRng.Value
End If
End Sub


Hope this gives you an idea ('cause I was sorely confused!).



Tom wrote:

Dave,

ah, I see... that works.

Truly final questions now (at least I hope).

I need to combine your solution with the INDIRECT method (value dependencies
between 2 combos).

For instance, I have 2 combos on another spreadsheet. In that spreadsheet,
Sheet2 contains the NameRange "EmployeeName" (A1:C3).

The value of cell A1 (Sheet2) is = "Tom", B1 = "Rich", C1 = "Tony".

Then, I entered a bunch of job duties into A2:A10. I named that range
"Tom". The same for B2:B10 (Name = "Rich") & for C2:C10 (Name = "Tony").

Now, back on Sheet1, cell A1, I added a combo (List) with source
"=EmployeeName".

Also, on Sheet1, cell A2, I added a 2nd combo (List) with source
"=Indirect(A1)".

So, dependent on the value in A1, I will bring up only those job
descriptions for the selected employee.

Again, I need to tie in the spreadsheet w/ the dependency combo into your
solution. I'm not sure how to create the dependencies plus listing
multiple column into the 2nd combo.

Do you have any suggestions as to how I can achieve this?

--
Thanks,
Tom

"Dave Peterson" wrote in message
...
These objects float over the worksheet cells in a separate layer.

If you really want to use a cell, you could look at Data|Validation, but
you
won't get that same multi-column effect within the combobox. But you
could
concatenate your range:

=a1&" "&b1&" "&c1

But that looks pretty ugly, too.

You saw the Font property. Double click on it and you'll see a familiar
dialog.

Tom wrote:

Dave:

Never mind... I think I got it. After I closed the file and reopened it,
the combo worked fine.

Just one more question though. Right now, the Combo Box is "floating"
around on Sheet1 (even though it's locked in its position).

Instead of placing it ontop of A1, is there a way to actually have cell
A1
turned into a combo box?

Finally, how to I change the FontSize property. I didn't see it listed
in
the Properties nor could I actually change it from the Excel toolbar.

--
Thanks,
Tom

"Dave Peterson" wrote in message
...
I don't open attachments or download files.

But I put a list of stuff on Sheet2 in A1:C10.

I added a combobox from the control toolbar to sheet1.

While still in design mode, I rightclicked on the combobox and selected
properties.

Look for ColumnCount. I used 3.
(you may want to play around with the ColumnWidths property later)

Look for LinkedCell. I typed in: A1 (on sheet1)

Look for ListFillRange. I typed in: sheet2!a1:c10

Then in B1, I used this formula:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,2,FALSE))
In C1:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,3,FALSE))

Notice the =vlookup() range is the same as the ListFillRange.

And one more property to change:
look for: Style. I used fmStyleDropDownList
That means I can't type something in that isn't on the list.

After you get the basics running, take a closer look at all those
properties.
(Save your workbook before you break anything, though!)

Tom wrote:

I have seen a spreadsheet that has a drop-down box (combo box). When
being
clicked on, it shows data in multiple columns. When selecting a
value
from
any of the default values, their associated information is
automatically
placed into adjacent cells.

Pls see http://www.microsheet.com/Downloads.htm and download the Time
&
Expense Manager example. On the INVOICE tab, you'll see combos under
Task
Description. I'm trying to built such drop-down menu.

How can I create such drop-down menu from which I select a value and a
few
other columns are automatically filled?

Tom

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson

Dave:

Thanks, that works great!!!

I appreciate your help in this matter.

--
Thanks,
Tom


"Dave Peterson" wrote in message
...
I'm not sure I understand, but I think I'd fix the listfillrange in code
when
the other comboboxes change.

You could use this kind of code (which is kind of similar to the
=indirect()
suggestion.

Option Explicit
Private Sub ComboBox1_Change()

If Me.ComboBox1.Value = "" Then
Sheet2.ComboBox1.Clear
Else
Sheet2.ComboBox1.List = Me.Range(Me.ComboBox1.Value).Value
End If

End Sub

Or you could just look at the value and set the range yourself:

Option Explicit
Private Sub ComboBox1_Change()

Dim myRng As Range

Set myRng = Nothing

Select Case LCase(Me.ComboBox1.Value)
Case Is = "tom": Set myRng = Me.Range("a1:c9")
Case Is = "dick": Set myRng = Me.Range("d1:f12")
Case Is = "harry": Set myRng = Me.Range("g1:i4")
End Select

If myRng Is Nothing Then
Sheet2.ComboBox1.Clear
Else
Sheet2.ComboBox1.List = myRng.Value
End If
End Sub


Hope this gives you an idea ('cause I was sorely confused!).



Tom wrote:

Dave,

ah, I see... that works.

Truly final questions now (at least I hope).

I need to combine your solution with the INDIRECT method (value
dependencies
between 2 combos).

For instance, I have 2 combos on another spreadsheet. In that
spreadsheet,
Sheet2 contains the NameRange "EmployeeName" (A1:C3).

The value of cell A1 (Sheet2) is = "Tom", B1 = "Rich", C1 = "Tony".

Then, I entered a bunch of job duties into A2:A10. I named that range
"Tom". The same for B2:B10 (Name = "Rich") & for C2:C10 (Name =
"Tony").

Now, back on Sheet1, cell A1, I added a combo (List) with source
"=EmployeeName".

Also, on Sheet1, cell A2, I added a 2nd combo (List) with source
"=Indirect(A1)".

So, dependent on the value in A1, I will bring up only those job
descriptions for the selected employee.

Again, I need to tie in the spreadsheet w/ the dependency combo into your
solution. I'm not sure how to create the dependencies plus listing
multiple column into the 2nd combo.

Do you have any suggestions as to how I can achieve this?

--
Thanks,
Tom

"Dave Peterson" wrote in message
...
These objects float over the worksheet cells in a separate layer.

If you really want to use a cell, you could look at Data|Validation,
but
you
won't get that same multi-column effect within the combobox. But you
could
concatenate your range:

=a1&" "&b1&" "&c1

But that looks pretty ugly, too.

You saw the Font property. Double click on it and you'll see a
familiar
dialog.

Tom wrote:

Dave:

Never mind... I think I got it. After I closed the file and reopened
it,
the combo worked fine.

Just one more question though. Right now, the Combo Box is
"floating"
around on Sheet1 (even though it's locked in its position).

Instead of placing it ontop of A1, is there a way to actually have
cell
A1
turned into a combo box?

Finally, how to I change the FontSize property. I didn't see it
listed
in
the Properties nor could I actually change it from the Excel toolbar.

--
Thanks,
Tom

"Dave Peterson" wrote in message
...
I don't open attachments or download files.

But I put a list of stuff on Sheet2 in A1:C10.

I added a combobox from the control toolbar to sheet1.

While still in design mode, I rightclicked on the combobox and
selected
properties.

Look for ColumnCount. I used 3.
(you may want to play around with the ColumnWidths property later)

Look for LinkedCell. I typed in: A1 (on sheet1)

Look for ListFillRange. I typed in: sheet2!a1:c10

Then in B1, I used this formula:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,2,FALSE))
In C1:
=IF(A1="","",VLOOKUP(A1,Sheet2!A1:C10,3,FALSE))

Notice the =vlookup() range is the same as the ListFillRange.

And one more property to change:
look for: Style. I used fmStyleDropDownList
That means I can't type something in that isn't on the list.

After you get the basics running, take a closer look at all those
properties.
(Save your workbook before you break anything, though!)

Tom wrote:

I have seen a spreadsheet that has a drop-down box (combo box).
When
being
clicked on, it shows data in multiple columns. When selecting a
value
from
any of the default values, their associated information is
automatically
placed into adjacent cells.

Pls see http://www.microsheet.com/Downloads.htm and download the
Time
&
Expense Manager example. On the INVOICE tab, you'll see combos
under
Task
Description. I'm trying to built such drop-down menu.

How can I create such drop-down menu from which I select a value
and a
few
other columns are automatically filled?

Tom

--

Dave Peterson

--

Dave Peterson

--

Dave Peterson