I know how to use the Forms combobox...

I would now like to learn how to use the Control toolbar combobox. Ho
do I accomplish this??

suppose I have 3 columns with info...

A B
Zip Code Area

11433 Jamaica
10458 Fordham
10003 Cooper
'.... etc. and so on

how do I make a combobox that contains the zip codes and as one i
selected the values in column B are pulled???

Please write clear steps, I am an amateur.

- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com

put the combobox on the sheet -

Right click on and select listfillrange. Put Sheet1!A1:A100 there. Then
double click on the combobox to get to the code.

This will take you to the sheet code and the click event for the combobox

Private Sub Combobox1_Click()

End Sub

Put code here

Private Sub Combobox1_Click()
res = Application.Match(combobox1.Value, _
Worksheets("Sheet1").Range("A1:A100"),0)
msgbox Worksheets("Sheet1").Range("B1:B100)(res).Value

End Sub

Go back to the workbook, go to the control toolbox toolbar and click the
upper left icon so it is not depressed - this takes you out of design mode.

Now if you select a zipcode, the message box should show the location.

--
Regards,
Tom Ogilvy


"nrage21 " wrote in message
...
I know how to use the Forms combobox...

I would now like to learn how to use the Control toolbar combobox. How
do I accomplish this??

suppose I have 3 columns with info...

A B
Zip Code Area

11433 Jamaica
10458 Fordham
10003 Cooper
'.... etc. and so on

how do I make a combobox that contains the zip codes and as one is
selected the values in column B are pulled???

Please write clear steps, I am an amateur.

- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

If it's too much work... can some1 direct me to a site that ha
instructions on how to make a combobox work??


-Larry-
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Perhaps I should explain clearer...

----A------------B
ZipCode---- Area

11433------Jamaica
10458------Fordham
10003------Cooper
'.... etc. and so on

I would like the combobox to have the zip code values in column A..
and as one value is selected it pulls the corresponding value in colum
B.

When I right click on the combobox there is no "listfillrange" option.
:(


- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com

Sorry Tom! (stupid me!!)

listfillrange from the properties box :)

I followed your instructions and when I get off design mode and clic
on the combobox I get a syntax error.

Private Sub Combobox1_Click()
res = Application.Match(ComboBox1.Value, _
Worksheets("Sheet1").Range("B1:B17"), 0)
msgbox Worksheets("Sheet1").Range("A1:A17)(res).Value
End Sub

any ideas???

- Larry -
VBA Amateu

--
Message posted from http://www.ExcelForum.com

well, their was a paren missing I think. Should be (I believe I had my
columns reversed as well)
Private Sub Combobox1_Click()
res = Application.Match(ComboBox1.Value, _
Worksheets("Sheet1").Range("A1:A17"), 0)
MsgBox Worksheets("Sheet1").Range("B1:B17")(res).Value
End Sub


but based on your later posting

Private Sub Combobox1_Click()
res = Application.Match(ComboBox1.Value, _
Worksheets("Sheet3").Range("A1:A17"), 0)
worksheets("Sheet1").Range("E1").Value = _
Worksheets("Sheet3").Range("B1:B17")(res).Value
End Sub

--
Regards,
Tom Ogilvy


"nrage21 " wrote in message
...
Sorry Tom! (stupid me!!)

listfillrange from the properties box :)

I followed your instructions and when I get off design mode and click
on the combobox I get a syntax error.

Private Sub Combobox1_Click()
res = Application.Match(ComboBox1.Value, _
Worksheets("Sheet1").Range("B1:B17"), 0)
msgbox Worksheets("Sheet1").Range("A1:A17)(res).Value
End Sub

any ideas???

- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

Not missing a paren, missing a double quote.

--
Regards,
Tom Ogilvy

"nrage21 " wrote in message
...
Sorry Tom! (stupid me!!)

listfillrange from the properties box :)

I followed your instructions and when I get off design mode and click
on the combobox I get a syntax error.

Private Sub Combobox1_Click()
res = Application.Match(ComboBox1.Value, _
Worksheets("Sheet1").Range("B1:B17"), 0)
msgbox Worksheets("Sheet1").Range("A1:A17)(res).Value
End Sub

any ideas???

- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

listfillrange property of the combobox Sheet1!A1:A100

Private Sub Combobox1_Click()
Dim res as variant
res = Application.Match(combobox1.Value, _
Worksheets("Sheet3").Range("A1:A100"),0)
Worksheets("Sheet1").Range("E1").Value = _
Worksheets("Sheet3").Range("B1:B100)(res).Value

End Sub


--
Regards,
Tom Ogilvy

"nrage21 " wrote in message
...
Perhaps I should explain clearer...

----A------------B
ZipCode---- Area

11433------Jamaica
10458------Fordham
10003------Cooper
'.... etc. and so on

I would like the combobox to have the zip code values in column A...
and as one value is selected it pulls the corresponding value in columb
B.

The workbook has 3 sheets. Sheet1 has the combobox in Cell A1 I want
the corresponding value of the selection to be place in Cell E1.

The Zip Code and Area List are in Sheet3.

Can anyone help?? or can anyone direct me to a source where this kind
of info is available??

Thanks!!

- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

corrected code: (missing a double quote in the last line)

Private Sub Combobox1_Click()
Dim res as variant
res = Application.Match(combobox1.Value, _
Worksheets("Sheet3").Range("A1:A100"),0)
Worksheets("Sheet1").Range("E1").Value = _
Worksheets("Sheet3").Range("B1:B100")(res).Value

End Sub

"Tom Ogilvy" wrote in message
...
listfillrange property of the combobox Sheet1!A1:A100

Private Sub Combobox1_Click()
Dim res as variant
res = Application.Match(combobox1.Value, _
Worksheets("Sheet3").Range("A1:A100"),0)
Worksheets("Sheet1").Range("E1").Value = _
Worksheets("Sheet3").Range("B1:B100)(res).Value

End Sub


--
Regards,
Tom Ogilvy

"nrage21 " wrote in message
...
Perhaps I should explain clearer...

----A------------B
ZipCode---- Area

11433------Jamaica
10458------Fordham
10003------Cooper
'.... etc. and so on

I would like the combobox to have the zip code values in column A...
and as one value is selected it pulls the corresponding value in columb
B.

The workbook has 3 sheets. Sheet1 has the combobox in Cell A1 I want
the corresponding value of the selection to be place in Cell E1.

The Zip Code and Area List are in Sheet3.

Can anyone help?? or can anyone direct me to a source where this kind
of info is available??

Thanks!!

- Larry -
VBA Amateur


---
Message posted from http://www.ExcelForum.com/

Thanks for your patiente Tom... Just what I was looking for... It work
perfectly!!

--
Message posted from http://www.ExcelForum.com

Are you saying you can't follow the instructions I gave?


here is a start:


http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

Microsoft(R) Visual Basic(R) for Applications Examples for Controlling
UserForms in Microsoft Excel 97

This Application Note is an introduction to manipulating UserForms in
Microsoft Excel 97. It includes examples and Microsoft Visual Basic for
Applications macros that show you how to take advantage of the capabilities
of UserForms and use each of the ActiveX controls that are available for
UserForms

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.



http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


John Walkenbach's site:
http://j-walk.com/ss/excel/tips/userformtips.htm
Userform Tips

Peter Aiken Articles:

watch word wrap. the URL should all be one line.
Part I
http://msdn.microsoft.com/library/en...uctiontoUserFo
rmsPartI.asp
Part II
http://msdn.microsoft.com/library/en...uctiontoUserFo
rmsPartII.asp

--
Regards,
Tom Ogilvy


"nrage21 " wrote in message
...
If it's too much work... can some1 direct me to a site that has
instructions on how to make a combobox work??


-Larry-
VBA Amateur


---
Message posted from http://www.ExcelForum.com/