Trouble w/ ActiveX Controls (no userform) Excel 2002.

[Chris]

Hi all,

I have a spreadsheet that is constantly populating combo
boxes (ActiveX) whenever a checkbox is clicked or some
other event occurs. The combo boxes are populated in
modules using the .AddItem method upon the opening of the
workbook, and upon a checkbox = true click event. The
issue arises that whenever I close the workbook the combo
box unpopulates and forgets the user selection that was
made.

When the workbook is saved I want it to remember the value
that the user last selected in each combo box and to avoid
the run time error 1004. Often times the combo box text
property is blank and doesn't contain anything because it
doesn't apply to the project.

Please help me determine the best way to accomplish this.
I have 30 combo boxes I need to do this for. Thanks in
advance!

Example of what I have done below.
This errors out the varBatesNumberingChange line. I can't
figure out how to fix it.

Public Sub cboBatesNumbering_Change()

Dim varBatesNumberingChange As Variant

varBatesNumberingChange = Sheets
("Input").cboBatesNumbering.Text

If Not IsNumeric(varBatesNumberingChange) = True And
varBatesNumberingChange <> "" Then
Range("Input_cboBatesNumbering_choice").Value =
Sheets("Input").cboBatesNumbering.Text
Application.ScreenUpdating = True
Else
Range("Input_cboBatesNumbering_choice").Value = ""
Application.ScreenUpdating = True
End If
End Sub

 

[Patrick Molloy]

the only way to do this would be to save the values somewhere - the registry
perhaps? - each time a slection is made. when re-loading the sheet the
activeX controls could be reset through the auto_open procedure or the
workbook open event.

When you close a workbook, the application cleans up memory by destroying
instances of the controls...hence they lose their data.

 

[Guest]

Patrick thanks for your reply.

In the workbook I have range names setup that I feed the
combo box values to. The main issue I am having is a (Run-
Time error '1004': Method 'Sheets' of object'_Global)
Error. This occurs in the cboBatesNumbering_Change
procedure below. If you could help me to correct the
syntax or structure of the code so it doesn't error out at
run time I would be forever indebted to you.
Unfortunately my project time on this is overdue. Thanks.

 

[Chris]

Patrick thanks for your reply.

In the workbook I have range names setup that I feed the
combo box values to. The main issue I am having is a (Run-
Time error '1004': Method 'Sheets' of object'_Global)
Error. This occurs in the cboBatesNumbering_Change
procedure below. If you could help me to correct the
syntax or structure of the code so it doesn't error out at
run time I would be forever indebted to you.
Unfortunately my project time on this is overdue. Thanks.

 

[keepitcool]

Chris,

The range objects you use are not "qualified"
e.g. are not pointed to a specific worksheet object.
It may be an Activesheet object, but in your case it's probably
a worksheet in the workbook from which you run the code.

ThisWorkbook.Worksheets("controls").Range("mynamed range")
would work best

Also remember that when you use ControlSource (which accepts cell's
address strings) to ALWAYS use the EXTERNAL address for a range.
(which is a fully qualifid address of workbook/sheet/range)

MyComboBOx1.ControlSource= myRng.address(external:=true)

This way you're sure you KNOW where it points, regardless of which book
or sheet is active when the form is loaded.


keepITcool

< email : keepitcool chello nl (with @ and .) >
< homepage: http://members.chello.nl/keepitcool >