Conditional drop down list

I'm using code that Doug Robbins provided to a previous post. I'm very
fortunate to have stumble across that little gold nugget. Thanks Doug.

The code is using a table in word to fill in the drop down boxes on a user
form. I'm having a problem when I try to show my user form. I get the error
message "Run-time error '4198': Command failed." It appears that I am losing
the focus on my template/document and the focus is now on the table document.
Any ideas on how I can work around this?

Thanks. Amy

Doug
Wed Oct 26 14:41:39 CDT 2005

If you click on Debug or look at the code, it a particular line highlighted?

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Amy" <Amy@discussions.microsoft.com> wrote in message
news:788A8860-D783-4947-9AA7-C8EA97E16042@microsoft.com...
> I'm using code that Doug Robbins provided to a previous post. I'm very
> fortunate to have stumble across that little gold nugget. Thanks Doug.
>
> The code is using a table in word to fill in the drop down boxes on a user
> form. I'm having a problem when I try to show my user form. I get the
> error
> message "Run-time error '4198': Command failed." It appears that I am
> losing
> the focus on my template/document and the focus is now on the table
> document.
> Any ideas on how I can work around this?
>
> Thanks. Amy

Amy
Wed Oct 26 14:51:19 CDT 2005

Yes, frmCurrentDept.Show is highlighted. I have this one line in a procedure
and this procedure is in the "Run macro on exit" of a form field.

"Doug Robbins - Word MVP" wrote:

> If you click on Debug or look at the code, it a particular line highlighted?
>
> --
> Hope this helps.
>
> Please reply to the newsgroup unless you wish to avail yourself of my
> services on a paid consulting basis.
>
> Doug Robbins - Word MVP
>
> "Amy" <Amy@discussions.microsoft.com> wrote in message
> news:788A8860-D783-4947-9AA7-C8EA97E16042@microsoft.com...
> > I'm using code that Doug Robbins provided to a previous post. I'm very
> > fortunate to have stumble across that little gold nugget. Thanks Doug.
> >
> > The code is using a table in word to fill in the drop down boxes on a user
> > form. I'm having a problem when I try to show my user form. I get the
> > error
> > message "Run-time error '4198': Command failed." It appears that I am
> > losing
> > the focus on my template/document and the focus is now on the table
> > document.
> > Any ideas on how I can work around this?
> >
> > Thanks. Amy
>
>
>

Jean-Guy
Wed Oct 26 15:29:07 CDT 2005

Amy was telling us:
Amy nous racontait que :

> Yes, frmCurrentDept.Show is highlighted. I have this one line in a
> procedure and this procedure is in the "Run macro on exit" of a form
> field.

This means that the error is somewhere in the code that is run when the
userform is initialized or activated. When you get to the highlighted line,
Hit F8 to step into the userform code to see which line in the Initialize or
Activate event is causing the problem.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Amy
Thu Oct 27 10:20:12 CDT 2005

It then goes to my Private Sub UserForm_Initialize()

I've added On Error Resume Next at the beginning of this Private Sub and it
now shows me my userform and allows me to make my selections. Once I close
my user form, how do I get back to my original document to insert my
selections from the userform. Now I get the error 5941 - requested member of
the collection does not exist. Since it's a template, and it creates a new
document, how do I point it back to my document.

Thanks. Amy

"Jean-Guy Marcil" wrote:

> Amy was telling us:
> Amy nous racontait que :
>
> > Yes, frmCurrentDept.Show is highlighted. I have this one line in a
> > procedure and this procedure is in the "Run macro on exit" of a form
> > field.
>
> This means that the error is somewhere in the code that is run when the
> userform is initialized or activated. When you get to the highlighted line,
> Hit F8 to step into the userform code to see which line in the Initialize or
> Activate event is causing the problem.
>
> --
> Salut!
> _______________________________________
> Jean-Guy Marcil - Word MVP
> jmarcilREMOVE@CAPSsympatico.caTHISTOO
> Word MVP site: http://www.word.mvps.org
>
>
>

Jean-Guy
Thu Oct 27 11:10:36 CDT 2005

Amy was telling us:
Amy nous racontait que :

> It then goes to my Private Sub UserForm_Initialize()
>
> I've added On Error Resume Next at the beginning of this Private Sub

Not a good idea to bypass errors that you do not understand with this line
of code.
Post your Sub so we can look at it.

> and it now shows me my userform and allows me to make my selections.
> Once I close my user form, how do I get back to my original document
> to insert my selections from the userform. Now I get the error 5941

It depends on what is going on and how you close the userform.
Do you have code behind and OK button that does that?
Where is the code that takes the info from the userform and then inserts it
in the document?
Post that code if you need help with it.

> - requested member of the collection does not exist. Since it's a
> template, and it creates a new document, how do I point it back to my
> document.

Again, this depends on what you do before you launch the userform.
Post the code that launches the userform.

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Amy
Thu Oct 27 12:07:03 CDT 2005

Thanks for looking into this. In a separate procedure under Modules I have:
frmCurrentDept.Show.

And this is the code for my user form:
Option Explicit

