| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox Code Error
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" wrote in message .. . 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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox Code Error
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" wrote in message ... 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" wrote in message .. . 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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox Code Error
"MBlake" wrote in message
... 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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox Code Error
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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
MsgBox Code Error
"MBlake" wrote in message
... 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Msgbox Code Help Please.... | Excel Discussion (Misc queries) | |||
| VBA code using if then and msgbox | Excel Discussion (Misc queries) | |||
| msgbox on error | Excel Discussion (Misc queries) | |||
| inputbox msgbox error | Excel Programming | |||
Hybrid Mode
