Thread: Run-Time Error '1004'

My userform was comming up in Excel and entering data properly when the dta
was typed in the combo box. I then tried to enter the code in to fill in the
options in the drop down menus. Now I can't even open the form!

The code when you click on the button to open the form hasn't changed:
Private Sub cmdBringUpForm_Click()
UserForm1.Show
End Sub

So why am I now getting Run-time error '1004' Method 'Range'of
object'_worksheet' failed ??

I did have to insert a new worksheet into the Excel document that contained
the lists of options... could this have affected the opening of the form and
if so how do I correct it?

The help is greatly appreciated! :-)

Do you get a path/file error when you try to save it?
You may have a corrupted form.

No I can save things just fine.

"bgeier" wrote:

>
> Do you get a path/file error when you try to save it?
> You may have a corrupted form.
>
>
> --
> bgeier
> ------------------------------------------------------------------------
> bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
> View this thread: http://www.excelforum.com/showthread...hreadid=541841
>
>

Do you have any initialization code under that userform module?

I'd look to see if there was something wrong in:
Private Sub UserForm_Initialize()

Christine wrote:
>
> My userform was comming up in Excel and entering data properly when the dta
> was typed in the combo box. I then tried to enter the code in to fill in the
> options in the drop down menus. Now I can't even open the form!
>
> The code when you click on the button to open the form hasn't changed:
> Private Sub cmdBringUpForm_Click()
> UserForm1.Show
> End Sub
>
> So why am I now getting Run-time error '1004' Method 'Range'of
> object'_worksheet' failed ??
>
> I did have to insert a new worksheet into the Excel document that contained
> the lists of options... could this have affected the opening of the form and
> if so how do I correct it?
>
> The help is greatly appreciated! :-)

--

Dave Peterson

I do - not that I would even begin to know where I screwed it up.

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


--> Do you see anything wrong with that?

"Dave Peterson" wrote:

> Do you have any initialization code under that userform module?
>
> I'd look to see if there was something wrong in:
> Private Sub UserForm_Initialize()
>
> Christine wrote:
> >
> > My userform was comming up in Excel and entering data properly when the dta
> > was typed in the combo box. I then tried to enter the code in to fill in the
> > options in the drop down menus. Now I can't even open the form!
> >
> > The code when you click on the button to open the form hasn't changed:
> > Private Sub cmdBringUpForm_Click()
> > UserForm1.Show
> > End Sub
> >
> > So why am I now getting Run-time error '1004' Method 'Range'of
> > object'_worksheet' failed ??
> >
> > I did have to insert a new worksheet into the Excel document that contained
> > the lists of options... could this have affected the opening of the form and
> > if so how do I correct it?
> >
> > The help is greatly appreciated! :-)
>
> --
>
> Dave Peterson
>

This looks very strange:

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

why is that 1.1?

I'm betting that the dot should be a comma????

Christine wrote:
>
> I do - not that I would even begin to know where I screwed it up.
>
> 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
>
> --> Do you see anything wrong with that?
>
> "Dave Peterson" wrote:
>
> > Do you have any initialization code under that userform module?
> >
> > I'd look to see if there was something wrong in:
> > Private Sub UserForm_Initialize()
> >
> > Christine wrote:
> > >
> > > My userform was comming up in Excel and entering data properly when the dta
> > > was typed in the combo box. I then tried to enter the code in to fill in the
> > > options in the drop down menus. Now I can't even open the form!
> > >
> > > The code when you click on the button to open the form hasn't changed:
> > > Private Sub cmdBringUpForm_Click()
> > > UserForm1.Show
> > > End Sub
> > >
> > > So why am I now getting Run-time error '1004' Method 'Range'of
> > > object'_worksheet' failed ??
> > >
> > > I did have to insert a new worksheet into the Excel document that contained
> > > the lists of options... could this have affected the opening of the form and
> > > if so how do I correct it?
> > >
> > > The help is greatly appreciated! :-)
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

You guessed right, that should be a comma - however when i make that change
and save the changes it still doesn't change the error message :-( Although
I'm sure that got me one step closer thanks!


"Dave Peterson" wrote:

> This looks very strange:
>
> 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
>
> why is that 1.1?
>
> I'm betting that the dot should be a comma????
>
> Christine wrote:
> >
> > I do - not that I would even begin to know where I screwed it up.
> >
> > 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
> >
> > --> Do you see anything wrong with that?
> >
> > "Dave Peterson" wrote:
> >
> > > Do you have any initialization code under that userform module?
> > >
> > > I'd look to see if there was something wrong in:
> > > Private Sub UserForm_Initialize()
> > >
> > > Christine wrote:
> > > >
> > > > My userform was comming up in Excel and entering data properly when the dta
> > > > was typed in the combo box. I then tried to enter the code in to fill in the
> > > > options in the drop down menus. Now I can't even open the form!
> > > >
> > > > The code when you click on the button to open the form hasn't changed:
> > > > Private Sub cmdBringUpForm_Click()
> > > > UserForm1.Show
> > > > End Sub
> > > >
> > > > So why am I now getting Run-time error '1004' Method 'Range'of
> > > > object'_worksheet' failed ??
> > > >
> > > > I did have to insert a new worksheet into the Excel document that contained
> > > > the lists of options... could this have affected the opening of the form and
> > > > if so how do I correct it?
> > > >
> > > > The help is greatly appreciated! :-)
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>

Did you change the line "cOriginator.Offset(0.1).Value" to (0,1) also?

Have you tried stepping through the initialization one line at a time (using F8?)
this will tell you exactly where the error occurs. It can be VERY time consuming, but will tell you where the error occurs.

I didn't even know that was possible... I'm totally new to VBA if you haven't
guessed and trying to put something together for work. Thanks so much I'll
give that a try.

"bgeier" wrote:

>
> Did you change the line "cOriginator.Offset(0.1).Value" to (0,1) also?
>
> Have you tried stepping through the initialization one line at a time
> (using F8?)
> this will tell you exactly where the error occurs. It can be VERY time
> consuming, but will tell you where the error occurs.
>
>
> --
> bgeier
> ------------------------------------------------------------------------
> bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
> View this thread: http://www.excelforum.com/showthread...hreadid=541841
>
>