Userform.show method not working in saved doc 
Author Message
 Userform.show method not working in saved doc

Hi all,
I've had a browse at the MVP's site (you wonderful people!) and other
messages....can't seem to find my exact problem. Any help would be greatly
appreciated.

I'm working in ver 2000, re-using the approach and much of the code I've had
working beautifully for a different template we developed in version 97. The
scenario includes:
- a userform in a report template to capture data and position it at
bookmarks in the document
- an AutoNew macro in the template that loads and shows the form when a new
doc is created based on the template.
- code in the Initialize event on the userform that checks to see if the
document has been saved, if so it reads from the bookmarks to populate the
userform; if not the userform displays blank
 - an OK button on the userform puts the info into the document
- a Cancel button hides the userform.
- a macro attached to a custom toolbar in the template shows the form. The
macro contains the single line of code: "Userform.Show".

This all works as it should when the document is first created - the form
loads as a blank, when the form is filled in the OK button correctly places
the data in the doc, I can hide and show the userform, change the data in
the document at will until the document has been closed and saved.

However, when I re-open the saved document, and try to show the userform
using the custom button/macro the code crashes in the one line
("Userform.show") and I get the message "The requested member of the
collection does not exit".

I'm including the code that checks to see if the doc is saved as I'm pretty
sure that this is where the problem lies. When I comment out the first If
statement below the code executes and the form shows, but as a blank. (It
doesn't get populated from the document as Len(Dir(ActiveDocument.Name)) =
0.)

'-----

FnPath = ActiveDocument.Path
If FnPath <> "" Then
    ChangeFileOpenDirectory (FnPath)
End If

If Len(Dir(ActiveDocument.Name)) <> 0 Then
   '---code to get the text from the bookmarks and put it in the userform
controls

end if

Any suggestions?? I would really appreciate any help.

Regards,
Helen



Tue, 20 Jul 2004 15:00:43 GMT  
 Userform.show method not working in saved doc
Hi Helen,

The error message you mention "The requested member of the collection does not exist" sounds to me more like you are referring to a bookmark that does not exist in your document (anymore).

What I reallly like to see is the code you refer to as
    >    '---code to get the text from the bookmarks and put it in the userform
    > controls

Does it look like this:
    If ActiveDocument.Bookmarks.Exists("NameOfBoomark") Then
        TextBox1.Text = ActiveDocument.Bookmarks("NameOfBookmark"). _
                                   Range.Text
    End If

The following is just an additional idea and depends on how the bookmarks are stored in your template initially. If these are only placeholders and the code you use to add the text to the bookmark really bookmarks the text (see example at :
http://www.mvps.org/word/FAQs/MacrosVBA/InsertingTextAtBookmark.htm)

Why not add some code in the initialize event of your userform that adds the content of the bookmark as a default to the form? If there's nothing there yet (if the document is new) the textboxes in the userform will be blank, otherwise filled with the values the user entered before.
To make it even easier, name you bookmarks the with the exact same name as the textboxes on your userform and use code like this:

------------------------------------------------
Private Sub UserForm_Initialize()
Dim oControl As MSForms.Control

  For Each oControl In Me.Controls
    If TypeOf oControl Is MSForms.TextBox Then
      If ActiveDocument.Bookmarks.Exists _
                    (oControl.Name) Then
        oControl.Text = Trim(ActiveDocument.Bookmarks _
                    (oControl.Name).Range.Text)
      End If
    End If
  Next

  Set oControl = Nothing

End Sub
------------------------------------------------

Hope this helps,
regards,
Astrid

So that all can benefit from the discussion, please post all follow-ups to the newsgroup.
Visit the MVP Word FAQ site at http://www.mvps.org/word/


Quote:
> Hi all,
> I've had a browse at the MVP's site (you wonderful people!) and other
> messages....can't seem to find my exact problem. Any help would be greatly
> appreciated.

> I'm working in ver 2000, re-using the approach and much of the code I've had
> working beautifully for a different template we developed in version 97. The
> scenario includes:
> - a userform in a report template to capture data and position it at
> bookmarks in the document
> - an AutoNew macro in the template that loads and shows the form when a new
> doc is created based on the template.
> - code in the Initialize event on the userform that checks to see if the
> document has been saved, if so it reads from the bookmarks to populate the
> userform; if not the userform displays blank
>  - an OK button on the userform puts the info into the document
> - a Cancel button hides the userform.
> - a macro attached to a custom toolbar in the template shows the form. The
> macro contains the single line of code: "Userform.Show".

> This all works as it should when the document is first created - the form
> loads as a blank, when the form is filled in the OK button correctly places
> the data in the doc, I can hide and show the userform, change the data in
> the document at will until the document has been closed and saved.

> However, when I re-open the saved document, and try to show the userform
> using the custom button/macro the code crashes in the one line
> ("Userform.show") and I get the message "The requested member of the
> collection does not exit".

> I'm including the code that checks to see if the doc is saved as I'm pretty
> sure that this is where the problem lies. When I comment out the first If
> statement below the code executes and the form shows, but as a blank. (It
> doesn't get populated from the document as Len(Dir(ActiveDocument.Name)) =
> 0.)

> '-----

> FnPath = ActiveDocument.Path
> If FnPath <> "" Then
>     ChangeFileOpenDirectory (FnPath)
> End If

> If Len(Dir(ActiveDocument.Name)) <> 0 Then
>    '---code to get the text from the bookmarks and put it in the userform
> controls

> end if

> Any suggestions?? I would really appreciate any help.

> Regards,
> Helen



Tue, 20 Jul 2004 22:15:47 GMT  
 Userform.show method not working in saved doc
Hi Astrid,

Many thanks for your reply! As per your suggestion I'm going to include code
in the initialise event to check that each of the bookmarks exists before I
try to read from them. Will be back if that doesn't solve my problem. :-)

But I also wanted to ask the slightly more general question please, if the
problem lies in the code on the initialise event wouldn't that be apparent
when I went into the debug window? I was expecting a line in the initialise
event procedure to be highlighted in yellow if that was the case. What has
been mystifying me is that it is the specific line in the macro
"UserForm.Show" that is highlighted when I go into the debug window.

I get a bit confused about the order that the code executes, in the
situation where running a macro causes a series of events to fire. For
example, am I right in understanding that if the show method is used when
the form isn't loaded (as would happen in my case when I'm opening a saved
document and running my macro from there),  VBA loads the form first, which
in turn causes the initialise event to fire? And then can the show method
executes? (or not, in my case lol)

