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?

Any help appreciated...Regards

Jason

 

[Cindy M -WordMVP-]

Hi Jason,

I've never done this with Excel, but I imagine it's very similar to Word...

If you've been working with ActiveX controls, you're very likely still in
"Design Mode". You have to deactivate that mode before your commandbars will
work, I believe. In Word, we have the ToggleFormsDesign method.

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?

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail

 

[Jason Webley]

Hi Cindy

Thanks for you help, but as far as I could work it out, this didn't make a
difference.

To explain the problem further:
When I open the workbook (code gets run but no CheckBox work is done), the
CommandBar works fine. But as soon as the user clicks a button on the
CommandBar that involves working with the CheckBoxes, the CommandBar will no
longer respond after that. If I comment out all the CheckBox work, then it
works fine.
Following is the code with deals with CheckBoxes:


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

Thanks again

Jason

 

[Cindy M -WordMVP-]

Hi Jason,

To explain the problem further:
When I open the workbook (code gets run but no CheckBox work is done), the
CommandBar works fine. But as soon as the user clicks a button on the
CommandBar that involves working with the CheckBoxes, the CommandBar will no
longer respond after that. If I comment out all the CheckBox work, then it
works fine.
Following is the code with deals with CheckBoxes:

Right, this looks very much like the code one would use in Word to automate an
embedded OLE object (such as an Excel sheet). In Word, I'd need to return focus
to the document environment, which sometimes isn't a simple thing to
accomplish. The user would click in the document or press ESC. If I'm
automating MS Graph I can use the .Quit method on the application; Excel I have
to open in its own window so that I can exit it properly.

I have trouble imagining that something like that would be necessary with these
objects in Excel, but I suspect the Excel experts in excel.programming will
know the trick. Try asking there

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question or reply
in the newsgroup and not by e-mail