Dim sourcedoc As Document
Dim intOne As Integer
Dim intTwo As Integer
Dim rngDept As Range
Dim rngTitle As Range

Private Sub cboDept_Change()

'Remove any existing items in cboTitle
If cboTitle.ListCount >= 1 Then
For intTwo = cboTitle.ListCount To 1 Step -1
cboTitle.RemoveItem (intTwo)
Next intTwo
End If

Application.ScreenUpdating = False
'Open the file containing the form data details
Set sourcedoc = Documents.Open(FileName:="G:\HRData102605.doc")
'"Q\Departments\Human Resources\Readonly\HRData.doc")
intTwo = cboDept.ListIndex + 2
For intOne = 1 To sourcedoc.Tables(1).Cell(intTwo, 2).Range.Paragraphs.Count
Set rngTitle = sourcedoc.Tables(1).Cell(intTwo,
2).Range.Paragraphs(intOne).Range
rngTitle.End = rngTitle.End - 1
cboTitle.AddItem rngTitle
Next intOne

End Sub

Private Sub UserForm_initialize()

On Error Resume Next

Application.ScreenUpdating = False
'open the file containing the form data
Set sourcedoc = Documents.Open(FileName:="G:\HRData102605.doc")
'"Q\Departments\Human Resources\Readonly\HRData.doc")
For intOne = 2 To sourcedoc.Tables(1).Rows.Count
Set rngDept = sourcedoc.Tables(1).Cell(intOne, 1).Range
rngDept.End = rngDept.End - 1
cboDept.AddItem rngDept
Next intOne
'close the file containing the department details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges

End Sub

Private Sub cmdClose_Click()

ActiveDocument.FormFields("bkDept1").Result = cboDept.Value
ActiveDocument.FormFields("bkTitle1").Result = cboTitle.Value

Unload Me

End Sub


"Jean-Guy Marcil" wrote:

> Amy was telling us:
> Amy nous racontait que :
>
> > It then goes to my Private Sub UserForm_Initialize()
> >
> > I've added On Error Resume Next at the beginning of this Private Sub
>
> Not a good idea to bypass errors that you do not understand with this line
> of code.
> Post your Sub so we can look at it.
>
> > and it now shows me my userform and allows me to make my selections.
> > Once I close my user form, how do I get back to my original document
> > to insert my selections from the userform. Now I get the error 5941
>
> It depends on what is going on and how you close the userform.
> Do you have code behind and OK button that does that?
> Where is the code that takes the info from the userform and then inserts it
> in the document?
> Post that code if you need help with it.
>
> > - requested member of the collection does not exist. Since it's a
> > template, and it creates a new document, how do I point it back to my
> > document.
>
> Again, this depends on what you do before you launch the userform.
> Post the code that launches the userform.
>
> --
> Salut!
> _______________________________________
> Jean-Guy Marcil - Word MVP
> jmarcilREMOVE@CAPSsympatico.caTHISTOO
> Word MVP site: http://www.word.mvps.org
>
>
>

Jean-Guy
Thu Oct 27 12:59:43 CDT 2005

Amy was telling us:
Amy nous racontait que :

> Thanks for looking into this. In a separate procedure under Modules
> I have: frmCurrentDept.Show.
>
> And this is the code for my user form:
> Option Explicit
>
> Dim sourcedoc As Document
> Dim intOne As Integer
> Dim intTwo As Integer
> Dim rngDept As Range
> Dim rngTitle As Range
>
> Private Sub cboDept_Change()
>
> 'Remove any existing items in cboTitle
> If cboTitle.ListCount >= 1 Then
> For intTwo = cboTitle.ListCount To 1 Step -1
> cboTitle.RemoveItem (intTwo)
> Next intTwo

This can all be replaced with:
cboTitle.Clear

Any particular reason why you have this complicated process to clear the
combobox when one simple line can do it?

> End If
>
> Application.ScreenUpdating = False
> 'Open the file containing the form data details
> Set sourcedoc = Documents.Open(FileName:="G:\HRData102605.doc")
> '"Q\Departments\Human Resources\Readonly\HRData.doc")
> intTwo = cboDept.ListIndex + 2
> For intOne = 1 To sourcedoc.Tables(1).Cell(intTwo,
> 2).Range.Paragraphs.Count Set rngTitle =
> sourcedoc.Tables(1).Cell(intTwo, 2).Range.Paragraphs(intOne).Range
> rngTitle.End = rngTitle.End - 1
> cboTitle.AddItem rngTitle
> Next intOne
>
> End Sub
>
> Private Sub UserForm_initialize()
>
> On Error Resume Next
Remove this line and debug the code line by line to see which line causes
the problem.

You could add code to set a variable to point to the original Document.
At the top (with the other Dim statements) add
Dim OriginDoc As Document

Then in the Initialize Sub:
Set OriginDoc = ActiveDocument
or
Set OriginDoc = Documents("Name of Doc")
if the document always has the same name.

