Run Time Error 1004

G

Guest

Sorry, yes this question was asked previously but its still not solved. My
user form would enter data correctly, then I tried to add in drop down lists
and now I get this error:
Run-time error '1004':
Method 'Range of object'_Worksheet'failed

Some helpful people have determined the problem is probably in my
initilization code- but I can't find the error -- Can you??

Private Sub UserForm_Initialize()
Dim cClient As Range
Dim cCategory As Range
Dim cSubCategory As Range
Dim cCompetency As Range
Dim cIndustry As Range
Dim cOriginator As Range
Dim cConfidentiality As Range
Dim cIssue As Range
Dim cAdditionalEditors As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cClient In ws.Range("ClientList")
With Me.cbClient
..AddItem cClient.Value
..List(.ListCount - 1, 1) = cClient.Offset(0.1).Value
End With
Next cClient

For Each cCategory In ws.Range("CategoryList")
With Me.cbCategory
..AddItem cCategory.Value
..List(.ListCount - 1, 1) = cCategory.Offset(0, 1).Value
End With
Next cCategory

For Each cSubCategory In ws.Range("SubCategoryList")
With Me.cbSubCategory
..AddItem cSubCategory.Value
..List(.ListCount - 1, 1) = cSubCategory.Offset(0, 1).Value
End With
Next cSubCategory

For Each cCompetency In ws.Range("CompetencyList")
With Me.cbCompetency
..AddItem cCompetency.Value
..List(.ListCount - 1, 1) = cCompetency.Offset(0, 1).Value
End With
Next cCompetency

For Each cIndustry In ws.Range("IndustryList")
With Me.cbIndustry
..AddItem cIndustry.Value
..List(.ListCount - 1, 1) = cIndustry.Offset(0, 1).Value
End With
Next cIndustry

For Each cOriginator In ws.Range("OriginatorList")
With Me.cbOriginator
..AddItem cOriginator.Value
..List(.ListCount - 1.1) = cOriginator.Offset(0.1).Value
End With
Next cOriginator

For Each cConfidentiality In ws.Range("ConfidentialityList")
With Me.cbConfidentiality
..AddItem cConfidentiality.Value
..List(.ListCount - 1, 1) = cConfidentiality.Offset(0, 1).Value
End With
Next cConfidentiality

For Each cIssue In ws.Range("IssueList")
With Me.cbClientBusinessIssue
..AddItem cIssue.Value
..List(.ListCount - 1, 1) = cIssue.Offset(0, 1).Value
End With
Next cIssue

For Each cAdditionalEditors In ws.Range("AdditionalEditorsList")
With Me.cbAdditionalEditors
..AddItem cAdditionalEditors.Value
..List(.ListCount - 1, 1) = cAdditionalEditors.Offset(0, 1).Value
End With
Next cAdditionalEditors

Me.txtDate.Value = Format(Date, "Short Date")
Me.txtTitle.SetFocus
End Sub
 
G

Guest

It actually comes up in a different section the one where I try to bring up
the form

Private Sub cmdBringUpForm_Click()
UserForm1.Show
End Sub

It highlights the middle line User.... but I've used this command many other
times and its never been an issue.
 
D

Dave Peterson

You have another guess at the first thread.
Sorry, yes this question was asked previously but its still not solved. My
user form would enter data correctly, then I tried to add in drop down lists
and now I get this error:
Run-time error '1004':
Method 'Range of object'_Worksheet'failed

Some helpful people have determined the problem is probably in my
initilization code- but I can't find the error -- Can you??

Private Sub UserForm_Initialize()
Dim cClient As Range
Dim cCategory As Range
Dim cSubCategory As Range
Dim cCompetency As Range
Dim cIndustry As Range
Dim cOriginator As Range
Dim cConfidentiality As Range
Dim cIssue As Range
Dim cAdditionalEditors As Range
Dim ws As Worksheet
Set ws = Worksheets("LookupLists")

For Each cClient In ws.Range("ClientList")
With Me.cbClient
.AddItem cClient.Value
.List(.ListCount - 1, 1) = cClient.Offset(0.1).Value
End With
Next cClient

For Each cCategory In ws.Range("CategoryList")
With Me.cbCategory
.AddItem cCategory.Value
.List(.ListCount - 1, 1) = cCategory.Offset(0, 1).Value
End With
Next cCategory

For Each cSubCategory In ws.Range("SubCategoryList")
With Me.cbSubCategory
.AddItem cSubCategory.Value
.List(.ListCount - 1, 1) = cSubCategory.Offset(0, 1).Value
End With
Next cSubCategory

For Each cCompetency In ws.Range("CompetencyList")
With Me.cbCompetency
.AddItem cCompetency.Value
.List(.ListCount - 1, 1) = cCompetency.Offset(0, 1).Value
End With
Next cCompetency

For Each cIndustry In ws.Range("IndustryList")
With Me.cbIndustry
.AddItem cIndustry.Value
.List(.ListCount - 1, 1) = cIndustry.Offset(0, 1).Value
End With
Next cIndustry

For Each cOriginator In ws.Range("OriginatorList")
With Me.cbOriginator
.AddItem cOriginator.Value
.List(.ListCount - 1.1) = cOriginator.Offset(0.1).Value
End With
Next cOriginator

For Each cConfidentiality In ws.Range("ConfidentialityList")
With Me.cbConfidentiality
.AddItem cConfidentiality.Value
.List(.ListCount - 1, 1) = cConfidentiality.Offset(0, 1).Value
End With
Next cConfidentiality

For Each cIssue In ws.Range("IssueList")
With Me.cbClientBusinessIssue
.AddItem cIssue.Value
.List(.ListCount - 1, 1) = cIssue.Offset(0, 1).Value
End With
Next cIssue

For Each cAdditionalEditors In ws.Range("AdditionalEditorsList")
With Me.cbAdditionalEditors
.AddItem cAdditionalEditors.Value
.List(.ListCount - 1, 1) = cAdditionalEditors.Offset(0, 1).Value
End With
Next cAdditionalEditors

Me.txtDate.Value = Format(Date, "Short Date")
Me.txtTitle.SetFocus
End Sub
Click to expand...
 
G

Guest

Your guess was right in that
For Each cOriginator In ws.Range("OriginatorList")
With Me.cbOriginator
..AddItem cOriginator.Value
..List(.ListCount - 1.1) = cOriginator.Offset(0.1).Value

That last line should be 1,1 not 1.1- but this change still doesn't get rid
of my error message. I'm sure it got me one step closer though thanks!
 
G

Guest

I have Option Explict on to make sure i spell things right... and I went
through and double checked all variables and lists should be consistantly
spelled the same.
 
D

Dave Peterson

In both spots, too!
Your guess was right in that
For Each cOriginator In ws.Range("OriginatorList")
With Me.cbOriginator
.AddItem cOriginator.Value
.List(.ListCount - 1.1) = cOriginator.Offset(0.1).Value

That last line should be 1,1 not 1.1- but this change still doesn't get rid
of my error message. I'm sure it got me one step closer though thanks!
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Error Help - Method "Range" of object "_Worksheet" failed. 3
list box remove item by combobox 4
Compile Error with User from 3
Modifing example code 2
Combobox number formats 4
Form Question 1
Need help in creating dynamic drop down list in userform 2
Remove Item from ListBox1 2

Top