Class Module Problem when Checkboxes Created on the Fly

[ErikaAdams]

I've got a worksheet where a user can estimate equipment costs. There are initially 30 blank rows where the user can describe each part, with its internal cost and retail price. I have created checkboxes next to retail price such that the person can either charge retail (list) price or markup our cost however they want. If "Charge List" is checked, then Margin (column G) is calulated based on cost and price. If "Charge List" is not checked, then the user can adjust margin (column G) however they want.

When the "Charge List" checkbox is clicked, the cell in column G of that same row is locked and shaded (if checked) or unlocked and highlighted (if unchecked). I have a class module that takes care of this code for all my checkboxes. The problem is if a user needs to add an item. I have a command button, "Add A Part" that inserts a new row at the end of my table and new checkbox (linked to the appropriate cell). But my "Class" doesn't seem to update. Is the problem with the Autpen module? Everything works great so long as no new check boxes are created, however I need that capability.

Here's my Class Module Code (as Class1):

Option Explicit
Public WithEvents grpCBX As MSForms.CheckBox
Dim X As Integer

Private Sub grpCBX_Change()

unprotectmacro
ActiveSheet.Shapes(grpCBX.Name).Select

X = Right(grpCBX.Name, Len(grpCBX.Name) - 8)

If ActiveSheet.Cells(X + 9, 7).Value = "True" Then
  Cells(X + 9, 9).Select
  With Selection
    .Interior.ColorIndex = 15
    .Locked = True
    .Formula = "=(R[]C[-3]-r[]c[-1])/r[]c[-3]"
  End With
Else
 Cells(X + 9, 9).Select
  With Selection
  .Interior.ColorIndex = 19
  .Locked = False
 End With
End If
protectionmacro
End Sub

And my Module 1 Code which calls the Class Module is:

Dim cbxHandler() As New Class1
Sub Auto_Open()
Dim cbxQuantity As Integer, myCBX As Object
cbxQuantity = 0

With ThisWorkbook

For Each myCBX In .Worksheets("Purchased Items").OLEObjects
If TypeName(myCBX.Object) = "CheckBox" Then
cbxQuantity = cbxQuantity + 1
ReDim Preserve cbxHandler(1 To cbxQuantity)
Set cbxHandler(cbxQuantity).grpCBX = myCBX.Object
End If
Next myCBX

End With
End Sub

And this is my code for the command button that Adds a New Part (Item). The code below is located on the worksheet object itself, not in a separate Module:

Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
    unprotectmacro
    Range("z10").Select
    ActiveCell.End(xlDown).EntireRow.Select
    Selection.Copy
    ActiveCell.Offset(1, 0).Select
    Selection.Insert Shift:=xlDown
    Application.CutCopyMode = False
    'create a new parts checkbox
    ActiveSheet.Shapes("checkbox1").Select
     Selection.Copy
     ActiveCell.Offset(0, 6).Select
     ActiveSheet.Paste
    i = ActiveCell.Row - 9
    ActiveSheet.Shapes("checkbox" & i).Select
    With Selection
          .LinkedCell = ActiveCell.Address
    End With
    ActiveCell.Offset(0, -6).Select
    protectionmacro
    'need to update class1

End Sub

Why does everything break down with the Command Button code above? Before I click the button, cells unlock and lock as required. When I click it, I have my new checkbox, linked to the right cell and everything. But the Class Module code no longer works. Any ideas? Thanks for the help.

 

[Norie]

Erika

As far as I can see you aren't doing anything with the class when you add a new checkbox.

Also what do you mean by "everything break down"? Errors?

 

[ErikaAdams]

When I add an item (i.e. new row is inserted with new checkbox), none of my checkboxes lock or unlock the cell in Column G anymore. It's as though the class module doesn't run.

 

[Norie]

Erika

Are you sure using this method is the best approach?

By the way Autpen will only run when the workbook opens and has actually been superseded by the workbook Open event.

And this comment is correct.

'need to update class1

To use this method you will need to update, but not Class1 - as far as I can see you need to update cbxHandler.

 

[ErikaAdams]

So how do I accomplish this, updating cbxhandler? A ReDim statement? Should I change the Autpen procedure? Thanks for the help.

 

[ErikaAdams]

I added this code to my CommandButton1_Click Event:

Dim cbxQuantity As Integer, myCBX As Object
cbxQuantity = 0
For Each myCBX In ActiveWorkbook.Worksheets("Purchased Items").OLEObjects
If TypeName(myCBX.Object) = "CheckBox" Then
cbxQuantity = cbxQuantity + 1
ReDim Preserve cbxHandler(1 To cbxQuantity)
Set cbxHandler(cbxQuantity).grpCBX = myCBX.Object
End If
Next myCBX

I did this in the hopes it would fix my problem. Should I do a Class Initialize Event or is there some way, when you create a new checkbox to make sure it's part of the Class?

 

[Norie]

Erika

Do you really need/want the class?

It seems to me to be causing more trouble than it's worth.

Note I'm not saying you shouldn't use the method but perhaps you should consider a different approach.

 

[ErikaAdams]

I'm not married to Class Modules by any stretch and in fact, am totally new to them. The only reason I was using them is because I needed a way to run the same macro for each checkbox (no matter how many are created). And I really need to use the ActiveX controls from the control toolbox because of checkbox font and size restrictions.