Excel crash when running macro after document save

K

kinmarcus

New Member
Joined
Jan 28, 2018
Messages
2
Hello all,

I have managed to break an excel reporting workbook I was working on and I would greatly appreciate any help you could provide with this issue.

The problem is with running a macro in the Excel workbook. The userform opens fine so I can enter data, but clicking the macro button on the userform to run the vba will crash excel.

It is a very strange problem because it always works the first time i.e. opening the book after a crash, I can open the form, enter data, click macro buttons and the vba works without a problem. After saving, copying, or sending the document via email, the next time it is opened, it will open the userform but crash when the macro button is clicked.

I believe it could be connected to the fact that Excel never asks to enable macros the second time the document is opened. But I do not know if this is indeed relevant or what to do with this information.

I have searched the forums and found many threads that cover vbe*.dll errors or corrupt macro problems, and have tried many fixes e.g. update .net framework, rebuilding the workbook by exporting the modules, playing with the compiling options. Nothing has worked so far.

Code: 
Private Sub btnCalculate_Click()
Dim ws As Worksheet
Dim Q1_Average As Integer
Dim Q2_Average As Integer
Dim Q3_Average As Integer
Dim Q4_Average As Integer
Dim Q5_Average As Integer
Dim Q6_Average As Integer
Dim Q7_Average As Integer
Dim Q8_Average As Integer
Dim Q9_Average As Integer
Dim NPS_Promotor As Integer
Dim NPS_Passive As Integer
Dim NPS_Detractor As Integer
Set ws = Worksheets("KPIs")
'copy information from userform to KPISheet for calculation
'Course
ws.Cells(1, 7).Value = Me.cbxCourse.Value
'# of Participants
ws.Cells(2, 7).Value = Me.txtParticipants.Value
'Location
ws.Cells(3, 7).Value = Me.txtLocation.Value
'Facilitator
ws.Cells(4, 7).Value = Me.txtFacilitator.Value
'Date
ws.Cells(5, 7).Value = Me.txtDate.Value
'Question 1 data
ws.Cells(1, 8).Value = Me.txtP1Q1.Value
ws.Cells(2, 8).Value = Me.txtP2Q1.Value
ws.Cells(3, 8).Value = Me.txtP3Q1.Value
ws.Cells(4, 8).Value = Me.txtP4Q1.Value
ws.Cells(5, 8).Value = Me.txtP5Q1.Value
ws.Cells(6, 8).Value = Me.txtP6Q1.Value
ws.Cells(7, 8).Value = Me.txtP7Q1.Value
ws.Cells(8, 8).Value = Me.txtP8Q1.Value
ws.Cells(9, 8).Value = Me.txtP9Q1.Value
ws.Cells(10, 8).Value = Me.txtP10Q1.Value
ws.Cells(11, 8).Value = Me.txtP11Q1.Value
ws.Cells(12, 8).Value = Me.txtP12Q1.Value
ws.Cells(13, 8).Value = Me.txtP13Q1.Value
ws.Cells(14, 8).Value = Me.txtP14Q1.Value
ws.Cells(15, 8).Value = Me.txtP15Q1.Value
ws.Cells(16, 8).Value = Me.txtP16Q1.Value
ws.Cells(17, 8).Value = Me.txtP17Q1.Value
ws.Cells(18, 8).Value = Me.txtP18Q1.Value
ws.Cells(19, 8).Value = Me.txtP19Q1.Value
ws.Cells(20, 8).Value = Me.txtP20Q1.Value
ws.Cells(21, 8).Value = Me.txtP21Q1.Value
ws.Cells(22, 8).Value = Me.txtP22Q1.Value
ws.Cells(23, 8).Value = Me.txtP23Q1.Value
ws.Cells(24, 8).Value = Me.txtP24Q1.Value
ws.Cells(25, 8).Value = Me.txtP25Q1.Value
ws.Cells(26, 8).Value = Me.txtP26Q1.Value
ws.Cells(27, 8).Value = Me.txtP27Q1.Value
ws.Cells(28, 8).Value = Me.txtP28Q1.Value
ws.Cells(29, 8).Value = Me.txtP29Q1.Value
ws.Cells(30, 8).Value = Me.txtP30Q1.Value
ws.Cells(31, 8).Value = Me.txtP31Q1.Value
ws.Cells(32, 8).Value = Me.txtP32Q1.Value
ws.Cells(33, 8).Value = Me.txtP33Q1.Value
ws.Cells(34, 8).Value = Me.txtP34Q1.Value
ws.Cells(35, 8).Value = Me.txtP35Q1.Value
ws.Cells(36, 8).Value = Me.txtP36Q1.Value
ws.Cells(37, 8).Value = Me.txtP37Q1.Value
ws.Cells(38, 8).Value = Me.txtP38Q1.Value
ws.Cells(39, 8).Value = Me.txtP39Q1.Value
ws.Cells(40, 8).Value = Me.txtP40Q1.Value
'Question 2 data
ws.Cells(1, 9).Value = Me.txtP1Q2.Value
ws.Cells(2, 9).Value = Me.txtP2Q2.Value
ws.Cells(3, 9).Value = Me.txtP3Q2.Value
ws.Cells(4, 9).Value = Me.txtP4Q2.Value
ws.Cells(5, 9).Value = Me.txtP5Q2.Value
ws.Cells(6, 9).Value = Me.txtP6Q2.Value
ws.Cells(7, 9).Value = Me.txtP7Q2.Value
ws.Cells(8, 9).Value = Me.txtP8Q2.Value
ws.Cells(9, 9).Value = Me.txtP9Q2.Value
ws.Cells(10, 9).Value = Me.txtP10Q2.Value
ws.Cells(11, 9).Value = Me.txtP11Q2.Value
ws.Cells(12, 9).Value = Me.txtP12Q2.Value
ws.Cells(13, 9).Value = Me.txtP13Q2.Value
ws.Cells(14, 9).Value = Me.txtP14Q2.Value
ws.Cells(15, 9).Value = Me.txtP15Q2.Value
ws.Cells(16, 9).Value = Me.txtP16Q2.Value
ws.Cells(17, 9).Value = Me.txtP17Q2.Value
ws.Cells(18, 9).Value = Me.txtP18Q2.Value
ws.Cells(19, 9).Value = Me.txtP19Q2.Value
ws.Cells(20, 9).Value = Me.txtP20Q2.Value
ws.Cells(21, 9).Value = Me.txtP21Q2.Value
ws.Cells(22, 9).Value = Me.txtP22Q2.Value
ws.Cells(23, 9).Value = Me.txtP23Q2.Value
ws.Cells(24, 9).Value = Me.txtP24Q2.Value
ws.Cells(25, 9).Value = Me.txtP25Q2.Value
ws.Cells(26, 9).Value = Me.txtP26Q2.Value
ws.Cells(27, 9).Value = Me.txtP27Q2.Value
ws.Cells(28, 9).Value = Me.txtP28Q2.Value
ws.Cells(29, 9).Value = Me.txtP29Q2.Value
ws.Cells(30, 9).Value = Me.txtP30Q2.Value
ws.Cells(31, 9).Value = Me.txtP31Q2.Value
ws.Cells(32, 9).Value = Me.txtP32Q2.Value
ws.Cells(33, 9).Value = Me.txtP33Q2.Value
ws.Cells(34, 9).Value = Me.txtP34Q2.Value
ws.Cells(35, 9).Value = Me.txtP35Q2.Value
ws.Cells(36, 9).Value = Me.txtP36Q2.Value
ws.Cells(37, 9).Value = Me.txtP37Q2.Value
ws.Cells(38, 9).Value = Me.txtP38Q2.Value
ws.Cells(39, 9).Value = Me.txtP39Q2.Value
ws.Cells(40, 9).Value = Me.txtP40Q2.Value
'Question 3 data
ws.Cells(1, 10).Value = Me.txtP1Q3.Value
ws.Cells(2, 10).Value = Me.txtP2Q3.Value
ws.Cells(3, 10).Value = Me.txtP3Q3.Value
ws.Cells(4, 10).Value = Me.txtP4Q3.Value
ws.Cells(5, 10).Value = Me.txtP5Q3.Value
ws.Cells(6, 10).Value = Me.txtP6Q3.Value
ws.Cells(7, 10).Value = Me.txtP7Q3.Value
ws.Cells(8, 10).Value = Me.txtP8Q3.Value
ws.Cells(9, 10).Value = Me.txtP9Q3.Value
ws.Cells(10, 10).Value = Me.txtP10Q3.Value
ws.Cells(11, 10).Value = Me.txtP11Q3.Value
ws.Cells(12, 10).Value = Me.txtP12Q3.Value
ws.Cells(13, 10).Value = Me.txtP13Q3.Value
ws.Cells(14, 10).Value = Me.txtP14Q3.Value
ws.Cells(15, 10).Value = Me.txtP15Q3.Value
ws.Cells(16, 10).Value = Me.txtP16Q3.Value
ws.Cells(17, 10).Value = Me.txtP17Q3.Value
ws.Cells(18, 10).Value = Me.txtP18Q3.Value
ws.Cells(19, 10).Value = Me.txtP19Q3.Value
ws.Cells(20, 10).Value = Me.txtP20Q3.Value
ws.Cells(21, 10).Value = Me.txtP21Q3.Value
ws.Cells(22, 10).Value = Me.txtP22Q3.Value
ws.Cells(23, 10).Value = Me.txtP23Q3.Value
ws.Cells(24, 10).Value = Me.txtP24Q3.Value
ws.Cells(25, 10).Value = Me.txtP25Q3.Value
ws.Cells(26, 10).Value = Me.txtP26Q3.Value
ws.Cells(27, 10).Value = Me.txtP27Q3.Value
ws.Cells(28, 10).Value = Me.txtP28Q3.Value
ws.Cells(29, 10).Value = Me.txtP29Q3.Value
ws.Cells(30, 10).Value = Me.txtP30Q3.Value
ws.Cells(31, 10).Value = Me.txtP31Q3.Value
ws.Cells(32, 10).Value = Me.txtP32Q3.Value
ws.Cells(33, 10).Value = Me.txtP33Q3.Value
ws.Cells(34, 10).Value = Me.txtP34Q3.Value
ws.Cells(35, 10).Value = Me.txtP35Q3.Value
ws.Cells(36, 10).Value = Me.txtP36Q3.Value
ws.Cells(37, 10).Value = Me.txtP37Q3.Value
ws.Cells(38, 10).Value = Me.txtP38Q3.Value
ws.Cells(39, 10).Value = Me.txtP39Q3.Value
ws.Cells(40, 10).Value = Me.txtP40Q3.Value
'Question 4 data
ws.Cells(1, 11).Value = Me.txtP1Q4.Value
ws.Cells(2, 11).Value = Me.txtP2Q4.Value
ws.Cells(3, 11).Value = Me.txtP3Q4.Value
ws.Cells(4, 11).Value = Me.txtP4Q4.Value
ws.Cells(5, 11).Value = Me.txtP5Q4.Value
ws.Cells(6, 11).Value = Me.txtP6Q4.Value
ws.Cells(7, 11).Value = Me.txtP7Q4.Value
ws.Cells(8, 11).Value = Me.txtP8Q4.Value
ws.Cells(9, 11).Value = Me.txtP9Q4.Value
ws.Cells(10, 11).Value = Me.txtP10Q4.Value
ws.Cells(11, 11).Value = Me.txtP11Q4.Value
ws.Cells(12, 11).Value = Me.txtP12Q4.Value
ws.Cells(13, 11).Value = Me.txtP13Q4.Value
ws.Cells(14, 11).Value = Me.txtP14Q4.Value
ws.Cells(15, 11).Value = Me.txtP15Q4.Value
ws.Cells(16, 11).Value = Me.txtP16Q4.Value
ws.Cells(17, 11).Value = Me.txtP17Q4.Value
ws.Cells(18, 11).Value = Me.txtP18Q4.Value
ws.Cells(19, 11).Value = Me.txtP19Q4.Value
ws.Cells(20, 11).Value = Me.txtP20Q4.Value
ws.Cells(21, 11).Value = Me.txtP21Q4.Value
ws.Cells(22, 11).Value = Me.txtP22Q4.Value
ws.Cells(23, 11).Value = Me.txtP23Q4.Value
ws.Cells(24, 11).Value = Me.txtP24Q4.Value
ws.Cells(25, 11).Value = Me.txtP25Q4.Value
ws.Cells(26, 11).Value = Me.txtP26Q4.Value
ws.Cells(27, 11).Value = Me.txtP27Q4.Value
ws.Cells(28, 11).Value = Me.txtP28Q4.Value
ws.Cells(29, 11).Value = Me.txtP29Q4.Value
ws.Cells(30, 11).Value = Me.txtP30Q4.Value
ws.Cells(31, 11).Value = Me.txtP31Q4.Value
ws.Cells(32, 11).Value = Me.txtP32Q4.Value
ws.Cells(33, 11).Value = Me.txtP33Q4.Value
ws.Cells(34, 11).Value = Me.txtP34Q4.Value
ws.Cells(35, 11).Value = Me.txtP35Q4.Value
ws.Cells(36, 11).Value = Me.txtP36Q4.Value
ws.Cells(37, 11).Value = Me.txtP37Q4.Value
ws.Cells(38, 11).Value = Me.txtP38Q4.Value
ws.Cells(39, 11).Value = Me.txtP39Q4.Value
ws.Cells(40, 11).Value = Me.txtP40Q4.Value
'Question 5 data
ws.Cells(1, 12).Value = Me.txtP1Q5.Value
ws.Cells(2, 12).Value = Me.txtP2Q5.Value
ws.Cells(3, 12).Value = Me.txtP3Q5.Value
ws.Cells(4, 12).Value = Me.txtP4Q5.Value
ws.Cells(5, 12).Value = Me.txtP5Q5.Value
ws.Cells(6, 12).Value = Me.txtP6Q5.Value
ws.Cells(7, 12).Value = Me.txtP7Q5.Value
ws.Cells(8, 12).Value = Me.txtP8Q5.Value
ws.Cells(9, 12).Value = Me.txtP9Q5.Value
ws.Cells(10, 12).Value = Me.txtP10Q5.Value
ws.Cells(11, 12).Value = Me.txtP11Q5.Value
ws.Cells(12, 12).Value = Me.txtP12Q5.Value
ws.Cells(13, 12).Value = Me.txtP13Q5.Value
ws.Cells(14, 12).Value = Me.txtP14Q5.Value
ws.Cells(15, 12).Value = Me.txtP15Q5.Value
ws.Cells(16, 12).Value = Me.txtP16Q5.Value
ws.Cells(17, 12).Value = Me.txtP17Q5.Value
ws.Cells(18, 12).Value = Me.txtP18Q5.Value
ws.Cells(19, 12).Value = Me.txtP19Q5.Value
ws.Cells(20, 12).Value = Me.txtP20Q5.Value
ws.Cells(21, 12).Value = Me.txtP21Q5.Value
ws.Cells(22, 12).Value = Me.txtP22Q5.Value
ws.Cells(23, 12).Value = Me.txtP23Q5.Value
ws.Cells(24, 12).Value = Me.txtP24Q5.Value
ws.Cells(25, 12).Value = Me.txtP25Q5.Value
ws.Cells(26, 12).Value = Me.txtP26Q5.Value
ws.Cells(27, 12).Value = Me.txtP27Q5.Value
ws.Cells(28, 12).Value = Me.txtP28Q5.Value
ws.Cells(29, 12).Value = Me.txtP29Q5.Value
ws.Cells(30, 12).Value = Me.txtP30Q5.Value
ws.Cells(31, 12).Value = Me.txtP31Q5.Value
ws.Cells(32, 12).Value = Me.txtP32Q5.Value
ws.Cells(33, 12).Value = Me.txtP33Q5.Value
ws.Cells(34, 12).Value = Me.txtP34Q5.Value
ws.Cells(35, 12).Value = Me.txtP35Q5.Value
ws.Cells(36, 12).Value = Me.txtP36Q5.Value
ws.Cells(37, 12).Value = Me.txtP37Q5.Value
ws.Cells(38, 12).Value = Me.txtP38Q5.Value
ws.Cells(39, 12).Value = Me.txtP39Q5.Value
ws.Cells(40, 12).Value = Me.txtP40Q5.Value
'Question 6 data
ws.Cells(1, 13).Value = Me.txtP1Q6.Value
ws.Cells(2, 13).Value = Me.txtP2Q6.Value
ws.Cells(3, 13).Value = Me.txtP3Q6.Value
ws.Cells(4, 13).Value = Me.txtP4Q6.Value
ws.Cells(5, 13).Value = Me.txtP5Q6.Value
ws.Cells(6, 13).Value = Me.txtP6Q6.Value
ws.Cells(7, 13).Value = Me.txtP7Q6.Value
ws.Cells(8, 13).Value = Me.txtP8Q6.Value
ws.Cells(9, 13).Value = Me.txtP9Q6.Value
ws.Cells(10, 13).Value = Me.txtP10Q6.Value
ws.Cells(11, 13).Value = Me.txtP11Q6.Value
ws.Cells(12, 13).Value = Me.txtP12Q6.Value
ws.Cells(13, 13).Value = Me.txtP13Q6.Value
ws.Cells(14, 13).Value = Me.txtP14Q6.Value
ws.Cells(15, 13).Value = Me.txtP15Q6.Value
ws.Cells(16, 13).Value = Me.txtP16Q6.Value
ws.Cells(17, 13).Value = Me.txtP17Q6.Value
ws.Cells(18, 13).Value = Me.txtP18Q6.Value
ws.Cells(19, 13).Value = Me.txtP19Q6.Value
ws.Cells(20, 13).Value = Me.txtP20Q6.Value
ws.Cells(21, 13).Value = Me.txtP21Q6.Value
ws.Cells(22, 13).Value = Me.txtP22Q6.Value
ws.Cells(23, 13).Value = Me.txtP23Q6.Value
ws.Cells(24, 13).Value = Me.txtP24Q6.Value
ws.Cells(25, 13).Value = Me.txtP25Q6.Value
ws.Cells(26, 13).Value = Me.txtP26Q6.Value
ws.Cells(27, 13).Value = Me.txtP27Q6.Value
ws.Cells(28, 13).Value = Me.txtP28Q6.Value
ws.Cells(29, 13).Value = Me.txtP29Q6.Value
ws.Cells(30, 13).Value = Me.txtP30Q6.Value
ws.Cells(31, 13).Value = Me.txtP31Q6.Value
ws.Cells(32, 13).Value = Me.txtP32Q6.Value
ws.Cells(33, 13).Value = Me.txtP33Q6.Value
ws.Cells(34, 13).Value = Me.txtP34Q6.Value
ws.Cells(35, 13).Value = Me.txtP35Q6.Value
ws.Cells(36, 13).Value = Me.txtP36Q6.Value
ws.Cells(37, 13).Value = Me.txtP37Q6.Value
ws.Cells(38, 13).Value = Me.txtP38Q6.Value
ws.Cells(39, 13).Value = Me.txtP39Q6.Value
ws.Cells(40, 13).Value = Me.txtP40Q6.Value
'Question 7 data
ws.Cells(1, 14).Value = Me.txtP1Q7.Value
ws.Cells(2, 14).Value = Me.txtP2Q7.Value
ws.Cells(3, 14).Value = Me.txtP3Q7.Value
ws.Cells(4, 14).Value = Me.txtP4Q7.Value
ws.Cells(5, 14).Value = Me.txtP5Q7.Value
ws.Cells(6, 14).Value = Me.txtP6Q7.Value
ws.Cells(7, 14).Value = Me.txtP7Q7.Value
ws.Cells(8, 14).Value = Me.txtP8Q7.Value
ws.Cells(9, 14).Value = Me.txtP9Q7.Value
ws.Cells(10, 14).Value = Me.txtP10Q7.Value
ws.Cells(11, 14).Value = Me.txtP11Q7.Value
ws.Cells(12, 14).Value = Me.txtP12Q7.Value
ws.Cells(13, 14).Value = Me.txtP13Q7.Value
ws.Cells(14, 14).Value = Me.txtP14Q7.Value
ws.Cells(15, 14).Value = Me.txtP15Q7.Value
ws.Cells(16, 14).Value = Me.txtP16Q7.Value
ws.Cells(17, 14).Value = Me.txtP17Q7.Value
ws.Cells(18, 14).Value = Me.txtP18Q7.Value
ws.Cells(19, 14).Value = Me.txtP19Q7.Value
ws.Cells(20, 14).Value = Me.txtP20Q7.Value
ws.Cells(21, 14).Value = Me.txtP21Q7.Value
ws.Cells(22, 14).Value = Me.txtP22Q7.Value
ws.Cells(23, 14).Value = Me.txtP23Q7.Value
ws.Cells(24, 14).Value = Me.txtP24Q7.Value
ws.Cells(25, 14).Value = Me.txtP25Q7.Value
ws.Cells(26, 14).Value = Me.txtP26Q7.Value
ws.Cells(27, 14).Value = Me.txtP27Q7.Value
ws.Cells(28, 14).Value = Me.txtP28Q7.Value
ws.Cells(29, 14).Value = Me.txtP29Q7.Value
ws.Cells(30, 14).Value = Me.txtP30Q7.Value
ws.Cells(31, 14).Value = Me.txtP31Q7.Value
ws.Cells(32, 14).Value = Me.txtP32Q7.Value
ws.Cells(33, 14).Value = Me.txtP33Q7.Value
ws.Cells(34, 14).Value = Me.txtP34Q7.Value
ws.Cells(35, 14).Value = Me.txtP35Q7.Value
ws.Cells(36, 14).Value = Me.txtP36Q7.Value
ws.Cells(37, 14).Value = Me.txtP37Q7.Value
ws.Cells(38, 14).Value = Me.txtP38Q7.Value
ws.Cells(39, 14).Value = Me.txtP39Q7.Value
ws.Cells(40, 14).Value = Me.txtP40Q7.Value
'Question 8 data
ws.Cells(1, 15).Value = Me.txtP1Q8.Value
ws.Cells(2, 15).Value = Me.txtP2Q8.Value
ws.Cells(3, 15).Value = Me.txtP3Q8.Value
ws.Cells(4, 15).Value = Me.txtP4Q8.Value
ws.Cells(5, 15).Value = Me.txtP5Q8.Value
ws.Cells(6, 15).Value = Me.txtP6Q8.Value
ws.Cells(7, 15).Value = Me.txtP7Q8.Value
ws.Cells(8, 15).Value = Me.txtP8Q8.Value
ws.Cells(9, 15).Value = Me.txtP9Q8.Value
ws.Cells(10, 15).Value = Me.txtP10Q8.Value
ws.Cells(11, 15).Value = Me.txtP11Q8.Value
ws.Cells(12, 15).Value = Me.txtP12Q8.Value
ws.Cells(13, 15).Value = Me.txtP13Q8.Value
ws.Cells(14, 15).Value = Me.txtP14Q8.Value
ws.Cells(15, 15).Value = Me.txtP15Q8.Value
ws.Cells(16, 15).Value = Me.txtP16Q8.Value
ws.Cells(17, 15).Value = Me.txtP17Q8.Value
ws.Cells(18, 15).Value = Me.txtP18Q8.Value
ws.Cells(19, 15).Value = Me.txtP19Q8.Value
ws.Cells(20, 15).Value = Me.txtP20Q8.Value
ws.Cells(21, 15).Value = Me.txtP21Q8.Value
ws.Cells(22, 15).Value = Me.txtP22Q8.Value
ws.Cells(23, 15).Value = Me.txtP23Q8.Value
ws.Cells(24, 15).Value = Me.txtP24Q8.Value
ws.Cells(25, 15).Value = Me.txtP25Q8.Value
ws.Cells(26, 15).Value = Me.txtP26Q8.Value
ws.Cells(27, 15).Value = Me.txtP27Q8.Value
ws.Cells(28, 15).Value = Me.txtP28Q8.Value
ws.Cells(29, 15).Value = Me.txtP29Q8.Value
ws.Cells(30, 15).Value = Me.txtP30Q8.Value
ws.Cells(31, 15).Value = Me.txtP31Q8.Value
ws.Cells(32, 15).Value = Me.txtP32Q8.Value
ws.Cells(33, 15).Value = Me.txtP33Q8.Value
ws.Cells(34, 15).Value = Me.txtP34Q8.Value
ws.Cells(35, 15).Value = Me.txtP35Q8.Value
ws.Cells(36, 15).Value = Me.txtP36Q8.Value
ws.Cells(37, 15).Value = Me.txtP37Q8.Value
ws.Cells(38, 15).Value = Me.txtP38Q8.Value
ws.Cells(39, 15).Value = Me.txtP39Q8.Value
ws.Cells(40, 15).Value = Me.txtP40Q8.Value
'Question 9 data
ws.Cells(1, 16).Value = Me.txtP1Q9.Value
ws.Cells(2, 16).Value = Me.txtP2Q9.Value
ws.Cells(3, 16).Value = Me.txtP3Q9.Value
ws.Cells(4, 16).Value = Me.txtP4Q9.Value
ws.Cells(5, 16).Value = Me.txtP5Q9.Value
ws.Cells(6, 16).Value = Me.txtP6Q9.Value
ws.Cells(7, 16).Value = Me.txtP7Q9.Value
ws.Cells(8, 16).Value = Me.txtP8Q9.Value
ws.Cells(9, 16).Value = Me.txtP9Q9.Value
ws.Cells(10, 16).Value = Me.txtP10Q9.Value
ws.Cells(11, 16).Value = Me.txtP11Q9.Value
ws.Cells(12, 16).Value = Me.txtP12Q9.Value
ws.Cells(13, 16).Value = Me.txtP13Q9.Value
ws.Cells(14, 16).Value = Me.txtP14Q9.Value
ws.Cells(15, 16).Value = Me.txtP15Q9.Value
ws.Cells(16, 16).Value = Me.txtP16Q9.Value
ws.Cells(17, 16).Value = Me.txtP17Q9.Value
ws.Cells(18, 16).Value = Me.txtP18Q9.Value
ws.Cells(19, 16).Value = Me.txtP19Q9.Value
ws.Cells(20, 16).Value = Me.txtP20Q9.Value
ws.Cells(21, 16).Value = Me.txtP21Q9.Value
ws.Cells(22, 16).Value = Me.txtP22Q9.Value
ws.Cells(23, 16).Value = Me.txtP23Q9.Value
ws.Cells(24, 16).Value = Me.txtP24Q9.Value
ws.Cells(25, 16).Value = Me.txtP25Q9.Value
ws.Cells(26, 16).Value = Me.txtP26Q9.Value
ws.Cells(27, 16).Value = Me.txtP27Q9.Value
ws.Cells(28, 16).Value = Me.txtP28Q9.Value
ws.Cells(29, 16).Value = Me.txtP29Q9.Value
ws.Cells(30, 16).Value = Me.txtP30Q9.Value
ws.Cells(31, 16).Value = Me.txtP31Q9.Value
ws.Cells(32, 16).Value = Me.txtP32Q9.Value
ws.Cells(33, 16).Value = Me.txtP33Q9.Value
ws.Cells(34, 16).Value = Me.txtP34Q9.Value
ws.Cells(35, 16).Value = Me.txtP35Q9.Value
ws.Cells(36, 16).Value = Me.txtP36Q9.Value
ws.Cells(37, 16).Value = Me.txtP37Q9.Value
ws.Cells(38, 16).Value = Me.txtP38Q9.Value
ws.Cells(39, 16).Value = Me.txtP39Q9.Value
ws.Cells(40, 16).Value = Me.txtP40Q9.Value
'NPS Score data
ws.Cells(1, 17).Value = Me.txtP1NPS.Value
ws.Cells(2, 17).Value = Me.txtP2NPS.Value
ws.Cells(3, 17).Value = Me.txtP3NPS.Value
ws.Cells(4, 17).Value = Me.txtP4NPS.Value
ws.Cells(5, 17).Value = Me.txtP5NPS.Value
ws.Cells(6, 17).Value = Me.txtP6NPS.Value
ws.Cells(7, 17).Value = Me.txtP7NPS.Value
ws.Cells(8, 17).Value = Me.txtP8NPS.Value
ws.Cells(9, 17).Value = Me.txtP9NPS.Value
ws.Cells(10, 17).Value = Me.txtP10NPS.Value
ws.Cells(11, 17).Value = Me.txtP11NPS.Value
ws.Cells(12, 17).Value = Me.txtP12NPS.Value
ws.Cells(13, 17).Value = Me.txtP13NPS.Value
ws.Cells(14, 17).Value = Me.txtP14NPS.Value
ws.Cells(15, 17).Value = Me.txtP15NPS.Value
ws.Cells(16, 17).Value = Me.txtP16NPS.Value
ws.Cells(17, 17).Value = Me.txtP17NPS.Value
ws.Cells(18, 17).Value = Me.txtP18NPS.Value
ws.Cells(19, 17).Value = Me.txtP19NPS.Value
ws.Cells(20, 17).Value = Me.txtP20NPS.Value
ws.Cells(21, 17).Value = Me.txtP21NPS.Value
ws.Cells(22, 17).Value = Me.txtP22NPS.Value
ws.Cells(23, 17).Value = Me.txtP23NPS.Value
ws.Cells(24, 17).Value = Me.txtP24NPS.Value
ws.Cells(25, 17).Value = Me.txtP25NPS.Value
ws.Cells(26, 17).Value = Me.txtP26NPS.Value
ws.Cells(27, 17).Value = Me.txtP27NPS.Value
ws.Cells(28, 17).Value = Me.txtP28NPS.Value
ws.Cells(29, 17).Value = Me.txtP29NPS.Value
ws.Cells(30, 17).Value = Me.txtP30NPS.Value
ws.Cells(31, 17).Value = Me.txtP31NPS.Value
ws.Cells(32, 17).Value = Me.txtP32NPS.Value
ws.Cells(33, 17).Value = Me.txtP33NPS.Value
ws.Cells(34, 17).Value = Me.txtP34NPS.Value
ws.Cells(35, 17).Value = Me.txtP35NPS.Value
ws.Cells(36, 17).Value = Me.txtP36NPS.Value
ws.Cells(37, 17).Value = Me.txtP37NPS.Value
ws.Cells(38, 17).Value = Me.txtP38NPS.Value
ws.Cells(39, 17).Value = Me.txtP39NPS.Value
ws.Cells(40, 17).Value = Me.txtP40NPS.Value
'calculate averages ready for return to userform
Q1_Average = Application.Average(Worksheets("KPIs").Range("h1:h40"))
Q2_Average = Application.Average(Worksheets("KPIs").Range("i1:i40"))
Q3_Average = Application.Average(Worksheets("KPIs").Range("j1:j40"))
Q4_Average = Application.Average(Worksheets("KPIs").Range("k1:k40"))
Q5_Average = Application.Average(Worksheets("KPIs").Range("l1:l40"))
Q6_Average = Application.Average(Worksheets("KPIs").Range("m1:m40"))
Q7_Average = Application.Average(Worksheets("KPIs").Range("n1:n40"))
Q8_Average = Application.Average(Worksheets("KPIs").Range("o1:o40"))
Q9_Average = Application.Average(Worksheets("KPIs").Range("p1:p40"))
NPS_Promotor = Application.CountIf(Worksheets("KPIs").Range("q1:q40"), ">=" & 9)
NPS_Passive = Application.CountIfs(Worksheets("KPIs").Range("q1:q40"), ">=" & 7, Worksheets("KPIs").Range("q1:q40"), "<=" & 8)
NPS_Detractor = Application.Count(Worksheets("KPIs").Range("q1:q40")) - (NPS_Promotor + NPS_Passive)
'transfer summary information back to userform
frm25.txtQ1Ave.Text = Q1_Average
frm25.txtQ2Ave.Text = Q2_Average
frm25.txtQ3Ave.Text = Q3_Average
frm25.txtQ4Ave.Text = Q4_Average
frm25.txtQ5Ave.Text = Q5_Average
frm25.txtQ6Ave.Text = Q6_Average
frm25.txtQ7Ave.Text = Q7_Average
frm25.txtQ8Ave.Text = Q8_Average
frm25.txtQ9Ave.Text = Q9_Average
frm25.txtNPSPro.Text = NPS_Promotor
frm25.txtNPSPas.Text = NPS_Passive
frm25.txtNPSDet.Text = NPS_Detractor
'remove all calculation data from KPISheet
Worksheets("KPIs").Range("g1:q40").ClearContents


