+ Reply to Thread
Results 1 to 3 of 3

Excel ActiveX causes lost Class Module reference

  1. #1
    Jason Webley
    Guest

    Excel ActiveX causes lost Class Module reference

    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



  2. #2
    Jason Webley
    Guest

    Re: Excel ActiveX causes lost Class Module reference

    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

    "Jason Webley" <hardaway10@iprimus.com.au> wrote in message
    news:uGidjFAYFHA.3864@TK2MSFTNGP10.phx.gbl...
    > 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
    >




  3. #3
    Peter T
    Guest

    Re: Excel ActiveX causes lost Class Module reference

    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

    "Jason Webley" <hardaway10@iprimus.com.au> wrote in message
    news:O$9YNQLZFHA.2288@TK2MSFTNGP14.phx.gbl...
    > 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
    >
    > "Jason Webley" <hardaway10@iprimus.com.au> wrote in message
    > news:uGidjFAYFHA.3864@TK2MSFTNGP10.phx.gbl...
    > > 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
    > >

    >
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1