Combobox problem, please help this novice!

[Pierre]

Hi,

I hope there is someone who can help me solve a problem.

I have a database sheet in excel called "datadga"

I have a userform on which i have a multipage object with a lot of text
boxes and comboboxes.
The use chooses an item from a dropdownlist on the multipage object and all
data of this particular item is retreived form the database sheet.
This works fine with textboxes, even if de database sheet does not have an
entry for this specific textbox. It leaves the textbox empty.

However, when i try to fill a combobox with teh value from a cell in the
database, it only works if there is a value present in that particular cell.
Otherwise i get an error for that particular combobox.

My question: How can i get rid of the error and let the program continue.
The combobox thould the have no value.

I now have the following code;

when a button is clicked, alle fields on the multipage object are filled
with data from the data sheet in the following way;

txt_opnpnapd1 = Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column + 75)
cbo_opnpnapd1 = Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column + 76)
etc..(over 120 fields)

This works fine for both textbox and combobox as long as the cells in
"klantmatrix" are all filled.
When a cell is empty, the combobox presents an error and the program stops.

Please help me so i can move on with the application.

Pierre

 

[Per Jessen]

Hi Pierre

Check if the cell is empty before you assign a value to the combobox.

txt_opnpnapd1 = Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column + 75)
If Not IsEmpty(Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column + 76)) Then
cbo_opnpnapd1 = Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column + 76)
End If

Regards,

Per

 

[Pierre]

Hi Per,

Thank you again, this works fine als but..

if the cell is not empty but has a value that is not in the combobox, it
should also not be a problem and it still is if i use your method.
Any suggestions on that ?
Thanks,
Pierre

 

[Per Jessen]

Hi Pierre

Thanks for your reply.

I am not sure what your problem are.


Regards,
Per

 

[Pierre]

Hi Per,

What i mean is the following;

the combobox is filled in vba (not from the datadga sheet).
In the datadga sheet there are cells that are not empty and not have a value
that is also in the combobox.
If i then use your code, i still get an error message because the cell
contents does not match any item in the combobox.

Any ideas?
Thanks,
Pierre

 

[Per Jessen]

Hi Per,

What i mean is the following;

the combobox is filled in vba (not from the datadga sheet).
In the datadga sheet there are cells that are not empty and not have a value
that is also in the combobox.
If i then use your code, i still get an error message because the cell
contents does not match any item in the combobox.

Any ideas?
Thanks,
Pierre

"Per Jessen" <[email protected]> schreef in bericht





- Vis tekst i anførselstegn -

Hi Pierre

I have solved your problem

Syntax:
Combobox1=Range("A1").Value

Your line will be:
cbo_opnpnapd1 = Worksheets("datadga").Cells(klantmatrix.Row,
klantmatrix.Column + 76) .Value

Regands,
Per