Need some help

D

djmadmike

New Member
Joined
Aug 12, 2017
Messages
9
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
 
Sort by date Sort by votes
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...
 
Upvote 0

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
Try
Code: 
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
Code: 
If Len(Trim(OwnerComboBox.Text)) = 0 Then bComplete = False
if there is no value in OwnerComboBox then bComplete will be False.
This means that
Code: 
If bComplete Then
is false so the code will jump to the End If & ignore
Code: 
If Len(Trim(weekBox.Text)) = 0 Then bComplete = False
HTH
 
Upvote 0
Fluff said:
Try
Code: 
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
Code: 
If Len(Trim(OwnerComboBox.Text)) = 0 Then bComplete = False
if there is no value in OwnerComboBox then bComplete will be False.
This means that
Code: 
If bComplete Then
is false so the code will jump to the End If & ignore
Code: 
If Len(Trim(weekBox.Text)) = 0 Then bComplete = False
HTH
Click to expand...

will do thanks alot
 
Upvote 0
You must log in or register to reply here.

Similar threads

WERNER SLABBERT
  • Question Question
Run-time error 70 after code changed
Replies
3
Views
72
RoryA
RoryA
K
  • Question Question
I was try to load data To List Box And Transfer data to Sheet2.... (Not work this userform)
Replies
4
Views
192
krunal123
K
K
  • Question Question
Help Needed
2
Replies
13
Views
319
kwavic
K
S
  • Solved
User Form Textbox looping
2
Replies
12
Views
943
sjtjr73
S
K
  • Question Question
create fixtures but 2 vs 2
Replies
7
Views
155
kerm007
K

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top