Class Module Problem when Checkboxes Created on the Fly

ErikaAdams

New Member
Joined
Oct 31, 2006
Messages
13
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 Auto_Open 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):
Code:
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:
Code:
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:
Code:
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.
 

Some videos you may like

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 31, 2006
Messages
13
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
Erika

Are you sure using this method is the best approach?

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

And this comment is correct.:)
Code:
'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

New Member
Joined
Oct 31, 2006
Messages
13

ADVERTISEMENT

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

ErikaAdams

New Member
Joined
Oct 31, 2006
Messages
13
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,230
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Oct 31, 2006
Messages
13
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,258
Messages
5,600,567
Members
414,389
Latest member
MarkElla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top