Hi,
I've created a Combo Box inside a sheet based on data from another sheet.
When an item is selected in this list I would like excel to fill a couple
cells with the data that is correlated to this item from the other sheet.
I've been trying for days now. Reading macro tutorials and such. And just
can't figure out quite how to do what I want. Is there anything anyone
recommends reading?
Or, I've hit a number of different road blocks. But one in particular is:
I've got a cell with a number corresponding to the row of data I want on the
other sheet, but I don't know how tell excel something like
= Data!A(Main!B24) where the row is sort of variable.
Sorry for all this babble, but any help would be appreciated.

PS: I started initially with just a data validation list that I could go
back to if that would make it easier.

Hi,

First question is what type of combo box do you have? There are 2 types.
When using xl2007 the two types are displayed when you click on Insert on the
Design Ribbon (Form control and ActiveX controls). In pre xl2007 versions,
one is created from a button on the Forms tool bar and the other is created
from a similar button on the Control Tool Box tool bar.

Changes to a combox created from the Forms toolbar only need you to right
click on the combo box and you get a hatched border around the combo box and
a dialog box with some options.

Changes to a combo box created from the Control Tool Box tool bar (ActiveX
control) require you to select the Design Mode first which is a button on the
Control Tool Box bar which looks like a blue set square, pencil and ruler and
then right click the control.

Unfortunately the help files do not do a good job of specifying which type
of control is being referred to.

Regards,

OssieMac

Hi,
Thanks for that clarification. I am using a Form control combo box. I
wouldn't know how to write it, but what I would want to program was every
time the combo box changed values, Excel would find the appropriate data
based on the selection contained in the separate worksheet and fill in some
cells. Any direction from here?

Hi again,

Ok I will now make a couple of assumptions and if my assumptions are wrong
then get back to me and Ill try again. You may not need all the info I give
you but it might still help in the future.

I assume that the combo box is populated by a list on one of your worksheets
using format control- Input range. When using a list like this you should
ensure that you have one column available to the left of the list of the
input range and you number the cells in this extra column 1,2,3,4 etc to the
bottom of your data as per the following example:-

1 John
2 Angela
3 Elizabeth
4 Peter
5 Paul
6 Mary

Assume that the data above is in cells A1 to B6. (Input range for the combo
box is B1:B6. Dont include column A for the combo box Input range.)

For your combo box I will assume that you have set the linked cell to C1
which will display the number of the selection.

Now when you make a selection say €˜Peter, the linked cell displays 4. To
get a cell to display €˜Peter, use the VLOOKUP function in ANOTHER cell like
this:-

=VLOOKUP(C1,$A$1:$B$10,2,FALSE)

The above assumes the lookup range is on the same worksheet as the VLOOKUP
formula. If the lookup range is on another worksheet then it would look like
this:-

=VLOOKUP(C1,Sheet2!$A$1:$B$10,2,FALSE)

Where C1 is the linked cell to the combo box.
$A$1:$B$10 is the lookup range with the numbers in column A and the data for
the combo box in column B.
2 is the second column of the lookup range because this is the data to
display.
FALSE says only display exact matches.

If I understand correctly, your specific question relates to how to address
a row in another worksheet from a number displayed on the worksheet. I am
assuming that this is the number displayed in the combo box linked cell. The
following samples of code may help you to understand.

The first macro is a very simplistic method of select, copy, select again
and paste as you would do in the interactive mode.

The second macro is simply an alternative method of doing the same as the
first macro does.

The third macro is just samples of code using the Cells method with numbers
in lieu of the A1 method.

Sub Test_Simplistic()
'Copy a row of data from one sheet
'and paste to another sheet

Dim numbRow As Long

numbRow = Sheets("Sheet1").Range("C1")

Sheets("Sheet3").Select
Rows(numbRow).Select
Selection.Copy
Sheets("Sheet1").Select
Rows(10).Select
ActiveSheet.Paste

End Sub


Sub Test_Alternative()

Dim numbRow As Long

numbRow = Sheets("Sheet1").Range("C1")

'Note the space and underscore at the end of
'the line is a line break and the 2 lines of code
'perform as if on one line.

Sheets("Sheet3").Rows(numbRow).Copy _
Destination:=Sheets("Sheet1").Rows(10)

End Sub

