Conditional drop down list

[Amy]

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 Robbins - Word MVP]

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]

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.

 

[Jean-Guy Marcil]

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
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Amy]

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]

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
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Amy]

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]

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
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Amy]

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