Need some help

[djmadmike]

I hate to just jump in with a hard question (at least for me)

I am looking to simply have a userform that will enter data onto a excel sheet.

Drop downs work fine
Prepopulating the date works fine

But when I hit submit (Ok)

I get an runtime error 1004

This is the line that is highlighted when I hit debug

ws.Cells(iRow, 2).Value = Me.OwnerComboBox.Value


The Code is below

Any hep would make you a hero in my eyes
_________________________________________________________________________
Private Sub CustomerComboBox_Change()


End Sub


Private Sub SellingSiteComboBox1_Change()


End Sub


Private Sub UserForm_Initialize()
Me.weekBox = Date


'fill combobox
Me.weekBox = Date
Me.OwnerComboBox.SetFocus




For Each blah In [Owner]
Me.OwnerComboBox.AddItem blah
Next blah

For Each blah In [RSM]
Me.RSMComboBox1.AddItem blah
Next blah



For Each blah In [Region]
Me.RegionComboBox2.AddItem blah
Next blah

For Each blah In
[list1]
Me.MfgSiteComboBox1.AddItem blah
Next blah

For Each blah In
[list2]
Me.SellingSiteComboBox1.AddItem blah
Next blah

For Each blah In
[list3]
Me.StatusComboBox1.AddItem blah
Next blah
For Each blah In
[list4]
Me.StageSalesProComboBox1.AddItem blah
Next blah

For Each blah In
[list5]
Me.MarketSegmentComboBox1.AddItem blah
Next blah

For Each blah In
[list6]
Me.SterilityComboBox1.AddItem blah
Next blah

For Each blah In
[list7]
Me.ApplicationComboBox1.AddItem blah
Next blah
For Each blah In
[list8]
Me.ProcessStageComboBox1.AddItem blah
Next blah









End Sub


Private Sub CancelButton_Click()
If MsgBox("Make Sure You SAVE Your Work!", vbQuestion + vbYesNo) <> vbNo Then

End If

Unload Me
End Sub


Private Sub ClearButton_Click()
Me.OwnerComboBox.Value = ""
Me.TextBox1.Value = ""
Me.TextBox2.Value = ""
Me.RSMComboBox1.Value = ""
Me.MoneyTextBox.Value = ""
Me.CustomerContactTextBox1.Value = ""
Me.StageSalesProComboBox1.Value = ""
Me.ApplicationComboBox1.Value = ""
Me.SterilityComboBox1.Value = ""
Me.NotesTextBox1.Value = ""
Me.RegionComboBox2.Value = ""
Me.SellingSiteComboBox1.Value = ""
Me.MfgSiteComboBox1.Value = ""
Me.StatusComboBox1.Value = ""
Me.CompetitionTextBox1.Value = ""
Me.MarketSegmentComboBox1.Value = ""
Me.ProcessStageComboBox1.Value = ""
Me.weekBox = Date
Me.weekBox.SetFocus
End Sub
Private Sub OkButton_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")


'checks to see if all fields are filled prior to submitting or sending email
'new
'Private Sub CommandButton1_Click()
' If bComplete(Me) = False Then MsgBox "Form needs completing"
'submit code
Dim bComplete As Boolean

bComplete = True

If Len(Trim(OwnerComboBox.Text)) = 0 Then bComplete = False
If bComplete Then
If Len(Trim(weekBox.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(TextBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(TextBox2.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(RSMComboBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(MoneyTextBox.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(CustomerContactTextBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(StageSalesProComboBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(ApplicationComboBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(SterilityComboBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(NotesTextBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(RegionComboBox2.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(SellingSiteComboBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(MfgSiteComboBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(StatusComboBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(CompetitionTextBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(MarketSegmentComboBox1.Text)) = 0 Then bComplete = False
End If
If bComplete Then
If Len(Trim(ProcessStageComboBox1.Text)) = 0 Then bComplete = False
End If
If Not bComplete Then
If MsgBox("Stop being lazy complete the form!", vbQuestion + vbYesNo) <> vbNo Then



Exit Sub
End If
End If




'find first empty row in database




'check for a Name number
If Trim(Me.ProcessStageComboBox1.Value) = "" Then
Me.OwnerComboBox.SetFocus


MsgBox "Please complete the form"
Exit Sub
End If


'copy the data to the database




ws.Cells(iRow, 2).Value = Me.OwnerComboBox.Value
ws.Cells(iRow, 3).Value = Me.weekBox.Value
ws.Cells(iRow, 4).Value = Me.TextBox1.Value
ws.Cells(iRow, 5).Value = Me.TextBox2.Value
ws.Cells(iRow, 6).Value = Me.RSMComboBox1.Value
ws.Cells(iRow, 7).Value = Me.MoneyTextBox.Value
ws.Cells(iRow, 8).Value = Me.CustomerContactTextBox1.Value
ws.Cells(iRow, 9).Value = Me.StageSalesProComboBox1.Value
ws.Cells(iRow, 10).Value = Me.ApplicationComboBox1.Value
ws.Cells(iRow, 11).Value = Me.SterilityComboBox1.Value
ws.Cells(iRow, 12).Value = Me.NotesTextBox1.Value
ws.Cells(iRow, 13).Value = Me.RegionComboBox2.Value
ws.Cells(iRow, 14).Value = Me.SellingSiteComboBox1.Value
ws.Cells(iRow, 15).Value = Me.MfgSiteComboBox1.Value
ws.Cells(iRow, 16).Value = Me.StatusComboBox1.Value
ws.Cells(iRow, 17).Value = Me.CompetitionTextBox1.Value
ws.Cells(iRow, 18).Value = Me.MarketSegmentComboBox1.Value
ws.Cells(iRow, 19).Value = Me.ProcessStageComboBox1.Value


MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"


End Sub

 

[djmadmike]

Oddly I tried naming it 3 other sheets that I have and it works on placing the data on all the other sheets...strange.

Yes I checked the spelling twice...

 

[djmadmike]

So I changed the name and changed it back to Bob and it worked!...strange

 

[Fluff]

Try

Set ws = Sheets("Bob")

Also, it maybe that not all of your inputs are being checked properly.
Using the first 4 lines as an example

If Len(Trim(OwnerComboBox.Text)) = 0 Then bComplete = False

if there is no value in OwnerComboBox then bComplete will be False.
This means that

If bComplete Then

is false so the code will jump to the End If & ignore

If Len(Trim(weekBox.Text)) = 0 Then bComplete = False

HTH

 

[djmadmike]

Try

Set ws = Sheets("Bob")

Also, it maybe that not all of your inputs are being checked properly.
Using the first 4 lines as an example

If Len(Trim(OwnerComboBox.Text)) = 0 Then bComplete = False

if there is no value in OwnerComboBox then bComplete will be False.
This means that

If bComplete Then

is false so the code will jump to the End If & ignore

If Len(Trim(weekBox.Text)) = 0 Then bComplete = False

HTH

will do thanks alot