Sub Test_Address_Cells()

'Addressing cells using numbers
'Selects cell A1 of the active sheet
Cells(1, 1).Select

Dim rowNumb As Single
Dim colNumb As Single

rowNumb = 8
colNumb = 5
Cells(rowNumb, colNumb).Select

'Note that when you use the Cells function the
'row is first; unlike the alpha numeric method
'where the column is first.

'Following is same as Range("A1:J1").Select
Range(Cells(1, 1), Cells(1, 10)).Select


End Sub

Regards,

OssieMac

"ybkusz" wrote:

Hi,
Thanks for that clarification. I am using a Form control combo box. I
wouldn't know how to write it, but what I would want to program was every
time the combo box changed values, Excel would find the appropriate data
based on the selection contained in the separate worksheet and fill in some
cells. Any direction from here?

Thank you soo much, that's exactly what I needed ten fold!

"OssieMac" wrote:

Hi again,

Ok I will now make a couple of assumptions and if my assumptions are wrong
then get back to me and Ill try again. You may not need all the info I give
you but it might still help in the future.

I assume that the combo box is populated by a list on one of your worksheets
using format control- Input range. When using a list like this you should
ensure that you have one column available to the left of the list of the
input range and you number the cells in this extra column 1,2,3,4 etc to the
bottom of your data as per the following example:-

1 John
2 Angela
3 Elizabeth
4 Peter
5 Paul
6 Mary

Assume that the data above is in cells A1 to B6. (Input range for the combo
box is B1:B6. Dont include column A for the combo box Input range.)

For your combo box I will assume that you have set the linked cell to C1
which will display the number of the selection.

Now when you make a selection say €˜Peter, the linked cell displays 4. To
get a cell to display €˜Peter, use the VLOOKUP function in ANOTHER cell like
this:-

=VLOOKUP(C1,$A$1:$B$10,2,FALSE)

The above assumes the lookup range is on the same worksheet as the VLOOKUP
formula. If the lookup range is on another worksheet then it would look like
this:-

=VLOOKUP(C1,Sheet2!$A$1:$B$10,2,FALSE)

Where C1 is the linked cell to the combo box.
$A$1:$B$10 is the lookup range with the numbers in column A and the data for
the combo box in column B.
2 is the second column of the lookup range because this is the data to
display.
FALSE says only display exact matches.

If I understand correctly, your specific question relates to how to address
a row in another worksheet from a number displayed on the worksheet. I am
assuming that this is the number displayed in the combo box linked cell. The
following samples of code may help you to understand.

The first macro is a very simplistic method of select, copy, select again
and paste as you would do in the interactive mode.

The second macro is simply an alternative method of doing the same as the
first macro does.

The third macro is just samples of code using the Cells method with numbers
in lieu of the A1 method.

Sub Test_Simplistic()
'Copy a row of data from one sheet
'and paste to another sheet

Dim numbRow As Long

numbRow = Sheets("Sheet1").Range("C1")

Sheets("Sheet3").Select
Rows(numbRow).Select
Selection.Copy
Sheets("Sheet1").Select
Rows(10).Select
ActiveSheet.Paste

End Sub


Sub Test_Alternative()

Dim numbRow As Long

numbRow = Sheets("Sheet1").Range("C1")

'Note the space and underscore at the end of
'the line is a line break and the 2 lines of code
'perform as if on one line.

Sheets("Sheet3").Rows(numbRow).Copy _
Destination:=Sheets("Sheet1").Rows(10)

End Sub

Sub Test_Address_Cells()

'Addressing cells using numbers
'Selects cell A1 of the active sheet
Cells(1, 1).Select

Dim rowNumb As Single
Dim colNumb As Single

rowNumb = 8
colNumb = 5
Cells(rowNumb, colNumb).Select

'Note that when you use the Cells function the
'row is first; unlike the alpha numeric method
'where the column is first.

'Following is same as Range("A1:J1").Select
Range(Cells(1, 1), Cells(1, 10)).Select


End Sub

Regards,

OssieMac

"ybkusz" wrote:

Hi,
Thanks for that clarification. I am using a Form control combo box. I
wouldn't know how to write it, but what I would want to program was every
time the combo box changed values, Excel would find the appropriate data
based on the selection contained in the separate worksheet and fill in some
cells. Any direction from here?