Thanks again for your help.

Best regards,
Helen


Hi Helen,

The error message you mention "The requested member of the collection does
not exist" sounds to me more like you are referring to a bookmark that does
not exist in your document (anymore).

What I reallly like to see is the code you refer to as
    >    '---code to get the text from the bookmarks and put it in the
userform
    > controls

Does it look like this:
    If ActiveDocument.Bookmarks.Exists("NameOfBoomark") Then
        TextBox1.Text = ActiveDocument.Bookmarks("NameOfBookmark"). _
                                   Range.Text
    End If

The following is just an additional idea and depends on how the bookmarks
are stored in your template initially. If these are only placeholders and
the code you use to add the text to the bookmark really bookmarks the text
(see example at :
http://www.mvps.org/word/FAQs/MacrosVBA/InsertingTextAtBookmark.htm)

Why not add some code in the initialize event of your userform that adds the
content of the bookmark as a default to the form? If there's nothing there
yet (if the document is new) the textboxes in the userform will be blank,
otherwise filled with the values the user entered before.
To make it even easier, name you bookmarks the with the exact same name as
the textboxes on your userform and use code like this:

------------------------------------------------
Private Sub UserForm_Initialize()
Dim oControl As MSForms.Control

  For Each oControl In Me.Controls
    If TypeOf oControl Is MSForms.TextBox Then
      If ActiveDocument.Bookmarks.Exists _
                    (oControl.Name) Then
        oControl.Text = Trim(ActiveDocument.Bookmarks _
                    (oControl.Name).Range.Text)
      End If
    End If
  Next

  Set oControl = Nothing

End Sub
------------------------------------------------

Hope this helps,
regards,
Astrid

So that all can benefit from the discussion, please post all follow-ups to
the newsgroup.
Visit the MVP Word FAQ site at http://www.mvps.org/word/



Quote:
> Hi all,
> I've had a browse at the MVP's site (you wonderful people!) and other
> messages....can't seem to find my exact problem. Any help would be greatly
> appreciated.

> I'm working in ver 2000, re-using the approach and much of the code I've
had
> working beautifully for a different template we developed in version 97.
The
> scenario includes:
> - a userform in a report template to capture data and position it at
> bookmarks in the document
> - an AutoNew macro in the template that loads and shows the form when a
new
> doc is created based on the template.
> - code in the Initialize event on the userform that checks to see if the
> document has been saved, if so it reads from the bookmarks to populate the
> userform; if not the userform displays blank
>  - an OK button on the userform puts the info into the document
> - a Cancel button hides the userform.
> - a macro attached to a custom toolbar in the template shows the form. The
> macro contains the single line of code: "Userform.Show".

> This all works as it should when the document is first created - the form
> loads as a blank, when the form is filled in the OK button correctly
places
> the data in the doc, I can hide and show the userform, change the data in
> the document at will until the document has been closed and saved.

> However, when I re-open the saved document, and try to show the userform
> using the custom button/macro the code crashes in the one line
> ("Userform.show") and I get the message "The requested member of the
> collection does not exit".

> I'm including the code that checks to see if the doc is saved as I'm
pretty
> sure that this is where the problem lies. When I comment out the first If
> statement below the code executes and the form shows, but as a blank. (It
> doesn't get populated from the document as Len(Dir(ActiveDocument.Name)) =
> 0.)

> '-----

> FnPath = ActiveDocument.Path
> If FnPath <> "" Then
>     ChangeFileOpenDirectory (FnPath)
> End If

> If Len(Dir(ActiveDocument.Name)) <> 0 Then
>    '---code to get the text from the bookmarks and put it in the userform
> controls

> end if

> Any suggestions?? I would really appreciate any help.

> Regards,
> Helen



Wed, 21 Jul 2004 08:47:45 GMT  
 Userform.show method not working in saved doc
Hi again Astrid,

Success thanks to you! I've tried the code you included in your response and
it works like a charm. Again my grateful appreciation.  Still not sure why
mine wasn't working, but what the hell....your solution avoided all that
changing directories etc. Funny how obvious it seems now that that wasn't
necessary at all. :-)

