How can I list values in a combo box?

[Please Help]

Hello all,

I have a sheet with student information. Of the student information, Column
B is Student No. How can I have the student numbers listed in my combo box
of my user form.

I tried having "B:B" in the RowSource event of combo box, and I saw the
student numbers in the design mode. When I ran the user form, I didn't see
anything.

Then I tried having "B" in the ControlSource event and "B:B" in the
RowSource event of combox box, and, again, I saw them in the design mode.
When I ran the user form, I got an error "Path/file access error". The rest
of events was unchanged.

Please help.

Thanks.

 

[JLGWhiz]

Set column count property to 1, bound column to 0.
Control Source requires a cell reference e.g. "a1".
The Control Source displays the value of the item
selected in the combobox. Make sure your column
widths property is set to a number greater than 0,
probably 30 or 40 will be enough. If you are entering
the row source directly into the properties window, don't
use quotation marks. If you are using code to assign the
properties, DO use quotation marks.

 

[Please Help]

Hi JLGWhiz,

Thanks for you response. As you instructed, below is my current settings:

ColumnCount = 1
BoundColumn = 0
ControlSource = B2
ColumnWidth = 60
RowSource = B:B

When I ran the user form, I got an error "Path/file access error". When I
clicked OK on that, I got another error "Run-time error 75: Could not find
the specified object.", and when I clicked "Debug", it pointed me to the code
"frmStudentNoLookup.Show".

Basically, I have two user forms: a main form and a lookup form. The code
"frmStudentNoLookup.Show" is located in the command button code on the main
form to call up the lookup form. The combo box that I need help with is
located on the lookup form.

As I previously stated in the previous post, if I include a cell reference
in the ControlSource, I would get the above errors. If I do not include a
cell ref, the control box would be blank.

Do you know what caused it to happen?

Please help.

Thanks.

 

[JLGWhiz]

I am not familiar with the error message you described and will have to do
some research to figure it out. Off hand, it sounds like your main form is
the culprit for the Path/file access error. What does the error help file
tell you to look for? Are you in the same workbook that the form is in when
you call it to show? If not, you would need to qualify the call with the
parent object workbook. I'll have to dig into this one a little deeper to
get a good understanding of what causes that message to pop up.

 

[Please Help]

I don't think the main form is causing the errors because the main form is
just a list of command buttons to call up various lookup forms. Eventually,
I will have 4 lookup forms (one to look up by student no, the other one to
look up by student name, etc.).

All 4 lookup forms will be retrieving student information from the sheet
that is located in the same workbook. So the sheet containing the student
info, the main form and 4 lookup forms are in the same workbook.

Thanks again very much for your patience and continuing help.

If there is something that I have not mentioned, please ask me.

 

[JLGWhiz]

Well, from what I can gather, there is a disconnect somewhere between the
project where you have the call for the main form and the lookup form. The
error message dialogue says that the message occurs when the file cannot be
found. So that means it could be caused by typos in the item name or any
qualifying names. It could be because the item is located in a different
workbook, or is inside a limited access code module or is in another project
module. There are probably more conditions that I can't think of tonight,
but I think you get the general idea

If I do not include a

cell ref, the control box would be blank.

I'm not sure what you are referring to as the control box but if you meat
the worksheet cell, then that is normal. There would be no lind without the
Control Source being specified. If you meant the ComboBox is blank, it
shouldn't make any difference because it is the RowSource that loads the
ComboBox.

You need to be sure that both forms show in your Project window in the VBE.
If they both show there, then check all of your spelling in your command
button code to make sure there are no typos. After that, I don't know what
else.

 

[Darren Hill]

Just on the off-chance this applies to you:

I got that Path/File access error a lot when using Excel 2007 (usually
shortly before a crash), and it stopped happening once I applied Service
Pack 1.

 

[Please Help]

Darren,

Thanks for your response. I had the same experience that you had, except I
am using Excel 2003 with SP2.

The user forms that I previously created had no problem, except this time.
Of course, I was not involved in combo boxes on the previous ones.

Thanks.