MsgBox Code Error

[MBlake]

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

 

[Stevie_mac]

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]

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

 

[Rob Bovey]

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

 

[MBlake]

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

 

[Rob Bovey]

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

 

[MBlake]

Hi Rob,
Sorry to be a nuisance!.

I think that at work we must have AUTOSAVE installed as an AddIn as I am not
using it in the file I am working on. The error gets highlighted at the
line - msg = MsgBox("Do you want to enter another record?", _
vbYesNo)

The only autosave funtion Ihave is when the user click a 'Close' button a
piece of code runs to save the file

Sub Sheet1ExitSystem()
'
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub


Cheers,
Mickey

 

[Rob Bovey]

I think that at work we must have AUTOSAVE installed as an AddIn as I am
not using it in the file I am working on. The error gets highlighted at
the line - msg = MsgBox("Do you want to enter another record?", _
vbYesNo)

Hi Mickey,

The only thing that can cause this error is a MISSING reference. I'd
double-check to be sure you've really unchecked the reference to
autosave.xla.

--
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

 

[MBlake]

Hi Rob,
I finally figured it out but have no idea why the MISSING referred to
AUTOSAVE. I added the line > Dim response As VbMsgBoxResult < and it now
works.

Mickey