How do I create a combobox???
 

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

How do I create a combobox???
 

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/

How do I create a combobox???
 

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

How do I create a combobox???
 

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.

How do I create a combobox???
 

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


- Larry -
VBA Amateu

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

How do I create a combobox???
 

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/

How do I create a combobox???
 

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

How do I create a combobox???
 

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/

How do I create a combobox???
 

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/

How do I create a combobox???
 

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/

How do I create a combobox???
 

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/

How do I create a combobox???
 

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

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

How do I create a combobox???
 

Hmmm... the code works well when combobox contains the areas and whe
selected displays the zip codes in cell E1... it doesnt work at all th
other way around when the combobox contains the numbers I get
run-time error '13'.....

This is the code (thanks to Tom)...

Private Sub Combobox1_Click()
Dim res as variant
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

Sheet3: contains 2 columns; column A =list of zip codes
column B=list of areas
Sheet1: contains combobox which listfillrange is set as Sheet3!A1:A17

I don't know but It might be the data-type, because when I add a
letter in from of a zip code number it places the wanted value on cel
E1. Tom! or anyone! any ideas??

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

How do I create a combobox???
 

Your learning it now. I don't know a better way than by doing it. If you
mean what is a good reference, there are many books written on the topic.
John Walkenbach's Excel power programming is often suggested.

http://www.j-walk.com should have a link to his books.

--
Regards,
Tom Ogilvy

nrage21 wrote in message
...
Thanks again Tom!... you are really a life saver... where can I learn
VBA so I can be nice like you? :)

- Larry -
VBA Amateur


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