Why is this code not working on odd occasions?

[kidwispa]

Hi all,

I have the below code relating to a userform I have set up.

What should happen:

- The user completes the form. If any of the fields are not completed then a message box is displayed informing them that the form is incomplete. They are then returned to the form to continue inputting information.
- Once the form is complete the information contained in the various text and comboboxes is added to the row after the last row of data on a worksheet within the workbook called "Data".
- Once this is complete, the user is shown a message box that states the claim has been added successfully. They are then asked if they want to add another claim - if they click "yes", they are returned to a blank version of the userform, if "no" then the messagebox is closed.

My problem is that for some reason every now and again when agents are adding claims they are not showing on the "data" sheet (even though they have seen the messagebox stating that the claim has been added successfully), so have to be input again. The file is not a shared workbook (as I only wanted one person to have access at a time) but many people use this form and I can't for the life of me work out how or why this command fails sometimes.

Here is the code I am using:

Private Sub CommandButton1_Click()
Dim lastrow As Object
Dim flag As Boolean
Worksheets("Data").Unprotect Password:="Password1"
flag = False
If TextBox1.Text = "" Then
flag = True
End If
If ComboBox7.Text = "" Then
flag = True
End If
If ComboBox8.Text = "" Then
flag = True
End If
If TextBox7.Text = "" Then
flag = True
End If
If ComboBox3.Text = "" Then
flag = True
End If
If TextBox25.Text = "" Then
flag = True
End If
If TextBox28.Text = "" Then
flag = True
End If
If TextBox27.Text = "" Then
flag = True
End If
If TextBox53.Text = "" Then
flag = True
End If
If TextBox26.Text = "" Then
flag = True
End If
If TextBox2.Text = "" Then
flag = True
End If
If TextBox3.Text = "" Then
flag = True
End If
If TextBox8.Text = "" Then
flag = True
End If
If ComboBox9.Text = "" Then
flag = True
End If
If TextBox10.Text = "" Then
flag = True
End If
If TextBox12.Text = "" Then
flag = True
End If
If TextBox14.Text = "" Then
flag = True
End If
If ComboBox4.Text = "" Then
flag = True
End If
If TextBox11.Text = "" Then
flag = True
End If
If TextBox52.Text = "" Then
flag = True
End If
If TextBox42.Text = "" Then
flag = True
End If
If TextBox29.Text = "" Then
flag = True
End If
If flag = False Then
 
Set lastrow = Sheet5.Range("a65536").End(xlUp)
lastrow.Offset(1, 0).Value = CDate(TextBox1.Value)
lastrow.Offset(1, 1).Value = ComboBox7.Text
lastrow.Offset(1, 4).Value = ComboBox8.Text
lastrow.Offset(1, 5).Value = TextBox7.Text
lastrow.Offset(1, 6).Value = ComboBox3.Text
lastrow.Offset(1, 7).Value = TextBox25.Text
lastrow.Offset(1, 9).Value = TextBox28.Text
lastrow.Offset(1, 10).Value = TextBox27.Text
lastrow.Offset(1, 11).Value = TextBox53.Text
lastrow.Offset(1, 8).Value = TextBox26.Text
lastrow.Offset(1, 12).Value = TextBox30.Text
lastrow.Offset(1, 14).Value = TextBox2.Text
lastrow.Offset(1, 15).Value = TextBox3.Text
lastrow.Offset(1, 16).Value = TextBox29.Text
lastrow.Offset(1, 17).Value = CDate(TextBox8.Value)
lastrow.Offset(1, 18).Value = ComboBox9.Text
lastrow.Offset(1, 19).Value = TextBox10.Text
lastrow.Offset(1, 20).Value = Label15.Caption
lastrow.Offset(1, 21).Value = TextBox11.Text
lastrow.Offset(1, 22).Value = Label20.Caption
lastrow.Offset(1, 23).Value = TextBox12.Text
lastrow.Offset(1, 24).Value = TextBox13.Text
lastrow.Offset(1, 25).Value = TextBox14.Text
lastrow.Offset(1, 26).Value = ComboBox4.Text
lastrow.Offset(1, 39).Value = TextBox52.Text
lastrow.Offset(1, 28).Value = TextBox42.Text
Worksheets("Data").Protect Password:="Password1"
MsgBox "Insurance Claim ADDED"
response = MsgBox("Do you want to enter another insurance claim?", vbYesNo)
If response = vbYes Then
ComboBox7.SetFocus
TextBox1.Text = Format(Now(), "DD-MM-YYYY")
ComboBox7.Text = ""
ComboBox8.Text = ""
TextBox7.Text = ""
ComboBox3.Text = ""
TextBox25.Text = ""
TextBox28.Text = ""
TextBox27.Text = ""
TextBox53.Text = ""
TextBox26.Text = ""
TextBox30.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox29.Text = ""
TextBox8.Text = ""
ComboBox9.Text = ""
TextBox10.Text = ""
Label15.Caption = ""
TextBox11.Text = ""
Label20.Caption = ""
TextBox12.Text = ""
TextBox13.Text = ""
TextBox14.Text = ""
ComboBox4.Text = ""
TextBox52.Text = ""
TextBox42.Text = ""
Else
Unload Me
End If
Else
MsgBox "Claim Form Incomplete"
End If
End Sub

Are there any glaringly obvious mistakes that I should try to correct, or is it something else?

Any help would be greatly appreciated!!

 

[Andrew Poulsom]

Hi Andrew,

Thanks for the suggestion - how is this done?

Like this?

Private Sub Workbook_Open()
    If Me.ReadOnly = True Then
       MsgBox "This workbook is read only. Please try again later."
       Me.Close
    End If
End Sub

 

[kidwispa]

Hi JackDanIce,

I have tested the code you gave in a copy of my workbook but for some reason it's added the data to row 40,710 of the sheet instead of the last row (which was row 270)... the code I was using originally has been giving me the answers that I needed - it's just that for some reason every now and again it's not saving the claims as they are added. Thanks for the suggestion though...

 

[kidwispa]

Like this?

Private Sub Workbook_Open()
    If Me.ReadOnly = True Then
       MsgBox "This workbook is read only. Please try again later."
       Me.Close
    End If
End Sub

This may sound dense but do I put the above code in "This Workbook" or somewhere else?

 

[JackDanIce]

You must have something in row 40709 - if you compare the way you calculate the first empty row after the last used row in your original code to how I do it in mine, it's virtually idential

Range("A65536").End(xlUp)

Is the same as Range("A" & Rows.Count).End(xlUp)

Because Rows.Count = 65536 (i.e. total number of available rows in the sheet)

 

[Andrew Poulsom]

This may sound dense but do I put the above code in "This Workbook" or somewhere else?

Yes, in the ThisWorkbook module.

 

[kidwispa]

You must have something in row 40709 - if you compare the way you calculate the first empty row after the last used row in your original code to how I do it in mine, it's virtually idential

Range("A65536").End(xlUp)

Is the same as Range("A" & Rows.Count).End(xlUp)

Because Rows.Count = 65536 (i.e. total number of available rows in the sheet)

There's nothing in row 40709 that I can see (could it be conditional formatting?) - I've used Ctrl & left/right arrows to check but doesn't appear that there's anything there (nor in any of the rows between that and 270)

 

[kidwispa]

Yes, in the ThisWorkbook module.

Thanks again for your patience Andrew (and to everyone else who has been helping me on here) - I am keeping a record of all VBA code I'm learning on here to try to make sure I don't ask the same thing twice!

Will try your suggestion and see if that helps