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

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
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

S
  • Solved
User Form Textbox looping
2
Replies
12
Views
658
sjtjr73
S
M
  • Question
Add in VBA Code to check zip code against pre-determined list
Replies
0
Views
197
mikefisher
M
N
  • Question
Select Method of Range Class Failed with Table
Replies
4
Views
266
NorthbyNorthwest
N
A
  • Question
Cannot enter text in textbox on form
Replies
3
Views
153
Anthony47
A
A
  • Solved
Application-defined or object defined Error when Filtering Listbox
Replies
4
Views
170
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,216,073
Messages
6,128,634
Members
449,460
Latest member
jgharbawi

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