Just as a matter of interest for others reading, I did change the bookmarks
too, to be all placeholders. Initially they were a mixture of place holders
and enclosing, and some enclosed a complete cell in a table. (Not sure if
the person who set up the template had reasons for this....maybe at the
time.) This meant that the "blank" form for the new document wasn't as
*blank* as I wanted it now that it's always displaying data from the
document. For example, the bookmarks that included a complete cell in a
table also included a square box when displaying in the form. Not a good
look.

Phew, such a relief to get a problem solved. It was really starting to
stress me out. Thanks again.

Cheers,
Helen


Hi Helen,

The error message you mention "The requested member of the collection does
not exist" sounds to me more like you are referring to a bookmark that does
not exist in your document (anymore).

What I reallly like to see is the code you refer to as
    >    '---code to get the text from the bookmarks and put it in the
userform
    > controls

Does it look like this:
    If ActiveDocument.Bookmarks.Exists("NameOfBoomark") Then
        TextBox1.Text = ActiveDocument.Bookmarks("NameOfBookmark"). _
                                   Range.Text
    End If

The following is just an additional idea and depends on how the bookmarks
are stored in your template initially. If these are only placeholders and
the code you use to add the text to the bookmark really bookmarks the text
(see example at :
http://www.mvps.org/word/FAQs/MacrosVBA/InsertingTextAtBookmark.htm)

Why not add some code in the initialize event of your userform that adds the
content of the bookmark as a default to the form? If there's nothing there
yet (if the document is new) the textboxes in the userform will be blank,
otherwise filled with the values the user entered before.
To make it even easier, name you bookmarks the with the exact same name as
the textboxes on your userform and use code like this:

------------------------------------------------
Private Sub UserForm_Initialize()
Dim oControl As MSForms.Control

  For Each oControl In Me.Controls
    If TypeOf oControl Is MSForms.TextBox Then
      If ActiveDocument.Bookmarks.Exists _
                    (oControl.Name) Then
        oControl.Text = Trim(ActiveDocument.Bookmarks _
                    (oControl.Name).Range.Text)
      End If
    End If
  Next

  Set oControl = Nothing

End Sub
------------------------------------------------

Hope this helps,
regards,
Astrid

So that all can benefit from the discussion, please post all follow-ups to
the newsgroup.
Visit the MVP Word FAQ site at http://www.mvps.org/word/



Quote:
> Hi all,
> I've had a browse at the MVP's site (you wonderful people!) and other
> messages....can't seem to find my exact problem. Any help would be greatly
> appreciated.

> I'm working in ver 2000, re-using the approach and much of the code I've
had
> working beautifully for a different template we developed in version 97.
The
> scenario includes:
> - a userform in a report template to capture data and position it at
> bookmarks in the document
> - an AutoNew macro in the template that loads and shows the form when a
new
> doc is created based on the template.
> - code in the Initialize event on the userform that checks to see if the
> document has been saved, if so it reads from the bookmarks to populate the
> userform; if not the userform displays blank
>  - an OK button on the userform puts the info into the document
> - a Cancel button hides the userform.
> - a macro attached to a custom toolbar in the template shows the form. The
> macro contains the single line of code: "Userform.Show".

> This all works as it should when the document is first created - the form
> loads as a blank, when the form is filled in the OK button correctly
places
> the data in the doc, I can hide and show the userform, change the data in
> the document at will until the document has been closed and saved.

> However, when I re-open the saved document, and try to show the userform
> using the custom button/macro the code crashes in the one line
> ("Userform.show") and I get the message "The requested member of the
> collection does not exit".

> I'm including the code that checks to see if the doc is saved as I'm
pretty
> sure that this is where the problem lies. When I comment out the first If
> statement below the code executes and the form shows, but as a blank. (It
> doesn't get populated from the document as Len(Dir(ActiveDocument.Name)) =
> 0.)

> '-----

> FnPath = ActiveDocument.Path
> If FnPath <> "" Then
>     ChangeFileOpenDirectory (FnPath)
> End If

> If Len(Dir(ActiveDocument.Name)) <> 0 Then
>    '---code to get the text from the bookmarks and put it in the userform
> controls

> end if

> Any suggestions?? I would really appreciate any help.

> Regards,
> Helen



Wed, 21 Jul 2004 13:09:20 GMT  
 Userform.show method not working in saved doc
Hi Helen,

Quote:
> Success thanks to you! I've tried the code you included in your response and
> it works like a charm. Again my grateful appreciation.  Still not sure why
> mine wasn't working, but what the hell....your solution avoided all that
> changing directories etc. Funny how obvious it seems now that that wasn't
> necessary at all. :-)

