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 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):
And my Module 1 Code which calls the Class Module is:
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:
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.
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):
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
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
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