MsgBox Code Error

I have the following code in a Userform that uses VBA controls. The idea is
that the below code should copy the userform data to a worksheet.
Unfortunatey the code stops at the line - response = MsgBox("Do you want to
enter another record?",

In addition no entries are copied to the worksheet, can anyone give me a
nudge with this please?

Thanks,
Mickey



Private Sub CmdbuttonSubmit_Click()
Dim LastRow As Object

Set LastRow = Sheet3.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = ComboBoxPIN.Text
LastRow.Offset(1, 1).Value = TextBox1.Text
LastRow.Offset(1, 2).Value = TextBox2.Text
LastRow.Offset(1, 3).Value = TextBox3.Text
LastRow.Offset(1, 4).Value = TextBox4.Text
LastRow.Offset(1, 5).Value = TextBox5.Text
LastRow.Offset(1, 6).Value = TextBox6.Text
LastRow.Offset(1, 7).Value = TextBox7.Text
LastRow.Offset(1, 8).Value = TextBox8.Text
LastRow.Offset(1, 9).Value = ComboBoxArea.Text
LastRow.Offset(1, 10).Value = TextBox9.Text
LastRow.Offset(1, 11).Value = ComboBoxActivity.Text
LastRow.Offset(1, 12).Value = ComboBoxOffence.Text
LastRow.Offset(1, 13).Value = TextBox10.Text
LastRow.Offset(1, 14).Value = TextBox11.Text
LastRow.Offset(1, 16).Value = CheckBoxSDet.Value
LastRow.Offset(1, 17).Value = CheckBoxPOMAN.Value
LastRow.Offset(1, 18).Value = CheckBoxANPR.Value

MsgBox "One record written to Performance Indicator System"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
ComboBoxPIN.Text = ""
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox6.Text = ""
TextBox7.Text = ""
TextBox8.Text = ""
ComboBoxArea.Text = ""
TextBox9.Text = ""
ComboBoxActivity.Text = ""
ComboBoxOffence.Text = ""
TextBox10.Text = ""
TextBox11.Text = ""
CheckBoxSDet.Value = ""
CheckBoxPOMAN.Value = ""
CheckBoxANPR.Value = ""

ComboBoxPIN.SetFocus

Else
Unload Me
End If

End Sub
End Sub

Change the 1st 2 lines to this...

Dim LastRow As Range
Set LastRow = Sheet3.Range("b65536").End(xlUp)

and ensure ComboBoxPIN.Text is not blank (as the End(xlUp) will simply take you to the same row.)




"MBlake" <mickeyblake@omitthisbtopenworld.com> wrote in message news:%23Yll%23%234SFHA.3308@TK2MSFTNGP14.phx.gbl...
>I have the following code in a Userform that uses VBA controls. The idea is that the below code should copy the
>userform data to a worksheet. Unfortunatey the code stops at the line - response = MsgBox("Do you want to enter
>another record?",
>
> In addition no entries are copied to the worksheet, can anyone give me a nudge with this please?
>
> Thanks,
> Mickey
>
>
>
> Private Sub CmdbuttonSubmit_Click()
> Dim LastRow As Object
>
> Set LastRow = Sheet3.Range("a65536").End(xlUp)
>
> LastRow.Offset(1, 0).Value = ComboBoxPIN.Text
> LastRow.Offset(1, 1).Value = TextBox1.Text
> LastRow.Offset(1, 2).Value = TextBox2.Text
> LastRow.Offset(1, 3).Value = TextBox3.Text
> LastRow.Offset(1, 4).Value = TextBox4.Text
> LastRow.Offset(1, 5).Value = TextBox5.Text
> LastRow.Offset(1, 6).Value = TextBox6.Text
> LastRow.Offset(1, 7).Value = TextBox7.Text
> LastRow.Offset(1, 8).Value = TextBox8.Text
> LastRow.Offset(1, 9).Value = ComboBoxArea.Text
> LastRow.Offset(1, 10).Value = TextBox9.Text
> LastRow.Offset(1, 11).Value = ComboBoxActivity.Text
> LastRow.Offset(1, 12).Value = ComboBoxOffence.Text
> LastRow.Offset(1, 13).Value = TextBox10.Text
> LastRow.Offset(1, 14).Value = TextBox11.Text
> LastRow.Offset(1, 16).Value = CheckBoxSDet.Value
> LastRow.Offset(1, 17).Value = CheckBoxPOMAN.Value
> LastRow.Offset(1, 18).Value = CheckBoxANPR.Value
>
> MsgBox "One record written to Performance Indicator System"
>
> response = MsgBox("Do you want to enter another record?", _
> vbYesNo)
>
> If response = vbYes Then
> ComboBoxPIN.Text = ""
> TextBox1.Text = ""
> TextBox2.Text = ""
> TextBox3.Text = ""
> TextBox4.Text = ""
> TextBox5.Text = ""
> TextBox6.Text = ""
> TextBox7.Text = ""
> TextBox8.Text = ""
> ComboBoxArea.Text = ""
> TextBox9.Text = ""
> ComboBoxActivity.Text = ""
> ComboBoxOffence.Text = ""
> TextBox10.Text = ""
> TextBox11.Text = ""
> CheckBoxSDet.Value = ""
> CheckBoxPOMAN.Value = ""
> CheckBoxANPR.Value = ""
>
> ComboBoxPIN.SetFocus
>
> Else
> Unload Me
> End If
>
> End Sub
> End Sub
>

Hi Stevie_mac,
I made the change as suggested but am still getting the same error message.
'Can't find project or library', the error is highlighted at the line -
response = MsgBox("Do you want to enter another record?", _
vbYesNo)

Any thoughts?
Mickey


"Stevie_mac" <no.email@please.com> wrote in message
news:epLQIU5SFHA.2432@TK2MSFTNGP12.phx.gbl...
> Change the 1st 2 lines to this...
>
> Dim LastRow As Range
> Set LastRow = Sheet3.Range("b65536").End(xlUp)
>
> and ensure ComboBoxPIN.Text is not blank (as the End(xlUp) will simply
> take you to the same row.)
>
>
>
>
> "MBlake" <mickeyblake@omitthisbtopenworld.com> wrote in message
> news:%23Yll%23%234SFHA.3308@TK2MSFTNGP14.phx.gbl...
>>I have the following code in a Userform that uses VBA controls. The idea
>>is that the below code should copy the userform data to a worksheet.
>>Unfortunatey the code stops at the line - response = MsgBox("Do you want
>>to enter another record?",
>>
>> In addition no entries are copied to the worksheet, can anyone give me a
>> nudge with this please?
>>
>> Thanks,
>> Mickey
>>
>>
>>
>> Private Sub CmdbuttonSubmit_Click()
>> Dim LastRow As Object
>>
>> Set LastRow = Sheet3.Range("a65536").End(xlUp)
>>
>> LastRow.Offset(1, 0).Value = ComboBoxPIN.Text
>> LastRow.Offset(1, 1).Value = TextBox1.Text
>> LastRow.Offset(1, 2).Value = TextBox2.Text
>> LastRow.Offset(1, 3).Value = TextBox3.Text
>> LastRow.Offset(1, 4).Value = TextBox4.Text
>> LastRow.Offset(1, 5).Value = TextBox5.Text
>> LastRow.Offset(1, 6).Value = TextBox6.Text
>> LastRow.Offset(1, 7).Value = TextBox7.Text
>> LastRow.Offset(1, 8).Value = TextBox8.Text
>> LastRow.Offset(1, 9).Value = ComboBoxArea.Text
>> LastRow.Offset(1, 10).Value = TextBox9.Text
>> LastRow.Offset(1, 11).Value = ComboBoxActivity.Text
>> LastRow.Offset(1, 12).Value = ComboBoxOffence.Text
>> LastRow.Offset(1, 13).Value = TextBox10.Text
>> LastRow.Offset(1, 14).Value = TextBox11.Text
>> LastRow.Offset(1, 16).Value = CheckBoxSDet.Value
>> LastRow.Offset(1, 17).Value = CheckBoxPOMAN.Value
>> LastRow.Offset(1, 18).Value = CheckBoxANPR.Value
>>
>> MsgBox "One record written to Performance Indicator System"
>>
>> response = MsgBox("Do you want to enter another record?", _
>> vbYesNo)
>>
>> If response = vbYes Then
>> ComboBoxPIN.Text = ""
>> TextBox1.Text = ""
>> TextBox2.Text = ""
>> TextBox3.Text = ""
>> TextBox4.Text = ""
>> TextBox5.Text = ""
>> TextBox6.Text = ""
>> TextBox7.Text = ""
>> TextBox8.Text = ""
>> ComboBoxArea.Text = ""
>> TextBox9.Text = ""
>> ComboBoxActivity.Text = ""
>> ComboBoxOffence.Text = ""
>> TextBox10.Text = ""
>> TextBox11.Text = ""
>> CheckBoxSDet.Value = ""
>> CheckBoxPOMAN.Value = ""
>> CheckBoxANPR.Value = ""
>>
>> ComboBoxPIN.SetFocus
>>
>> Else
>> Unload Me
>> End If
>>
>> End Sub
>> End Sub
>>
>
>

"MBlake" <mickeyblake@omitthisbtopenworld.com> wrote in message
news:%23iSKW8CTFHA.2916@TK2MSFTNGP15.phx.gbl...
> Hi Stevie_mac,
> I made the change as suggested but am still getting the same error
> message. 'Can't find project or library', the error is highlighted at the
> line - response = MsgBox("Do you want to enter another record?", _
> vbYesNo)

Hi Mickey,

Choose Tools/References from the Visual Basic Editor menu. You should
notice that one of the selected references is prefixed with "MISSING". This
is the source of the error (and it frequently has no connection with the
line of code that VBA flags with the error).

If you aren't using this reference you can just uncheck it. If you are
using this reference you'll need to figure out why your project can't find
it any longer.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm

Hi Rob,
Thanks again. The MISSING was AUTOSAVE.XLA, I have unchecked it but the
error persists. I am at home working on this file so perhaps something is
different with the Excel installation at work.

I will try the process at work in the morning.

Thanks again,
Mickey

"MBlake" <mickeyblake@omitthisbtopenworld.com> wrote in message
news:eAfY7TDTFHA.3980@TK2MSFTNGP12.phx.gbl...
> Hi Rob,
> Thanks again. The MISSING was AUTOSAVE.XLA, I have unchecked it but the
> error persists. I am at home working on this file so perhaps something is
> different with the Excel installation at work.

Hi Mickey,

If the error persists after removing the reference it means that you are
using that reference. However, after removing the reference, the error
should flag a line of code that actually relates to the reference you
unchecked. AUTOSAVE.XLA is the file that implements the Autosave Add-in. You
will need to make sure this add-in is installed and loaded if you will be
using it in your code.

--
Rob Bovey, Excel MVP
Application Professionals
http://www.appspro.com/

* Take your Excel development skills to the next level.
* Professional Excel Development
http://www.appspro.com/Books/Books.htm