Excel ActiveX causes lost Class Module reference

[Jason Webley]

Hello All,

I have an excel spreadsheet which is driven by a CommandBar. This CommandBar
is a class module, and referenced by a public variable in my main module.
Each line in the Spreadsheet has an ActiveX control, a checkbox from the
Control Toolbox.
Using the CommandBar, the user will select products to place on the
spreadsheet. The code will automatically do its work with the products (get
data from db), remove the checkboxes, then re-add them (the number of
products selected may differ).
As soon as I have worked with the checkboxes, the CommandBar will not
respond to any clicks. It is like the reference to the CommandBar has been
lost.
Prior to removing/adding the checkboxes, i unprotect the sheet then protect
it again after all the work is done.

Is this a known or common problem, or just dodgy code on my behalf?

The code which does this checkbox work is as follows:

Dim oCheck As OLEObject
Dim currRow As Integer
Dim rCell As Range

On Error Resume Next

currRow = 4

'First clear any existing checkboxes
For Each oCheck In shSMI.OLEObjects
If TypeName(oCheck.Object) = "CheckBox" Then
oCheck.Delete
End If
Next

'add new checkboxes
With shSMI
For Each rCell In .Columns(1).Cells
If (rCell.Row > .Rows(3).Row) Then
If (rCell.Offset(0, 1) <> "") Then
rCell.RowHeight = 14 'this makes the checkbox look nicer
With shSMI.OLEObjects.Add(classtype:="Forms.Checkbox.1", _
Top:=rCell.Top, Left:=(rCell.Width / 2 - 2), _
Height:=rCell.Height, Width:=(rCell.Width / 2))
.Locked = False
.Object.Caption = ""
.LinkedCell = rCell.Address(False, False)
'.Object.Value = False
End With
Else
Exit For
End If
End If
Next rCell
End With

If (Err <> 0) Then
MsgBox Err.Description
End If

Set oCheck = Nothing
Err.Clear

Basically, once the above code has been run once, the CommandBar stops
responding.

Any help appreciated...Regards

Jason

 

[Jason Webley]

Sorry for updating but I have searched the web for hours and have found no
help.
Upon further investigation, what I have found is that if I open my
spreadsheet, enable the macros, enter design mode, then exit design mode,
the command bar will not work. My commandbar variable is then of type
nothing. Is this avoidable?

Regards

 

[Peter T]

Hi Jason,

I see you are adding new ActiveX controls and presumably a new instance of
class to handle its event code. If adding to the active sheet big problems -
likely to recompile your project clearing all public variables including any
ref's to your class. See this messages #10-20 in the this thread, in
particular Stephen Bullen's comments:

http://tinyurl.com/c9s9u

If you are adding new controls NOT to the active sheet it might work!

Could you work adding controls from the Forms menu instead of ActiveX's.

Regards,
Peter T