Hey all,
I am struggling to understand why some of my macros are running when I am not intending for them to run. Here's some examples of situations happening in this workbook.
1. Making a change to any cell on any sheet in the workbook run's two of my macros (these macros are written in two separate combobox_click events)
2. Running some of the macros in a module runs some of my combobox_click macros
3. Running some of my combobox_click macros also triggers other combobox_click macros
I have used msgbox to tell me what macros are triggering other macros, it goes as follows;
Sub add_Labour() Runs the following (not intended): ComboBoxSpec_click(), ComboBoxSupplier_click()
Sub calc_Total() Runs the following (not intended): ComboBoxSpec_click(), ComboBoxSupplier_click()
Sub clear_Data() Runs the following (not intended): ComboBoxSpec_click() - then hits an error because a variable isnt set - it shouldnt be so this is fine
Private Sub ComboBoxSupplier_Click() Runs the following (not intended): ComboBoxSpec_click()
Private Sub ComboBoxSpec_Click() Runs the following (not intended): ComboBoxSupplier_click()
Making any change to the worksheet runs the following (unintended): ComboBoxSpec_click(), ComboBoxSupplier_click()
You can probably see the pattern of which macros are always running, but for the life of me i don't know why???
Heres my code:
The following is in a module;
I am struggling to understand why some of my macros are running when I am not intending for them to run. Here's some examples of situations happening in this workbook.
1. Making a change to any cell on any sheet in the workbook run's two of my macros (these macros are written in two separate combobox_click events)
2. Running some of the macros in a module runs some of my combobox_click macros
3. Running some of my combobox_click macros also triggers other combobox_click macros
I have used msgbox to tell me what macros are triggering other macros, it goes as follows;
Sub add_Labour() Runs the following (not intended): ComboBoxSpec_click(), ComboBoxSupplier_click()
Sub calc_Total() Runs the following (not intended): ComboBoxSpec_click(), ComboBoxSupplier_click()
Sub clear_Data() Runs the following (not intended): ComboBoxSpec_click() - then hits an error because a variable isnt set - it shouldnt be so this is fine
Private Sub ComboBoxSupplier_Click() Runs the following (not intended): ComboBoxSpec_click()
Private Sub ComboBoxSpec_Click() Runs the following (not intended): ComboBoxSupplier_click()
Making any change to the worksheet runs the following (unintended): ComboBoxSpec_click(), ComboBoxSupplier_click()
You can probably see the pattern of which macros are always running, but for the life of me i don't know why???
Heres my code:
Code:
Private Sub ComboBoxSheet_Click()
MsgBox "combobox SHEET"
'update sheet combobox on click
Dim x As Integer
'set x to combo box option
x = ComboBoxSheet.ListIndex
'do update
Select Case x
Case Is = 0
'user selects Builders Merchants
'populate materials combo box
ComboBoxMaterial.ListFillRange = "BuildersMerchants"
Case Is = 1
'user selects Rebar Accessories
'populate materials combo box
ComboBoxMaterial.ListFillRange = "RebarAccessories"
Case Is = 2
'user selects Aco Channel
'populate materials combo box
ComboBoxMaterial.ListFillRange = "AcoChannel"
Case Is = 3
'user selects Drainage
'populate materials combo box
ComboBoxMaterial.ListFillRange = "Drainage"
Case Is = 4
'user selects Timber
'populate materials combo box
ComboBoxMaterial.ListFillRange = "Timber"
Case Is = 5
'user selects CPM Direct
'populate materials combo box
ComboBoxMaterial.ListFillRange = "cpmDirect"
End Select
End Sub
Private Sub ComboBoxMaterial_Click()
MsgBox "combobox MATERIAL"
'update material combobox on click
Dim x As Integer
'set x to combo box option
x = ComboBoxMaterial.ListIndex
'set options for Builders Merchants Materials
If ComboBoxSheet.Value = "Builders Merchants" Then
'do update
Select Case x
Case Is = 0
ComboBoxSpec.ListFillRange = "Gulley"
Case Is = 1
ComboBoxSpec.ListFillRange = "SeatingRing"
Case Is = 2
ComboBoxSpec.ListFillRange = "Manhole900mm"
Case Is = 3
ComboBoxSpec.ListFillRange = "Manhole1050mm"
Case Is = 4
ComboBoxSpec.ListFillRange = "Manhole1200mm"
Case Is = 5
ComboBoxSpec.ListFillRange = "Manhole1350mm"
Case Is = 6
ComboBoxSpec.ListFillRange = "Manhole1500mm"
Case Is = 7
ComboBoxSpec.ListFillRange = "Manhole1800mm"
Case Is = 8
ComboBoxSpec.ListFillRange = "HicSections"
Case Is = 9
ComboBoxSpec.ListFillRange = "PlasticYardGully"
Case Is = 10
ComboBoxSpec.ListFillRange = "FlexsealCouplings"
Case Is = 11
ComboBoxSpec.ListFillRange = "ClayToPlastic"
Case Is = 12
ComboBoxSpec.ListFillRange = "ManholeCovers"
Case Is = 13
ComboBoxSpec.ListFillRange = "RecessedCovers"
Case Is = 14
ComboBoxSpec.ListFillRange = "Bricks"
Case Is = 15
ComboBoxSpec.ListFillRange = "Kerbs"
Case Is = 16
ComboBoxSpec.ListFillRange = "ConservationKerb"
Case Is = 17
ComboBoxSpec.ListFillRange = "CountrysideKerb"
Case Is = 18
ComboBoxSpec.ListFillRange = "KerbRepair"
Case Is = 19
ComboBoxSpec.ListFillRange = "ChannelKerbs"
Case Is = 20
ComboBoxSpec.ListFillRange = "Slabs"
Case Is = 21
ComboBoxSpec.ListFillRange = "BlockPaving"
Case Is = 22
ComboBoxSpec.ListFillRange = "GraniteSetts"
Case Is = 23
ComboBoxSpec.ListFillRange = "TactileSlabs"
Case Is = 24
ComboBoxSpec.ListFillRange = "SaxonSlabs"
Case Is = 25
ComboBoxSpec.ListFillRange = "Geotextile"
Case Is = 26
ComboBoxSpec.ListFillRange = "MDPE"
Case Is = 27
ComboBoxSpec.ListFillRange = "WarningTape"
Case Is = 28
ComboBoxSpec.ListFillRange = "BitumenPaint"
Case Is = 29
ComboBoxSpec.ListFillRange = "DrawCord"
Case Is = 30
ComboBoxSpec.ListFillRange = "BaggedAggregates"
Case Is = 31
ComboBoxSpec.ListFillRange = "Blocks"
Case Is = 32
ComboBoxSpec.ListFillRange = "CoursingBricks"
Case Is = 33
ComboBoxSpec.ListFillRange = "DPC"
Case Is = 34
ComboBoxSpec.ListFillRange = "Lintels"
Case Is = 35
ComboBoxSpec.ListFillRange = "Febmix"
Case Is = 36
ComboBoxSpec.ListFillRange = "SikaWaterproofing"
Case Is = 37
ComboBoxSpec.ListFillRange = "AirBrick"
Case Is = 38
ComboBoxSpec.ListFillRange = "TelescopicVents"
Case Is = 39
ComboBoxSpec.ListFillRange = "Polythene"
Case Is = 40
ComboBoxSpec.ListFillRange = "DuctBoxes"
Case Is = 41
ComboBoxSpec.ListFillRange = "StakkaBoxes"
Case Is = 42
ComboBoxSpec.ListFillRange = "Duct"
Case Is = 43
ComboBoxSpec.ListFillRange = "Twinwall"
Case Is = 44
ComboBoxSpec.ListFillRange = "PolystormUnits"
Case Is = 45
ComboBoxSpec.ListFillRange = "DrainageChannel"
Case Is = 46
ComboBoxSpec.ListFillRange = "Lubricant"
Case Is = 47
ComboBoxSpec.ListFillRange = "Claymaster"
Case Is = 48
ComboBoxSpec.ListFillRange = "Polystrene"
Case Is = 49
ComboBoxSpec.ListFillRange = "Mesh"
Case Is = 50
ComboBoxSpec.ListFillRange = "StockSteel"
Case Is = 51
ComboBoxSpec.ListFillRange = "WoodenPegs"
Case Is = 52
ComboBoxSpec.ListFillRange = "Shimpacks"
Case Is = 53
ComboBoxSpec.ListFillRange = "OrangeBarrierFencing"
Case Is = 54
ComboBoxSpec.ListFillRange = "FencingPins"
End Select
End If
'set options for Rebar Accessories Materials
If ComboBoxSheet.Value = "Rebar Accessories" Then
'do update
Select Case x
Case Is = 0
ComboBoxSpec.ListFillRange = "DoubleSpacers"
Case Is = 1
ComboBoxSpec.ListFillRange = "Polythene2"
Case Is = 2
ComboBoxSpec.ListFillRange = "GaffaTape"
Case Is = 3
ComboBoxSpec.ListFillRange = "ConcreteMarsBars"
Case Is = 4
ComboBoxSpec.ListFillRange = "TyingWire"
Case Is = 5
ComboBoxSpec.ListFillRange = "WireChairs"
Case Is = 6
ComboBoxSpec.ListFillRange = "TricTrakSpacers"
Case Is = 7
ComboBoxSpec.ListFillRange = "GradePlateSpacers"
Case Is = 8
ComboBoxSpec.ListFillRange = "Geotextile"
Case Is = 9
ComboBoxSpec.ListFillRange = "Fibreboard"
Case Is = 10
ComboBoxSpec.ListFillRange = "JointFiller"
Case Is = 11
ComboBoxSpec.ListFillRange = "WaxedCones"
Case Is = 12
ComboBoxSpec.ListFillRange = "Grout"
Case Is = 13
ComboBoxSpec.ListFillRange = "Beamform"
Case Is = 14
ComboBoxSpec.ListFillRange = "AngleFillet"
Case Is = 15
ComboBoxSpec.ListFillRange = "BitumenPaint"
Case Is = 16
ComboBoxSpec.ListFillRange = "WirleyTube"
Case Is = 17
ComboBoxSpec.ListFillRange = "WirleyCones"
Case Is = 18
ComboBoxSpec.ListFillRange = "WirleyStoppers"
Case Is = 19
ComboBoxSpec.ListFillRange = "RebarProtectionCaps"
Case Is = 20
ComboBoxSpec.ListFillRange = "SprayPack"
Case Is = 21
ComboBoxSpec.ListFillRange = "Sealent"
Case Is = 22
ComboBoxSpec.ListFillRange = "Foam"
Case Is = 23
ComboBoxSpec.ListFillRange = "Polystrene2"
Case Is = 24
ComboBoxSpec.ListFillRange = "MbtCoupler"
Case Is = 25
ComboBoxSpec.ListFillRange = "Nails"
Case Is = 26
ComboBoxSpec.ListFillRange = "ForstBlanket"
Case Is = 27
ComboBoxSpec.ListFillRange = "Sika"
Case Is = 28
ComboBoxSpec.ListFillRange = "Grace"
End Select
End If
'set options for Aco Channel Materials
If ComboBoxSheet.Value = "Aco Channel" Then
'do update
Select Case x
Case Is = 0
ComboBoxSpec.ListFillRange = "M100DACO"
Case Is = 1
ComboBoxSpec.ListFillRange = "S100ACO"
End Select
End If
'set options for Drainage Materials
If ComboBoxSheet.Value = "Drainage" Then
'do update
Select Case x
Case Is = 0
ComboBoxSpec.ListFillRange = "110mmSewer"
Case Is = 1
ComboBoxSpec.ListFillRange = "Lubricant"
Case Is = 2
ComboBoxSpec.ListFillRange = "PPIC"
Case Is = 3
ComboBoxSpec.ListFillRange = "Flexseal"
Case Is = 4
ComboBoxSpec.ListFillRange = "Gullies"
Case Is = 5
ComboBoxSpec.ListFillRange = "160mmSewer"
Case Is = 6
ComboBoxSpec.ListFillRange = "250mmSewer"
Case Is = 7
ComboBoxSpec.ListFillRange = "ManholeCovers2"
Case Is = 8
ComboBoxSpec.ListFillRange = "Flexiduct"
Case Is = 9
ComboBoxSpec.ListFillRange = "GPDuct"
Case Is = 10
ComboBoxSpec.ListFillRange = "Twinwall"
Case Is = 11
ComboBoxSpec.ListFillRange = "DrawCord"
Case Is = 12
ComboBoxSpec.ListFillRange = "WarningTape"
Case Is = 13
ComboBoxSpec.ListFillRange = "Geotextile"
Case Is = 14
ComboBoxSpec.ListFillRange = "NonReturnValve"
Case Is = 15
ComboBoxSpec.ListFillRange = "DuctBoxes"
Case Is = 16
ComboBoxSpec.ListFillRange = "Landrain"
Case Is = 17
ComboBoxSpec.ListFillRange = "MDPE"
Case Is = 18
ComboBoxSpec.ListFillRange = "BarrierPipe"
Case Is = 19
ComboBoxSpec.ListFillRange = "RainwaterAdaptor"
Case Is = 20
ComboBoxSpec.ListFillRange = "PipeStopper"
Case Is = 21
ComboBoxSpec.ListFillRange = "ElectricalDuct"
Case Is = 22
ComboBoxSpec.ListFillRange = "FloorVent"
Case Is = 23
ComboBoxSpec.ListFillRange = "AirBricks"
Case Is = 24
ComboBoxSpec.ListFillRange = "BTDuct"
Case Is = 25
ComboBoxSpec.ListFillRange = "CableDuct"
Case Is = 26
ComboBoxSpec.ListFillRange = "BTBoxes"
Case Is = 27
ComboBoxSpec.ListFillRange = "RidgiGullies"
Case Is = 28
ComboBoxSpec.ListFillRange = "PipeInsulation"
End Select
End If
'set options for Timber Materials
If ComboBoxSheet.Value = "Timber" Then
'do update
Select Case x
Case Is = 0
ComboBoxSpec.ListFillRange = "fourtwo"
Case Is = 1
ComboBoxSpec.ListFillRange = "fourthree"
Case Is = 2
ComboBoxSpec.ListFillRange = "sixthree"
Case Is = 3
ComboBoxSpec.ListFillRange = "sixone"
Case Is = 4
ComboBoxSpec.ListFillRange = "sixtwo"
Case Is = 5
ComboBoxSpec.ListFillRange = "twoone"
Case Is = 6
ComboBoxSpec.ListFillRange = "Ply"
Case Is = 7
ComboBoxSpec.ListFillRange = "Pegs"
End Select
End If
'set options for CPM Direct Materials
If ComboBoxSheet.Value = "CPM Direct" Then
'do update
Select Case x
Case Is = 0
ComboBoxSpec.ListFillRange = "ChamberRings"
Case Is = 1
ComboBoxSpec.ListFillRange = "SurchargeForChamberRings"
Case Is = 2
ComboBoxSpec.ListFillRange = "DoubleSteps"
Case Is = 3
ComboBoxSpec.ListFillRange = "Perfs"
Case Is = 4
ComboBoxSpec.ListFillRange = "CoverSlabs"
Case Is = 5
ComboBoxSpec.ListFillRange = "ConcretePipes"
End Select
End If
End Sub
Private Sub ComboBoxSpec_Click()
MsgBox "combobox SPEC"
'update spec combobox on click
Dim SpecVal As String
Dim fRow As Integer
Dim lastRow As Integer
SpecVal = ComboBoxSpec.Value
Dim SuppRng As Range
'select range to search
If ComboBoxSheet.Value = "Builders Merchants" Then
'set dynamic last row
lastRow = BuildersMerchants.Range("B" & Rows.Count).End(xlUp).row
'search builders merchants
fRow = Application.WorksheetFunction.Match(SpecVal, BuildersMerchants.Range("B1:B" & lastRow), 0)
'copy suppliers to table
BuildersMerchants.Range("D2:I2").Copy
Calc.Range("H9:M9").PasteSpecial xlPasteValues
'copy costs to table
BuildersMerchants.Range("D" & fRow & ":I" & fRow).Copy
Calc.Range("H10:M10").PasteSpecial xlPasteValues
Calc.Range("H10:M10").NumberFormat = "[$£-809]#,##0.00"
'set supplier combo box listfillrange
Set SuppRng = Calc.Range("H9:M9")
ComboBoxSupplier.ListFillRange = ""
ComboBoxSupplier.Column = SuppRng.Value
'get unit
BuildersMerchants.Range("C" & fRow).Copy
Calc.Range("F12").PasteSpecial xlPasteValues
ElseIf ComboBoxSheet.Value = "Rebar Accessories" Then
'set dynamic last row
lastRow = RebarAccessories.Range("B" & Rows.Count).End(xlUp).row
'search rebar accessories
fRow = Application.WorksheetFunction.Match(SpecVal, RebarAccessories.Range("B1:B" & lastRow), 0)
'copy suppliers to table
RebarAccessories.Range("D2:I2").Copy
Calc.Range("H9:M9").PasteSpecial xlPasteValues
'copy costs to table
RebarAccessories.Range("D" & fRow & ":I" & fRow).Copy
Calc.Range("H10:M10").PasteSpecial xlPasteValues
Calc.Range("H10:M10").NumberFormat = "[$£-809]#,##0.00"
'set supplier combo box listfillrange
Set SuppRng = Calc.Range("H9:M9")
ComboBoxSupplier.ListFillRange = ""
ComboBoxSupplier.Column = SuppRng.Value
'get unit
RebarAccessories.Range("C" & fRow).Copy
Calc.Range("F12").PasteSpecial xlPasteValues
ElseIf ComboBoxSheet.Value = "Aco Channel" Then
'set dynamic last row
lastRow = AcoChannel.Range("B" & Rows.Count).End(xlUp).row
'search Aco Channel
fRow = Application.WorksheetFunction.Match(SpecVal, AcoChannel.Range("B1:B" & lastRow), 0)
'copy suppliers to table
AcoChannel.Range("D2:I2").Copy
Calc.Range("H9:M9").PasteSpecial xlPasteValues
'copy costs to table
AcoChannel.Range("D" & fRow & ":I" & fRow).Copy
Calc.Range("H10:M10").PasteSpecial xlPasteValues
Calc.Range("H10:M10").NumberFormat = "[$£-809]#,##0.00"
'set supplier combo box listfillrange
Set SuppRng = Calc.Range("H9:M9")
ComboBoxSupplier.ListFillRange = ""
ComboBoxSupplier.Column = SuppRng.Value
'get unit
AcoChannel.Range("C" & fRow).Copy
Calc.Range("F12").PasteSpecial xlPasteValues
ElseIf ComboBoxSheet.Value = "Drainage" Then
'set dynamic last row
lastRow = Drainage.Range("B" & Rows.Count).End(xlUp).row
'search Drainage
fRow = Application.WorksheetFunction.Match(SpecVal, Drainage.Range("B1:B" & lastRow), 0)
'copy suppliers to table
Drainage.Range("D2:I2").Copy
Calc.Range("H9:M9").PasteSpecial xlPasteValues
'copy costs to table
Drainage.Range("D" & fRow & ":I" & fRow).Copy
Calc.Range("H10:M10").PasteSpecial xlPasteValues
Calc.Range("H10:M10").NumberFormat = "[$£-809]#,##0.00"
'set supplier combo box listfillrange
Set SuppRng = Calc.Range("H9:M9")
ComboBoxSupplier.ListFillRange = ""
ComboBoxSupplier.Column = SuppRng.Value
'get unit
Drainage.Range("C" & fRow).Copy
Calc.Range("F12").PasteSpecial xlPasteValues
ElseIf ComboBoxSheet.Value = "Timber" Then
'set dynamic last row
lastRow = Timber.Range("B" & Rows.Count).End(xlUp).row
'search Timber
fRow = Application.WorksheetFunction.Match(SpecVal, Timber.Range("B1:B" & lastRow), 0)
'copy suppliers to table
Timber.Range("D2:I2").Copy
Calc.Range("H9:M9").PasteSpecial xlPasteValues
'copy costs to table
Timber.Range("D" & fRow & ":I" & fRow).Copy
Calc.Range("H10:M10").PasteSpecial xlPasteValues
Calc.Range("H10:M10").NumberFormat = "[$£-809]#,##0.00"
'set supplier combo box listfillrange
Set SuppRng = Calc.Range("H9:M9")
ComboBoxSupplier.ListFillRange = ""
ComboBoxSupplier.Column = SuppRng.Value
'get unit
Timber.Range("C" & fRow).Copy
Calc.Range("F12").PasteSpecial xlPasteValues
ElseIf ComboBoxSheet.Value = "CPM Direct" Then
'set dynamic last row
lastRow = cpmDirect.Range("B" & Rows.Count).End(xlUp).row
'search cpm Direct
fRow = Application.WorksheetFunction.Match(SpecVal, cpmDirect.Range("B1:B" & lastRow), 0)
'copy suppliers to table
cpmDirect.Range("D2:I2").Copy
Calc.Range("H9:M9").PasteSpecial xlPasteValues
'copy costs to table
cpmDirect.Range("D" & fRow & ":I" & fRow).Copy
Calc.Range("H10:M10").PasteSpecial xlPasteValues
Calc.Range("H10:M10").NumberFormat = "[$£-809]#,##0.00"
'set supplier combo box listfillrange
Set SuppRng = Calc.Range("H9:M9")
ComboBoxSupplier.ListFillRange = ""
ComboBoxSupplier.Column = SuppRng.Value
'get unit
cpmDirect.Range("C" & fRow).Copy
Calc.Range("F12").PasteSpecial xlPasteValues
End If
End Sub
Private Sub ComboBoxSupplier_Click()
'set rate on supplier selection
MsgBox "combobox SUPPLIER"
Dim x As Integer
'set x to combo box option
x = ComboBoxSupplier.ListIndex
Select Case x
Case Is = 0
'set cell F13 (rate) to correct rate
Calc.Range("F13") = Calc.Range("H11")
Case Is = 1
'set cell F13 (rate) to correct rate
Calc.Range("F13") = Calc.Range("I11")
Case Is = 2
'set cell F13 (rate) to correct rate
Calc.Range("F13") = Calc.Range("J11")
Case Is = 3
'set cell F13 (rate) to correct rate
Calc.Range("F13") = Calc.Range("K11")
Case Is = 4
'set cell F13 (rate) to correct rate
Calc.Range("F13") = Calc.Range("L11")
Case Is = 5
'set cell F13 (rate) to correct rate
Calc.Range("F13") = Calc.Range("M11")
End Select
End SubThe following is in a module;
Code:
Sub add_labour()
' Adds labour line entry to variance order
MsgBox "sub ADD LABOUR"
'declare variables
Dim lastRow As Integer
Dim newRow As Integer
'find last row
lastRow = Calc.Range("C" & Rows.Count).End(xlUp).row
'set new row
newRow = lastRow + 1
'add line entry
'insert item (trade)
Calc.Range("C" & newRow) = Calc.Range("C9")
'insert quantity (hours)
Calc.Range("D" & newRow) = Calc.Range("C11")
'insert unit (hours)
Calc.Range("E" & newRow) = "Hours"
'insert rate (pounds sterling)
Calc.Range("F" & newRow) = Calc.Range("C12")
Calc.Range("F" & newRow).NumberFormat = "[$£-809]#,##0.00"
'insert total (pounds sterling)
Calc.Range("G" & newRow) = Calc.Range("C13")
Calc.Range("G" & newRow).NumberFormat = "[$£-809]#,##0.00"
End Sub
Sub add_materials()
'Adds material line entry to variance order
End Sub
Sub calc_total()
'calculates grand total for line items in details list
MsgBox "sub CALC TOTAL"
'declare variables
Dim firstRow As Integer
Dim lastRow As Integer
Dim gtRow As Integer
Dim sumRng As Range
'check for list items
If Range("C29") = "" Then
MsgBox "No data present to perform calculation.", vbInformation, "Information"
Exit Sub
End If
'set firstRow (line before first entry)
firstRow = 28
'find last row
lastRow = Calc.Range("C" & Rows.Count).End(xlUp).row
'set rowCount
rowCount = lastRow - firstRow
'set range for SUM
Set sumRng = Calc.Range("G29:G" & lastRow)
'set row location for Grand Total to be placed
gtRow = lastRow + 2
'Calculate grand total and create label
Calc.Range("F" & gtRow) = "Grand Total"
Calc.Range("G" & gtRow).Formula = "=Sum(" & sumRng.Address & ")"
Calc.Range("G" & gtRow).NumberFormat = "[$£-809]#,##0.00"
End Sub
Sub clear_data()
'clears line entries from details list
MsgBox "sub CLEAR DATA"
'declare variables
Dim lastRow As Integer
Dim answer As Integer
'check for list items
If Calc.Range("C29") = "" Then
'stop runnning code
Exit Sub
End If
'find last row (grand total row)
lastRow = Calc.Range("G" & Rows.Count).End(xlUp).row
'check proceed
answer = MsgBox("Are you sure you want to clear the data?", vbYesNo + vbQuestion, "Clear Data")
If answer = vbYes Then
Calc.Range("B29:G" & lastRow).ClearContents
End If
End Sub
