Control Combobox Linked Cell vs Forms Combobox Linked Cell

R

RamblinWreck

I need some quick help from a forum "expert". In my younger naive days
I wrote a spreadsheet that has about 30 drop down menus via th
combobox button from the forms menu.

Many people use this spreadsheet - including my new boss who has ba
eyesight. Well, the new boss requested that I increase the font siz
of the drop down menu which can't be done with the forms combobox.
However, the font size of the drop down menu can be easily changed on
control combobox.

The problem is that the output (the linked cell) of a control combobo
is the excact text from the drop down menu. This is a problem as th
linked cell form the forms combobox is a numeric value, and ALL of m
logic is predicated on the numeric value.

Yes, I can use the VLOOKUP function to convert control combobox outpu
to a numeric value, but that's a pain. Is there a way (perhaps via
properites setting) to change the output of the control combobox fro
the text vaule to a numerical value ?

Also, please understand that I am not lazy. I spent about an hou
searching this forum and other forums for the answer to my question
but I got nowhere. I guess I just don't know how to search !

Many thanks for your patience in reading this
 
K

Karl

A control combobox has a listindex function that should do the job

Private Sub ComboBox1_Change()
Worksheets("Sheet1").Range("A1") = ComboBox1.ListIndex
End Sub
 
R

RamblinWreck

Thank you very much !! Because I am somewhat new to this stuff, it too
me about 30 seconds to figure out your reply.

The key point is not to use the "linked cell" in the comb bo
properties, but to use the code option in the properties.

The code that I used was:

Private Sub ComboBox1_Change()
Worksheets("Sheet1").Range("a1") = ComboBox1.ListIndex + 1

End Sub

Cell a1 on Sheet1 essentially becomes the linked cell. I had to ad
the "+1" to keep the index numbering the same as that used by the form
combo box.

Again, thanks for the info !!!! Could you suggest some books on thi
stuff, i.e., ones that describe the VBA macros and coding and all th
properties and funtions of the various icons on the controls tool ba
?

Thanks !!!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top