Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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
Posted to microsoft.public.excel.programming
|
|||
|
|||
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" wrote in message ... 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" wrote in message ... 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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
CLASS MODULE & SIMPLE MODULE | Excel Discussion (Misc queries) | |||
Class Module | Excel Programming | |||
How can I name the Module or class after I insert it into the Excel | Excel Programming | |||
Exposing Class Module from VBA Excel 97 SR-2 | Excel Programming | |||
Variable from a sheet module in a class module in XL XP | Excel Programming |