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.


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=541841

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.


--
bgeier
------------------------------------------------------------------------
bgeier's Profile: http://www.excelforum.com/member.php...o&userid=12822
View this thread: http://www.excelforum.com/showthread...hreadid=541841

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