HELP!!!! VB Macro in Excel

[shreyoshi_c]

hi guys
i am new to excel and i have made this macro in VB. using it i am
populating a dropdown list in the excel sheet.
now this list has some 1200 entries of names. i want a the user to be
able to reach a particular name by just entering the first letter of
the name.

how do i provide this funcionality in my page.
i can use VB macro but i would need a reference code atleast


thanx
regards
Shreya

 

[Ivan Raiminius]

Hi Shreya,

in properties (in design mode right-click, properties) of the combobox
or listbox control, property "Match entry", choose value
"0-fmMatchEntryFirstLetter".

Regards,
Ivan

 

[shreyoshi_c]

thanx dear
but i have this list box in excel so there are no properties as such.
that is some functionality i can give in VB. but i want it in Excel

 

[Ivan Raiminius]

Hi Shreya,

yes, you can edit properties of list box embedded in Excel sheet.

In design mode (the mode where you can resize and move the control),
right click and follow my preceding post.

Regards,
Ivan

 

[NickHK]

Use the list box from the Control Toolbox, not from Forms.

NickHK

 

[shreyoshi_c]

i found my prob i have used a dropdown list so i am using a combo box
and not a list box. since i am using a dropdown list and the list is
being populated by another column of data in the same sheet.so i will
have to stick to that. now tel me how i can handle that.
either i should be able to populate my data column in the list box
(which incidentally i dont know how to ) and then use ur idea.
or find a solution with the use of a combo box

 

[Ivan Raiminius]

Hi Shreya,

you should be able to edit properties of both listbox and combobox. Of
course use controls from Control Toolbox, not from Forms (as Nick
remarked).

Regards,
Ivan

 

[shreyoshi_c]

i found my prob i have used a dropdown list so i am using a combo box
and not a list box. since i am using a dropdown list and the list is
being populated by another column of data in the same sheet.so i will
have to stick to that. now tel me how i can handle that.
either i should be able to populate my data column in the list box
(which incidentally i dont know how to ) and then use ur idea.
or find a solution with the use of a combo box

 

[Ivan Raiminius]

Hi,

listbox and/or combobox you can populate with excel range also in
properties - it is "ListFillRange" property, where you insert something
like this: Sheet1!D25.

Regards,
Ivan

 

[NickHK]

With either a list or combo box the approach is the same. These properties
will be useful to read about in Help:
..MatchEntry
..MatchRequired & .Style (Combo only)
..ListFillRange
..LinkedCell

NickHK

 

[shreyoshi_c]

but how do i populate my data in the combo box (using control tollbox)
since it is not giving me the ->control option in -->format control as
i had in the combo box (using form). this way my whole list which i was
accessing from another column in the excel sheet is lost. funtionality
cannot be applied.

 

[NickHK]

Right click the control and select "Properties".

NickHK

 

[Don Wiss]

now this list has some 1200 entries of names. i want a the user to be
able to reach a particular name by just entering the first letter of
the name.

I've found combo boxes on user forms to be limited to about 100 entries.
Presumably the combo box from the control toolbar would have the same
limitations.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[NickHK]

Don,
No, I can fit some 10000 entries.
Whether that is a good idea or not is another question...

NickHK

 

[shreyoshi_c]

thanx a lot guys u have solved my problem. Special thanx to nick and
ivan for helping an ametuer like me.
I am really grateful for ur help.

 

[Don Wiss]

No, I can fit some 10000 entries.
Whether that is a good idea or not is another question...

Well, the time I tried a couple hundred it clearly failed. The solution was
to cut it up. Maybe it was because the list behind the combo box had some
10 columns.

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[shreyoshi_c]

hi again
i have got a new problem this time. I have managed to populate the data
and all in the Combo box and its doing first letter select. but the
trouble is earlier in the list box (form type) when i used to select an
option i used to set the data in another cell.
it was giving the index of the data and i was doing some calculations
on the basis of this index value(it was a numeric value.)


but now when i select from the combo box its giving me the Data value
from the list which is of no use as far as my calculations are
concerned as its a data value and (not numeric). so what should i do to
get the index value instead of the exact text from the list in the
combo box.

I tried using bound column and set another column containing the index
of the list but its not working. even column count is of no use


plz help out.
thanx in advance

 

[Don Wiss]

but now when i select from the combo box its giving me the Data value
from the list which is of no use as far as my calculations are
concerned as its a data value and (not numeric). so what should i do to
get the index value instead of the exact text from the list in the
combo box.

ComboBox1.ListIndex

Don <www.donwiss.com> (e-mail link at home page bottom).

 

[shreyoshi_c]

thanx a lots for helping me out.