Excel not calculating after Macro Run when Macro is called with a picture/button. But works when run in the vba

[snowstars695]

[FONT=&quot]Hi all, I've a macro which ends off with "Application.Calculation = xlCalculationAutomatic"[/FONT]
[FONT=&quot]When i run the sub from the code editor(F5), there is no issues with calculations.[/FONT]
[FONT=&quot]However, when i call the macro with a button on excel, it stops calculating (even though the calculation option is at Automatic.[/FONT]
[FONT=&quot]Any experience with this issue and ideas on how to fix it?[/FONT]
[FONT=&quot]Thanks![/FONT]

 

[bobsan42]

What is the button type? Is there any other code behind the button?
Can you post the code for the macro and for the button?

 

[snowstars695]

Sub click_reset()


Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual


Range("flag_stop") = 1


Range("RNG_reset").ClearContents
Range("RNG_reset_SI").ClearContents


Range("Biz_desc").formula = "=IF(SSIC_Biz="""","""",RIGHT(SSIC_Biz,LEN(SSIC_Biz)-7))"
Range("Asso_companies").formula = "=IF(Client="""","""",Client)"


'renewal download
ThisWorkbook.Worksheets("Renewal Claims").Cells.Clear
ThisWorkbook.Worksheets("Import").Cells.Clear


'NB/RE
Range("RNG_RENB_1") = "NB"
Range("RNG_RENB_2") = "NB"
Range("RNG_RENB_3") = "NB"


'POIs
Range("RNG_POI_Start_YY_1") = Year(Date)
Range("RNG_POI_Start_MM_1") = Month(Date) + 2
Range("RNG_POI_Start_DD_1") = 1


Range("RNG_POI_Start_YY_2").FormulaR1C1 = "=R[-1]C"
Range("RNG_POI_Start_MM_2").FormulaR1C1 = "=R[-1]C"
Range("RNG_POI_Start_DD_2").FormulaR1C1 = "=R[-1]C"


Range("RNG_POI_Start_YY_3").FormulaR1C1 = "=R[-1]C"
Range("RNG_POI_Start_MM_3").FormulaR1C1 = "=R[-1]C"
Range("RNG_POI_Start_DD_3").FormulaR1C1 = "=R[-1]C"


Range("RNG_POI_End_YY_1") = Year(Date) + 1
Range("RNG_POI_End_MM_1") = Month(Date) + 1
Range("RNG_POI_End_DD_1") = Day(DateSerial(Year(Date) + 1, Month(Date) + 2, 1) - 1)




Range("RNG_POI_End_YY_2").FormulaR1C1 = "=R[-1]C"
Range("RNG_POI_End_MM_2").FormulaR1C1 = "=R[-1]C"
Range("RNG_POI_End_DD_2").FormulaR1C1 = "=R[-1]C"


Range("RNG_POI_End_YY_3").FormulaR1C1 = "=R[-1]C"
Range("RNG_POI_End_MM_3").FormulaR1C1 = "=R[-1]C"
Range("RNG_POI_End_DD_3").FormulaR1C1 = "=R[-1]C"


Range("AR") = False
Range("RNG_No_Building") = False


'commission
ThisWorkbook.Worksheets("Quotation").Range("Property_Comm") = ThisWorkbook.Worksheets("Assumptions").Range("Default_comm_property").Value
ThisWorkbook.Worksheets("Quotation").Range("WIC_Comm") = ThisWorkbook.Worksheets("Assumptions").Range("Default_comm_WIC").Value
ThisWorkbook.Worksheets("Quotation").Range("PL_Comm") = ThisWorkbook.Worksheets("Assumptions").Range("Default_comm_PL").Value
'RI Coin
ThisWorkbook.Worksheets("Quotation").Range("Property_Coin") = 0
ThisWorkbook.Worksheets("Quotation").Range("WIC_Coin") = 0
ThisWorkbook.Worksheets("Quotation").Range("PL_Coin") = 0


ThisWorkbook.Worksheets("Quotation").Range("Property_RI") = 0
ThisWorkbook.Worksheets("Quotation").Range("WIC_RI") = 0
ThisWorkbook.Worksheets("Quotation").Range("PL_RI") = 0


'set product
Range("RNG_Type_Product") = False
Range("RNG_Cover_Property") = True
Range("RNG_Cover_WIC") = True
Range("RNG_Cover_PL") = True
Range("Commercial") = True
Range("Industrial") = False
Range("FEP") = True
Range("RNG_No_Building") = False


For i = Range("RNG_Add_Cover_BI").Row To Range("RNG_Add_Cover_EE").Row
ThisWorkbook.Worksheets("Quotation").Cells(i, 9).Value = False
ThisWorkbook.Worksheets("Quotation").Cells(i, 22).Value = "Separate Policy"
Next i


ThisWorkbook.Worksheets("Quotation").Range("RNG_Add_Cover_DOS").Value = False
ThisWorkbook.Worksheets("Quotation").Range("RNG_Add_Cover_CPM").Value = False
ThisWorkbook.Worksheets("Quotation").Range("RNG_Add_Cover_EAR").Value = False


ThisWorkbook.Worksheets("Quotation").Range("RNG_Add_Cover_6").Value = "Extension"
ThisWorkbook.Worksheets("Quotation").Range("RNG_Add_Cover_8").Value = "Extension"


'Fire System
For i = 1 To 4
Range("RNG_Fire_NA_" & i).Value = True
Next i


'Security System
For i = 1 To 5
Range("RNG_Sec_NA_" & i).Value = True
Next i


'BI other covers
Range("SI_BI_ICOW_title").Value = Range("A_BI_Cover_1").Value
Range("SI_BI_Rent_title").Value = Range("A_BI_Cover_2").Value
Range("SI_BI_Auditor_title").Value = Range("A_BI_Cover_3").Value
Range("SI_BI_4_title").Value = Range("A_BI_Cover_4").Value
Range("SI_BI_5_title").Value = Range("A_BI_Cover_5").Value
Range("AR_6").Value = ""
Range("AR_7").Value = ""
Range("AR_8").Value = ""
Range("AR_9").Value = ""
Range("AR_10").Value = ""
Range("BUR_others_1").Value = ""
Range("BUR_others_2").Value = ""
Range("BUR_others_3").Value = ""
Range("BUR_others_4").Value = ""
Range("BUR_others_5").Value = ""
Range("BUR_others_6").Value = ""
Range("BUR_others_7").Value = ""
Range("BUR_others_8").Value = ""
Range("BUR_others_9").Value = ""
Range("BUR_others_10").Value = ""


'BI extention button
Range("RNG_ShowHide_BIExt") = "Deleted"
ThisWorkbook.Worksheets("Quotation").BTN_ShowHide_BIExt.Caption = "Add BI Extension"


'WIC Buttons
Range("RNG_WIC_CL_SL_AddDelete") = "Deleted"
Range("RNG_ShowHide_Exclusion_WIC") = "Hide"


Range("RNG_WIC_CONSTRUCTION") = "FALSE"
Range("RNG_WIC_CONSTRUCTION_NO") = "FALSE"


ThisWorkbook.Worksheets("Quotation").BTN_WIC_SL.Caption = "Add Common Law Sub Limit"
ThisWorkbook.Worksheets("Quotation").BTN_Exclusions_WIC.Caption = "Show WIC Exclusions"
ThisWorkbook.Worksheets("Quotation").CB_WIC_Add_Occ.Caption = "Add Occupation"
ThisWorkbook.Worksheets("Quotation").Range("WIC_Occ1").EntireRow.Hidden = False


For i = 1 To 25
ThisWorkbook.Worksheets("Quotation").Cells(Range("WIC_Desc_1").Row - 1 + i, 13).formula = "=if(WIC_Occ" & i & "="""","""",WIC_Occ" & i & ")"
Next i


For i = 1 To 7
Range("WIC_Exclusions_tick_" & i) = True
Next i


For i = 8 To 28
Range("WIC_Exclusions_tick_" & i) = False
Next i


'PL buttons
Range("RNG_PL_SL_AddDelete") = "Deleted"
Range("PL_Terr_1") = True
Range("PL_Terr_4") = False


Range("PL_Jur_1") = True
Range("PL_Jur_2") = False


Range("RNG_ShowHide_Exclusion_PL") = "Hide"


Range("Excess_PL").formula = "=PL_Excess_Default"




ThisWorkbook.Worksheets("Quotation").BTN_PL_SL.Caption = "Add Sub Limit"
ThisWorkbook.Worksheets("Quotation").BTN_Exclusions_PL.Caption = "Show PL Exclusions"


For i = 1 To 9
Range("PL_Exclusions_tick_" & i) = True
Next i


For i = 10 To 29
Range("PL_Exclusions_tick_" & i) = False
Next i


'floating
Range("RNG_SI_Building") = False
Range("RNG_SI_FFF") = False
Range("RNG_SI_Stock") = False
Range("RNG_SI_Content") = False
Range("RNG_SI_Machinery") = False
Range("RNG_SI_Others") = False
Range("RNG_SI_FEP") = False
Range("RNG_SI_BI") = False
Range("RNG_SI_EAR") = False
Range("RNG_SI_BI_ICOW") = False
Range("RNG_SI_BI_Rent") = False
Range("RNG_SI_BI_Auditor") = False
Range("RNG_SI_BI_4") = False
Range("RNG_SI_BI_5") = False


'set proposed premium to recommended premium
'--Property
Range("Prop_FEP").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"
Range("Prop_BI").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"
Range("Prop_Burglary").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"
Range("Prop_PG").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"
Range("Prop_Money").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"
Range("Prop_FG").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"
Range("Prop_MB").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"
Range("Prop_EE").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"
Range("Prop_DOS").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"
Range("Prop_EAR").FormulaR1C1 = "=if(RC[-6]="""","""",RC[-6])"


'--WIC
For i = 1 To 25
Range("WIC_prop_" & i).FormulaR1C1 = "=if(RC[-5]="""","""",RC[-5])"
Next i


'--PL
Range("PL_Prop").FormulaR1C1 = "=if(R[-2]C="""","""",R[-2]C)"


'set WIC common law limit to 10mil
Range("WIC_Agt_Limit").Value = Range("WIC_CL_Default").Value
'set limit per period of PL to unlimited
Range("PL_Limit_Period").Value = Range("PL_Limit_Period_default").Value




'WIC set marine contractor to no
Range("RNG_WIC_Marine_no").Value = True


'clauses
ThisWorkbook.Worksheets("Clauses(standard)").Cells.Copy
ThisWorkbook.Worksheets("Clauses").Cells(1, 1).PasteSpecial xlPasteValues


'Exclusions
For i = 1 To 7
Range("WIC_Exclusions_" & i).Value = Range("WIC_exclusion_standard").Cells(i, 1)
Next i


For i = 1 To 9
Range("PL_Exclusions_" & i).Value = Range("PL_exclusion_standard").Cells(i, 1)
Next i


'user name
Range("UW").Value = Range("User_Name").Value


'clear approval
Range("BT_Hit_Property").Value = ""
Range("BT_Hit_WIC").Value = ""
Range("BT_Hit_PL").Value = ""


'clear quoreg and application form
ThisWorkbook.Worksheets("quoreg").Rows("3").ClearContents
ThisWorkbook.Worksheets("Import - Application Form").Rows("2").ClearContents


'worksheets
Dim ShtName As String
For i = 1 To Range("ShtLib_end").Row - Range("ShtLib_start").Row
ShtName = ThisWorkbook.Worksheets("Assumptions").Cells(Range("ShtLib_start").Row + i, 1).Value
If ThisWorkbook.Worksheets("Assumptions").Cells(Range("ShtLib_start").Row + i, 2).Value = True Then
ThisWorkbook.Worksheets(ShtName).Visible = xlVeryHidden
Else
ThisWorkbook.Worksheets(ShtName).Visible = True
End If
Next i




'remove SPO mark
ThisWorkbook.Worksheets("Quotation Slip").Range("QS_SPO").Value = ""


'hide rows on sum insured tab
ThisWorkbook.Worksheets("Property SI").Rows("27:29").EntireRow.Hidden = True


ThisWorkbook.Worksheets("Quotation").Cells(1, 1) = ""


ThisWorkbook.Worksheets("Quotation").Activate
ActiveWindow.ScrollRow = 1


'hide columns on quotation tab
ThisWorkbook.Worksheets("Quotation").Columns("BE:CK").EntireColumn.Hidden = True
ThisWorkbook.Worksheets("Occupation").Columns("B:Q").EntireColumn.Hidden = True
'ThisWorkbook.Worksheets("Property SI").Columns("Q").EntireColumn.Hidden = True


ThisWorkbook.Worksheets("Quotation Slip").Columns("AG:AO").EntireColumn.Hidden = True


'Property SI Tab
With ThisWorkbook.Worksheets("Property SI")
Range("AR_1").Value = "Building"
Range("AR_2").Value = "Furniture, Fittings & Fixtures"
Range("AR_3").Value = "Contents"
Range("AR_4").Value = "Stock"
Range("AR_5").Value = "Machinery"
Range("AR_6").Value = ""
Range("AR_7").Value = ""
Range("AR_8").Value = ""
Range("AR_9").Value = ""
Range("AR_10").Value = ""

Range("FEP_Building").Value = "Building"

Range("SI_BI_ICOW_title").Value = "Additional Increased Cost of Working"
Range("SI_BI_Rent_title").Value = "Rent"
Range("SI_BI_Auditor_title").Value = "Auditor's Fee"
Range("SI_BI_4_title").Value = ""
Range("SI_BI_5_title").Value = ""
End With


'Quoref
Call set_quoref
Call Display_SI
Call Display
ThisWorkbook.Worksheets("Quotation").Range("WIC_Occ1").EntireRow.Hidden = False


'Call hiding claims
Call Property_Hide_Claims
Call WIC_Hide_Claims
Call PL_Hide_Claims


Range("flag_stop") = 0




'ProActiveMapping------------------------------------------------------------------------
'OppIDArray = ThisWorkbook.Worksheets("ProActive Mapping").Range("ProActive_OppIDArray_Range")
'For k = 1 To UBound(OppIDArray, 1)
' Range(OppIDArray(k, 13)).Value = ""
'Next k


'ThisWorkbook.Sheets("Quotation Slip").Shapes("Button 2").Visible = False
Range("pw_entered").Value = ""
'------------------------------------------------------------------------------------------


Call Protect


Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

 

[snowstars695]

the button is a picture. and it calls the reset macro directly.