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

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

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

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.

-----Original Message-----
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.

--
Patrick Molloy
Microsoft Excel MVP
----------------------------------
"Chris" wrote in
message
...
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


.

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

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


"Chris" wrote:

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.

-----Original Message-----