> Application.ScreenUpdating = False
> 'open the file containing the form data
> Set sourcedoc = Documents.Open(FileName:="G:\HRData102605.doc")
> '"Q\Departments\Human Resources\Readonly\HRData.doc")
> For intOne = 2 To sourcedoc.Tables(1).Rows.Count
> Set rngDept = sourcedoc.Tables(1).Cell(intOne, 1).Range
> rngDept.End = rngDept.End - 1
> cboDept.AddItem rngDept
> Next intOne
> 'close the file containing the department details
> sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
>
> End Sub
>
> Private Sub cmdClose_Click()

Here you could use OriginDoc to make sure you are inserting the values in
the proper document:

With OriginDoc
.FormFields("bkDept1").Result = cboDept.Value
.FormFields("bkTitle1").Result = cboTitle.Value
End With

Note that this will remove the bookmarks. If you do not need the bookmarks
anymore, then it does not matter, if you do (If the userform is called a
second time for example) then let us know we will show you how to save the
bookmarks.

> ActiveDocument.FormFields("bkDept1").Result = cboDept.Value
> ActiveDocument.FormFields("bkTitle1").Result = cboTitle.Value
>
> Unload Me

Note that once this line executes, the form is wiped out from memory so that
you cannot refer to it anymore.

> End Sub
--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
jmarcilREMOVE@CAPSsympatico.caTHISTOO
Word MVP site: http://www.word.mvps.org

Amy
Thu Oct 27 13:54:03 CDT 2005

Thanks Jean-Guy,

I've tried stepping through the code to determine the error, but I can't
figure it out. In the meantime, everything else is working. Thanks for your
contribution to this site.

Amy

"Jean-Guy Marcil" wrote:

> Amy was telling us:
> Amy nous racontait que :
>
> > Thanks for looking into this. In a separate procedure under Modules
> > I have: frmCurrentDept.Show.
> >
> > And this is the code for my user form:
> > Option Explicit
> >
> > Dim sourcedoc As Document
> > Dim intOne As Integer
> > Dim intTwo As Integer
> > Dim rngDept As Range
> > Dim rngTitle As Range
> >
> > Private Sub cboDept_Change()
> >
> > 'Remove any existing items in cboTitle
> > If cboTitle.ListCount >= 1 Then
> > For intTwo = cboTitle.ListCount To 1 Step -1
> > cboTitle.RemoveItem (intTwo)
> > Next intTwo
>
> This can all be replaced with:
> cboTitle.Clear
>
> Any particular reason why you have this complicated process to clear the
> combobox when one simple line can do it?
>
> > End If
> >
> > Application.ScreenUpdating = False
> > 'Open the file containing the form data details
> > Set sourcedoc = Documents.Open(FileName:="G:\HRData102605.doc")
> > '"Q\Departments\Human Resources\Readonly\HRData.doc")
> > intTwo = cboDept.ListIndex + 2
> > For intOne = 1 To sourcedoc.Tables(1).Cell(intTwo,
> > 2).Range.Paragraphs.Count Set rngTitle =
> > sourcedoc.Tables(1).Cell(intTwo, 2).Range.Paragraphs(intOne).Range
> > rngTitle.End = rngTitle.End - 1
> > cboTitle.AddItem rngTitle
> > Next intOne
> >
> > End Sub
> >
> > Private Sub UserForm_initialize()
> >
> > On Error Resume Next
> Remove this line and debug the code line by line to see which line causes
> the problem.
>
> You could add code to set a variable to point to the original Document.
> At the top (with the other Dim statements) add
> Dim OriginDoc As Document
>
> Then in the Initialize Sub:
> Set OriginDoc = ActiveDocument
> or
> Set OriginDoc = Documents("Name of Doc")
> if the document always has the same name.
>
> > Application.ScreenUpdating = False
> > 'open the file containing the form data
> > Set sourcedoc = Documents.Open(FileName:="G:\HRData102605.doc")
> > '"Q\Departments\Human Resources\Readonly\HRData.doc")
> > For intOne = 2 To sourcedoc.Tables(1).Rows.Count
> > Set rngDept = sourcedoc.Tables(1).Cell(intOne, 1).Range
> > rngDept.End = rngDept.End - 1
> > cboDept.AddItem rngDept
> > Next intOne
> > 'close the file containing the department details
> > sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
> >
> > End Sub
> >
> > Private Sub cmdClose_Click()
>
> Here you could use OriginDoc to make sure you are inserting the values in
> the proper document:
>
> With OriginDoc
> .FormFields("bkDept1").Result = cboDept.Value
> .FormFields("bkTitle1").Result = cboTitle.Value
> End With
>
> Note that this will remove the bookmarks. If you do not need the bookmarks
> anymore, then it does not matter, if you do (If the userform is called a
> second time for example) then let us know we will show you how to save the
> bookmarks.
>
> > ActiveDocument.FormFields("bkDept1").Result = cboDept.Value
> > ActiveDocument.FormFields("bkTitle1").Result = cboTitle.Value
> >
> > Unload Me
>
> Note that once this line executes, the form is wiped out from memory so that
> you cannot refer to it anymore.
>
> > End Sub
> --
> Salut!
> _______________________________________
> Jean-Guy Marcil - Word MVP
> jmarcilREMOVE@CAPSsympatico.caTHISTOO
> Word MVP site: http://www.word.mvps.org
>
>
>