Why is this code not working on odd occasions?

K

kidwispa

Active Member
Joined
Mar 7, 2011
Messages
330
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:

Code: 
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!!

:)
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Sort by date Sort by votes
There's nothing glaringly obvious, though it it a fair it of code to check thoroughly.

All the references seem correct, I assume the names of the controls are too.

Is it possible to recreate the circumstances when it doesn't work properly?

Then you could step through the code using F8.

Doing that might help anyway.

Actually have you checked that the values from the correct textboxes/comboboxes are going to the right locations on the worksheets.

The names eg TextBox1, TextBox7 etc seem kind of anyonymous.

Perhaps renaming would clarify things a bit.:)
 
Upvote 0
Are you sure that the agent is saving the workbook after updating it? What happens if it is read-only when they open it?
 
Upvote 0
Norie said:
There's nothing glaringly obvious, though it it a fair it of code to check thoroughly.

All the references seem correct, I assume the names of the controls are too.

Is it possible to recreate the circumstances when it doesn't work properly?

Then you could step through the code using F8.

Doing that might help anyway.

Actually have you checked that the values from the correct textboxes/comboboxes are going to the right locations on the worksheets.

The names eg TextBox1, TextBox7 etc seem kind of anyonymous.

Perhaps renaming would clarify things a bit.:)
Click to expand...


Thanks for the reply Norie,

As it stands I personally have never seen this issue happen - I have asked agents who have outstanding claims why they have not been added and have been told that they have already added them, but when I go to look they aren't there. I have sat with these same people and watched them input the info and every time I've done that everything works exactly as it should! I just wanted to confirm that there were no obvious errors within the code that could cause this before I accuse people of not completing the tasks in the first place?

Would it make any difference that people are using different versions of excel to me? I'm on 2010 now (but created this file in 2003, and have it saved in .xls format) and most of my colleagues are still on 2003 (or in some cases 2002)
 
Upvote 0
What is the name of the sheet the data is going into if the flag is FALSE? Is it named Sheet5?

(Also I think you're using lastrow and offset incorrectly, but will wait for your answer first before making my suggestion...)
 
Upvote 0
Andrew Poulsom said:
Are you sure that the agent is saving the workbook after updating it? What happens if it is read-only when they open it?
Click to expand...

Hi Andrew,

I hadn't even considered that! What would be the best way to show a message if the file is opened as Read-only? I know there is the excel message that appears but there is the possibility that they arent paying any attention to it...
 
Upvote 0
JackDanIce said:
What is the name of the sheet the data is going into if the flag is FALSE? Is it named Sheet5?

(Also I think you're using lastrow and offset incorrectly, but will wait for your answer first before making my suggestion...)
Click to expand...


No the sheet is called "Data" but shows up in the project explorer as Sheet5 (Data)...
 
Last edited:
Upvote 0
You could test the Workbook's ReadOnly property in the Workbook_Open event procedure and close it if True (with a suitable message to the user).
 
Upvote 0
In your code, you have:
Code: 
Set lastrow = Sheet5.Range("a65536").End(xlUp)
Where you've defined lastrow of type object whereas I think it should be type range. Also you said it should be on the Sheet data, but in the code you reference Sheet5 (is that meant to be the index number of the sheet, i.e. Sheets(5) ? )
Anyway, see if this code works:
Code: 
Private Sub CommandButton1_Click()
Dim i As Long
Dim flag As Boolean
Worksheets("Data").Unprotect Password:="Password1"

If TextBox1.Text = "" Or _
    ComboBox7.Text = "" Or _
    ComboBox8.Text = "" Or _
    TextBox7.Text = "" Or _
    ComboBox3.Text = "" Or _
    TextBox25.Text = "" Or _
    TextBox28.Text = "" Or _
    TextBox27.Text = "" Or _
    TextBox53.Text = "" Or _
    TextBox26.Text = "" Or _
    TextBox2.Text = "" Or _
    TextBox3.Text = "" Or _
    TextBox8.Text = "" Or _
    ComboBox9.Text = "" Or _
    TextBox10.Text = "" Or _
    TextBox12.Text = "" Or _
    TextBox14.Text = "" Or _
    ComboBox4.Text = "" Or _
    TextBox11.Text = "" Or _
    TextBox52.Text = "" Or _
    TextBox42.Text = "" Or _
    TextBox29.Text = "" Then flag = True
 
If Not flag Then
    With Sheets("Data")
        i = .Range("A" & Rows.Count).End(xlUp) + 1
        .Range("A" & i).Offset(0, 0).Value = CDate(TextBox1.Value)
        .Range("A" & i).Offset(0, 1).Value = ComboBox7.Text
        .Range("A" & i).Offset(0, 4).Value = ComboBox8.Text
        .Range("A" & i).Offset(0, 5).Value = TextBox7.Text
        .Range("A" & i).Offset(0, 6).Value = ComboBox3.Text
        .Range("A" & i).Offset(0, 7).Value = TextBox25.Text
        .Range("A" & i).Offset(0, 9).Value = TextBox28.Text
        .Range("A" & i).Offset(0, 10).Value = TextBox27.Text
        .Range("A" & i).Offset(0, 11).Value = TextBox53.Text
        .Range("A" & i).Offset(0, 8).Value = TextBox26.Text
        .Range("A" & i).Offset(0, 12).Value = TextBox30.Text
        .Range("A" & i).Offset(0, 14).Value = TextBox2.Text
        .Range("A" & i).Offset(0, 15).Value = TextBox3.Text
        .Range("A" & i).Offset(0, 16).Value = TextBox29.Text
        .Range("A" & i).Offset(0, 17).Value = CDate(TextBox8.Value)
        .Range("A" & i).Offset(0, 18).Value = ComboBox9.Text
        .Range("A" & i).Offset(0, 19).Value = TextBox10.Text
        .Range("A" & i).Offset(0, 20).Value = Label15.Caption
        .Range("A" & i).Offset(0, 21).Value = TextBox11.Text
        .Range("A" & i).Offset(0, 22).Value = Label20.Caption
        .Range("A" & i).Offset(0, 23).Value = TextBox12.Text
        .Range("A" & i).Offset(0, 24).Value = TextBox13.Text
        .Range("A" & i).Offset(0, 25).Value = TextBox14.Text
        .Range("A" & i).Offset(0, 26).Value = ComboBox4.Text
        .Range("A" & i).Offset(0, 39).Value = TextBox52.Text
        .Range("A" & i).Offset(0, 28).Value = TextBox42.Text
    End With
    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
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
User form successfully ran but no results are showing
Replies
1
Views
310
rlv01
R
P
  • Solved
Code doesn't go on a new line (emptyRow)
Replies
8
Views
403
Fluff
Fluff
I
  • Solved
Show or Hide Textbox advice
Replies
8
Views
653
dmt32
D
X
  • Question Question
userform auto fill all
Replies
1
Views
406
Micron
M
P
  • Question Question
USERFORM button for UPDATE error "block if without end if"
2
Replies
16
Views
857
dmt32
D

Forum statistics

Threads
1,224,522
Messages
6,179,299
Members
452,904
Latest member
CodeMasterX

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