End Sub


Private Sub btnClose_Click()
Dim answer As Integer
answer = MsgBox("Are you sure you want to close the sheet?", vbYesNo + vbQuestion, "Close Sheet")
If answer = vbYes Then
    frm25.Hide
Else
    'do nothing
End If
End Sub


Private Sub btnCopy_Click()


Dim ws As Worksheet
Set ws = Worksheets("Classes")
'To find the next empty row.
    erow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
     'To Write Data to Worksheet
    ws.Cells(erow, 1).Value = Me.cbxCourse.Value
    ws.Cells(erow, 2).Value = Me.txtDate.Value
    ws.Cells(erow, 3).Value = Me.txtParticipants.Value
    ws.Cells(erow, 5).Value = Me.txtFacilitator.Value
    ws.Cells(erow, 6).Value = Me.txtLocation.Value
    ws.Cells(erow, 7).Value = Me.txtQ1Ave.Value
    ws.Cells(erow, 8).Value = Me.txtQ2Ave.Value
    ws.Cells(erow, 9).Value = Me.txtQ3Ave.Value
    ws.Cells(erow, 10).Value = Me.txtQ4Ave.Value
    ws.Cells(erow, 11).Value = Me.txtQ5Ave.Value
    ws.Cells(erow, 12).Value = Me.txtQ6Ave.Value
    ws.Cells(erow, 13).Value = Me.txtQ7Ave.Value
    ws.Cells(erow, 14).Value = Me.txtQ8Ave.Value
    ws.Cells(erow, 15).Value = Me.txtQ9Ave.Value
    ws.Cells(erow, 16).Value = Me.txtNPSPro.Value
    ws.Cells(erow, 17).Value = Me.txtNPSPas.Value
    ws.Cells(erow, 18).Value = Me.txtNPSDet.Value
    'To Clear the Userform
    For Each Ctl In Me.Controls
        If TypeName(Ctl) = "TextBox" Or TypeName(Ctl) = "ComboBox" Then
        Ctl.Value = ""
        End If
    Next Ctl
    
    'To Save Workbook.
    ThisWorkbook.Save




