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

 

[mrhstn]

You dimensioned iRow but never gave it a value.

 

[djmadmike]

You dimensioned iRow but never gave it a value.

Sorry noob here ...I've been piece mailing tutorials online

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

Isnt the value stated as "Me.OwnerComboBox.Value"?

Confused

 

[mrhstn]

The problem is this bit

ws.Cells(iRow, 2).Value

iRow isn't set to anything so the worksheets (ws) cells(iRow, 2) won't be found, hence 1004 runtime error, it doesn't exist...

 

[djmadmike]

The problem is this bit

ws.Cells(iRow, 2).Value

iRow isn't set to anything so the worksheets (ws) cells(iRow, 2) won't be found, hence 1004 runtime error, it doesn't exist...

ah...would this work? If the sheet where I want to populate the data is sheet1?

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("sheet1")

 

[djmadmike]

Ok that didnt work

 

[mrhstn]

ws may also need set, guessing it's a database sheet.

iRow will need set, guessing that will be the next available blank row.

I think you have the dims already set but I've included them, you will need to adjust this

Dim ws As Worksheet 'Which I think is set
Dim iRow As Long ' Definitiely set


Set ws = Sheets("Name_Of_Sheet_Goes_Here")
iRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

 

[djmadmike]

ws may also need set, guessing it's a database sheet.

iRow will need set, guessing that will be the next available blank row.

I think you have the dims already set but I've included them, you will need to adjust this

Dim ws As Worksheet 'Which I think is set
Dim iRow As Long ' Definitiely set


Set ws = Sheets("Name_Of_Sheet_Goes_Here")
iRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1

MY HERO

It worked...I had to name the sheet "Sheet1" for it to work on a different sheet/tab (a blank new worksheet).

I'm trying to add it to another sheet (that I am already using) with a instead and it doesnt seem to work. Is there a sheet/tab designation method?

I tried define name under formulas but no dice.

Either way thanks!

 

[mrhstn]

If you are adding a sheet then you can refer to the newly added sheet like so...

' If you are adding a new worksheet, try..
Worksheets.Add
Set ws = ActiveSheet

If you know the name of the sheet then just use it's name where I've put it above

Set ws = Sheets("Name_Of_Sheet_Goes_Here")

 

[djmadmike]

yeah...

It continues to add the data to a new sheet.

The correct sheet gets ignored. When I add the name it comes up with an error. Just to be clear the name Im using is the name on the tab of the sheet that I am using.
I also notice that the data over writes the previous entry.

I added this and it worked!'find first empty row in database



Set ws = Sheets("Sheet1")
iRow = ws.Range("A" & Rows.Count).End(xlUp).Row + 1




iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, _
SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1

but the not recording the data on the main sheet is still an issue. The Tab is caled Bob