That happens to all of us. After solving a problem, the initial question that seemed so difficult is then not difficult anymore ;-)
Glad to hear you've got it all working now.

Regards,
Astrid

So that all can benefit from the discussion, please post all follow-ups to the newsgroup.
Visit the MVP Word FAQ site at http://www.mvps.org/word/



Wed, 21 Jul 2004 17:50:35 GMT  
 Userform.show method not working in saved doc
hi Helen,

this depends on the error trapping options you set in the VBE. if it is set
to 'break on unhandled errors' & there exists no custom error handler, then
this will cause the project to enter break mode on the line of code that
invoked the offending procedure of the class (in this case the userform
class module). if you set it to break on all errors or to 'break in clas
modules', then it would enter break mode in the initialise event of the
userform class module.

greetings, pieter.

Quote:
> But I also wanted to ask the slightly more general question please, if the
> problem lies in the code on the initialise event wouldn't that be apparent
> when I went into the debug window? I was expecting a line in the
initialise
> event procedure to be highlighted in yellow if that was the case. What has
> been mystifying me is that it is the specific line in the macro
> "UserForm.Show" that is highlighted when I go into the debug window.

> I get a bit confused about the order that the code executes, in the
> situation where running a macro causes a series of events to fire. For
> example, am I right in understanding that if the show method is used when
> the form isn't loaded (as would happen in my case when I'm opening a saved
> document and running my macro from there),  VBA loads the form first,
which
> in turn causes the initialise event to fire? And then can the show method
> executes? (or not, in my case lol)

> Thanks again for your help.

> Best regards,
> Helen



Thu, 22 Jul 2004 07:34:37 GMT  
 Userform.show method not working in saved doc
Hi Pieter

That's a great tip, thank you!  I've never actually looked in there. I see
mine is set to Break on Unhandled Errors, so that explains it. Serves me
right for not tackling the error handling too I guess.:-)

Best regards,

Helen


Quote:
> hi Helen,

> this depends on the error trapping options you set in the VBE. if it is
set
> to 'break on unhandled errors' & there exists no custom error handler,
then
> this will cause the project to enter break mode on the line of code that
> invoked the offending procedure of the class (in this case the userform
> class module). if you set it to break on all errors or to 'break in clas
> modules', then it would enter break mode in the initialise event of the
> userform class module.

> greetings, pieter.

> > But I also wanted to ask the slightly more general question please, if
the
> > problem lies in the code on the initialise event wouldn't that be
apparent
> > when I went into the debug window? I was expecting a line in the
> initialise
> > event procedure to be highlighted in yellow if that was the case. What
has
> > been mystifying me is that it is the specific line in the macro
> > "UserForm.Show" that is highlighted when I go into the debug window.

> > I get a bit confused about the order that the code executes, in the
> > situation where running a macro causes a series of events to fire. For
> > example, am I right in understanding that if the show method is used
when
> > the form isn't loaded (as would happen in my case when I'm opening a
saved
> > document and running my macro from there),  VBA loads the form first,
> which
> > in turn causes the initialise event to fire? And then can the show
method
> > executes? (or not, in my case lol)

> > Thanks again for your help.

> > Best regards,
> > Helen



Thu, 22 Jul 2004 10:21:05 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Userform.show method not working in saved doc

2. Can't show an UserForm (Frm.Show False does not work)

3. Finding UserForm Show Method in Object Browser

4. W2K userform.show method changes between documents

5. Method or Property not available, doc window not active

6. Show userform on doc open

7. Word 97 doc does not save in usual .doc format - HELP

8. Word x saving in last saved folder, not where doc is from

9. Show All (show/hide) not working in Word 2002

10. Show method doesn't work


 
Powered by phpBB® Forum Software © phpBB Group