myerror:
If Err <> 0 Then MsgBox (Error(Err)), 48, "Error"
End Sub


Private Sub cmdClose_Click()
'Close User Form.
Unload Me
End Sub


Private Sub btnReset_Click()
Dim answer As Integer
answer = MsgBox("Are you sure you want to reset the sheet?", vbYesNo + vbQuestion, "Reset Sheet")
If answer = vbYes Then
    Unload frm25
    frm25.Show
Else
    'do nothing
End If
End Sub

As you can see above, I am very new on my vba journey so I am open fr any help, be it improvements for my code or fixes for the crashing.

Many thanks in advance!
Best,
Marc
 

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple
Sort by date Sort by votes
you have
Dim Q1_Average As Integer (to me that is a WHOLE Number)

later you use

frm25.txtQ1Ave.Text = Q1_Average a TEXT value

not sure you can do that (IMHO)
 
Upvote 0
Thank you for the reply, Mole999.

I changed all of the 'frm25.txtQ1Ave.Text' code to 'frm25.txtQ1Ave.Value'.
I then tested the form and everything went fine, as is normal with the first attempt.

After saving and reopening, it failed on the second attempt and returned the following error:

Code: 
Problemsignatur:
  Problemereignisname:	APPCRASH
  Anwendungsname:	EXCEL.EXE
  Anwendungsversion:	15.0.4997.1000
  Anwendungszeitstempel:	5a2f7f99
  Fehlermodulname:	VBE7.DLL
  Fehlermodulversion:	7.1.10.68
  Fehlermodulzeitstempel:	58def2a6
  Ausnahmecode:	c0000005
  Ausnahmeoffset:	000dc806
  Betriebsystemversion:	6.1.7601.2.1.0.256.4
  Gebietsschema-ID:	1031


Zusatzinformationen zum Problem:
  LCID:	1033
  skulcid:	1033


Lesen Sie unsere Datenschutzbestimmungen online:
  http://go.microsoft.com/fwlink/?linkid=104288&clcid=0x0407


Wenn die Onlinedatenschutzbestimmungen nicht verfügbar sind, lesen Sie unsere Datenschutzbestimmungen offline:
  C:\Windows\system32\de-DE\erofflps.txt

Does anyone have any other ideas?
Best,
Marc
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question Question
Linking two sheets in different workbooks takes a long time even with only 500 rows of data. Is there a quicker, more efficient way to do this?
Replies
0
Views
158
chazrab
C
G
  • Solved
If Else Loop macro running slow, anyway to speed it up?
Replies
9
Views
258
Alex Blakenburg
A
A
  • Question Question
Code to Add Data From UserForm In Row Above or Below Based on Criteria
Replies
1
Views
478
amoverton2
A
R
  • Question Question
Highlighting cells based on criteria
Replies
0
Views
396
rahildhody
R
A
  • Solved
Listbox For Loop
Replies
7
Views
565
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,223,246
Messages
6,170,988
Members
452,373
Latest member
TimReeks

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