Hello and thankyou for any attention my post may receive.
So I have a userform which the user will capture all necessary information and save. The problem I have is not that it doesn't save but the length of time it takes to save. Perhaps my code below is not as efficient as it could be.
The userform information is saved to a spreadsheet in the same workbook.
Any help to speed up the saving process will be greatly appreciated. Thank you and have a great day!
So I have a userform which the user will capture all necessary information and save. The problem I have is not that it doesn't save but the length of time it takes to save. Perhaps my code below is not as efficient as it could be.
The userform information is saved to a spreadsheet in the same workbook.
Any help to speed up the saving process will be greatly appreciated. Thank you and have a great day!
Code:
Private Sub CommandButton1_Click()
Dim ws As Worksheet
Dim Inp, Outp
Dim Rng As Range
Inp = TextBox59.Value
Set ws = Worksheets("IncidentLog")
With ws.Range("A:A")
Set Rng = .Find(what:=Inp) ', after:=.Cells(.row.Count, 1), LookIn:=xlValues)
If Not Rng Is Nothing Then
Sheets("IncidentLog").Unprotect Password:="BEx101"
''' Notification
Rng.Offset(0, 7).Value = ComboBox1.Value
Rng.Offset(0, 8).Value = TextBox7.Value
Rng.Offset(0, 9).Value = ComboBox2.Value
Rng.Offset(0, 10).Value = TextBox8.Value
Rng.Offset(0, 11).Value = ComboBox3.Value
Rng.Offset(0, 12).Value = TextBox9.Value
''' Classification
Rng.Offset(0, 13).Value = ComboBox4.Value
Rng.Offset(0, 14).Value = TextBox10.Value
Rng.Offset(0, 15).Value = ComboBox5.Value
Rng.Offset(0, 16).Value = TextBox11.Value
Rng.Offset(0, 17).Value = ComboBox6.Value
Rng.Offset(0, 18).Value = TextBox12.Value
''' Special Investigation
Rng.Offset(0, 19).Value = ComboBox7.Value
Rng.Offset(0, 20).Value = TextBox13.Value
Rng.Offset(0, 21).Value = ComboBox8.Value
Rng.Offset(0, 22).Value = TextBox14.Value
Rng.Offset(0, 23).Value = ComboBox9.Value
Rng.Offset(0, 24).Value = TextBox15.Value
Rng.Offset(0, 25).Value = ComboBox10.Value
Rng.Offset(0, 26).Value = TextBox16.Value
Rng.Offset(0, 27).Value = ComboBox11.Value
Rng.Offset(0, 28).Value = TextBox17.Value
''' Investigation
Rng.Offset(0, 29).Value = ComboBox12.Value
Rng.Offset(0, 30).Value = TextBox18.Value
Rng.Offset(0, 31).Value = ComboBox13.Value
Rng.Offset(0, 32).Value = TextBox19.Value
Rng.Offset(0, 33).Value = ComboBox14.Value
Rng.Offset(0, 34).Value = TextBox20.Value
Rng.Offset(0, 35).Value = ComboBox15.Value
Rng.Offset(0, 36).Value = TextBox21.Value
''' Actions
Rng.Offset(0, 37).Value = ComboBox16.Value
Rng.Offset(0, 38).Value = TextBox23.Value
Rng.Offset(0, 39).Value = ComboBox17.Value
Rng.Offset(0, 40).Value = TextBox24.Value
Rng.Offset(0, 41).Value = ComboBox18.Value
Rng.Offset(0, 42).Value = TextBox25.Value
Rng.Offset(0, 43).Value = ComboBox19.Value
Rng.Offset(0, 44).Value = TextBox26.Value
''' Communication
Rng.Offset(0, 45).Value = ComboBox20.Value
Rng.Offset(0, 46).Value = TextBox27.Value
Rng.Offset(0, 47).Value = ComboBox21.Value
Rng.Offset(0, 48).Value = TextBox28.Value
''' Close-Out
Rng.Offset(0, 49).Value = ComboBox22.Value
Rng.Offset(0, 50).Value = TextBox29.Value
Rng.Offset(0, 51).Value = ComboBox23.Value
Rng.Offset(0, 52).Value = TextBox30.Value
''' Event Review
Rng.Offset(0, 53).Value = ComboBox24.Value
Rng.Offset(0, 54).Value = TextBox31.Value
''' Audit Results
Rng.Offset(0, 55).Value = Label82.Caption
Rng.Offset(0, 56).Value = Label83.Caption
Rng.Offset(0, 57).Value = TextBox58.Value
''' Audit Actions
Rng.Offset(0, 58).Value = TextBox32.Value
Rng.Offset(0, 59).Value = TextBox37.Value
Rng.Offset(0, 60).Value = TextBox41.Value
Rng.Offset(0, 61).Value = TextBox45.Value
Rng.Offset(0, 62).Value = TextBox33.Value
Rng.Offset(0, 63).Value = TextBox38.Value
Rng.Offset(0, 64).Value = TextBox42.Value
Rng.Offset(0, 65).Value = TextBox46.Value
Rng.Offset(0, 66).Value = TextBox34.Value
Rng.Offset(0, 67).Value = TextBox39.Value
Rng.Offset(0, 68).Value = TextBox43.Value
Rng.Offset(0, 69).Value = TextBox47.Value
Rng.Offset(0, 70).Value = TextBox35.Value
Rng.Offset(0, 71).Value = TextBox36.Value
Rng.Offset(0, 72).Value = TextBox40.Value
Rng.Offset(0, 73).Value = TextBox44.Value
Rng.Offset(0, 74).Value = TextBox60.Value
Rng.Offset(0, 75).Value = TextBox61.Value
Rng.Offset(0, 76).Value = TextBox62.Value
Rng.Offset(0, 77).Value = TextBox63.Value
''' Audit Review Team
Rng.Offset(0, 78).Value = Label69.Caption
Rng.Offset(0, 79).Value = Label73.Caption
Rng.Offset(0, 80).Value = CheckBox4.Value
Rng.Offset(0, 81).Value = Label70.Caption
Rng.Offset(0, 82).Value = Label74.Caption
Rng.Offset(0, 83).Value = CheckBox2.Value
Rng.Offset(0, 84).Value = Label71.Caption
Rng.Offset(0, 85).Value = Label75.Caption
Rng.Offset(0, 86).Value = CheckBox3.Value
Rng.Offset(0, 87).Value = Label68.Caption
Rng.Offset(0, 88).Value = Label72.Caption
Rng.Offset(0, 89).Value = CheckBox5.Value
Rng.Offset(0, 90).Value = TextBox57.Value
Rng.Offset(0, 99).Value = TextBox65.Value
''' Departments
Rng.Offset(0, 91).Value = ComboBox25.Value
Rng.Offset(0, 92).Value = ComboBox26.Value
Rng.Offset(0, 93).Value = ComboBox27.Value
Rng.Offset(0, 94).Value = ComboBox28.Value
Sheets("IncidentLog").Protect Password:="BEx101" _
, AllowFiltering:=True
Unload Me
End If
End With
MsgBox "Saved"
End Sub