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

S

snowstars695

New Member
Joined
Jan 16, 2017
Messages
3
[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]
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)
Sort by date Sort by votes
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?
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
Saving workbook "hides" macros
Replies
2
Views
374
dotsent
D
J
  • Question
Forced to discard digital signature after opening and immediately saving an Macro-Enabled Excel file
Replies
0
Views
91
JDCreate
J
Ottsel
  • Solved
Loading Screen Userform; Make X Exit Sub When Clicked
Replies
5
Views
609
Ottsel
Ottsel
A
  • Question
Excel custom ribbon disappears when running macro
Replies
0
Views
1K
avcape
A
D
  • Question
Table isn't updating when goal seek runs
Replies
1
Views
391
dimitrisanto13
D

Forum statistics

Threads
1,215,025
Messages
6,122,731
Members
449,093
Latest member
Mnur

Share this page

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
Back
Top