Repopulating userform based on bookmark.range.text

[Katherine]

Me again. My userform fills out an enormous number of bookmarks in a
structured document (which will then be imported into another program). The
user wants to be able to reopen the document and have it automatically
populate the userform with what they entered last time, so they only have to
edit it rather than retype it all.

No problem, I thought - just use userform_initialize to set the value of
each textbox/combobox with the value from the relevant bookmark in the
document. I've used this:

If ActiveDocument.Bookmarks("bmkPrice1").Range.Text = "" Then Exit Sub
--If they have filled the form in before, this bookmark won't be blank, so
I'm checking it first and not bothering with the rest of the sub if the
bookmark is blank

For i = 1 To 48
Me.Controls("txtAgencyRef" & CStr(i)).Value = ActiveDocument.Bookmarks _
("bmkAgencyRef" & CStr(i)).Range.Text
Next i

To all intents and purposes, this works. Problem is, it's feasible for some
bookmarks to be blank and when they are, the userform is pulling in a
paragraph mark or a space (because that's whats currently inside the bookmark
I guess).

It's doing what I tell it to do but this isn't what I want (typical woman,
huh?) - is there any other way I can pull in just the text from inside the
bookmark?

Because the doc is designed to work to the specifications of the program
it's going to be imported into, I can't get rid of the carriage returns in
the doc, which is structured something like this:

###AgencyRef1
[bookmark]
#Price1
[bookmark]
....etc

Also....

Submitting the edited details by clicking the command button is appending
the value of the controls to what is already in each bookmark - which is,
again, no good. I could clear all the bookmarks before entering the values in
them, but as the user is only going to be editing the userform contents this
may lead me to wipe out information that is actually needed.

This is an example of the type of thing I have coded onto the command
button, just in case you can see any rookie errors or 'stupid thinking':

For i = 1 To 48
ActiveDocument.Bookmarks("bmkAgencyRef" & CStr(i)).Range _
..InsertBefore Me.Controls("txtAgencyRef" & CStr(i)).Text
Next i

Can anyone think of a way round this? Am I a hopeless case? Shall I just
give up and tell the user to "Do it your @~#! self!" ? Anyone shedding any
light is welcome to my firstborn.

 

[Chad DeMeyer]

Katherine,

This is one of the pitfalls of using bookmarks. You should use document
properties instead. In your template, create a custom document propety
(File>Properties>Custom) for each value that your userform will insert into
the document. Then, in place of the existing bookmarks, insert DOCPROPERTY
fields. Then in your code:

Me.Controls("txtAgencyRef" & Format(i)).Value =
ActiveDocument.CustomDocumentProperties("txtAgencyRef" & Format(i)).Value

and

ActiveDocument.CustomDocumentProperties("txtAgencyRef" & Format(i)).Value =
Me.Controls("txtAgencyRef" & Format(i)).Value
Next i
ActiveDocument.Fields.Update

Then you won't be have to do all sorts of validation on the contents of a
bookmark range (other exceptions besides the one you are currently dealing
with would eventually arise). Hope that helps.

Regards,
Chad

Me again. My userform fills out an enormous number of bookmarks in a
structured document (which will then be imported into another program). The
user wants to be able to reopen the document and have it automatically
populate the userform with what they entered last time, so they only have to
edit it rather than retype it all.

No problem, I thought - just use userform_initialize to set the value of
each textbox/combobox with the value from the relevant bookmark in the
document. I've used this:

If ActiveDocument.Bookmarks("bmkPrice1").Range.Text = "" Then Exit Sub
--If they have filled the form in before, this bookmark won't be blank, so
I'm checking it first and not bothering with the rest of the sub if the
bookmark is blank

For i = 1 To 48
Me.Controls("txtAgencyRef" & CStr(i)).Value = ActiveDocument.Bookmarks _
("bmkAgencyRef" & CStr(i)).Range.Text
Next i

To all intents and purposes, this works. Problem is, it's feasible for some
bookmarks to be blank and when they are, the userform is pulling in a
paragraph mark or a space (because that's whats currently inside the bookmark
I guess).

It's doing what I tell it to do but this isn't what I want (typical woman,
huh?) - is there any other way I can pull in just the text from inside the
bookmark?

Because the doc is designed to work to the specifications of the program
it's going to be imported into, I can't get rid of the carriage returns in
the doc, which is structured something like this:

###AgencyRef1
[bookmark]
#Price1
[bookmark]
...etc

Also....

Submitting the edited details by clicking the command button is appending
the value of the controls to what is already in each bookmark - which is,
again, no good. I could clear all the bookmarks before entering the values in
them, but as the user is only going to be editing the userform contents this
may lead me to wipe out information that is actually needed.

This is an example of the type of thing I have coded onto the command
button, just in case you can see any rookie errors or 'stupid thinking':

For i = 1 To 48
ActiveDocument.Bookmarks("bmkAgencyRef" & CStr(i)).Range _
.InsertBefore Me.Controls("txtAgencyRef" & CStr(i)).Text
Next i

Can anyone think of a way round this? Am I a hopeless case? Shall I just
give up and tell the user to "Do it your @~#! self!" ? Anyone shedding any
light is welcome to my firstborn.

 

[Katherine]

Excellent, thanks Chad. My firstborn will be in the post as soon as he arrives.

I've mocked up a template using document properties as you suggested and it
appears to be working like a charm.

But (there's always a but, isn't there), now I don't seem to be able to tab
from one control to the next within the userform - hitting the tab key just
enters an actual tab into the textbox. Is there a way to change this? I'm
figuring it's something simple I've forgotten to do.

Katherine,

This is one of the pitfalls of using bookmarks. You should use document
properties instead. In your template, create a custom document propety
(File>Properties>Custom) for each value that your userform will insert into
the document. Then, in place of the existing bookmarks, insert DOCPROPERTY
fields. Then in your code:

Me.Controls("txtAgencyRef" & Format(i)).Value =
ActiveDocument.CustomDocumentProperties("txtAgencyRef" & Format(i)).Value

and

ActiveDocument.CustomDocumentProperties("txtAgencyRef" & Format(i)).Value =
Me.Controls("txtAgencyRef" & Format(i)).Value
Next i
ActiveDocument.Fields.Update

Then you won't be have to do all sorts of validation on the contents of a
bookmark range (other exceptions besides the one you are currently dealing
with would eventually arise). Hope that helps.

Regards,
Chad

Me again. My userform fills out an enormous number of bookmarks in a
structured document (which will then be imported into another program). The
user wants to be able to reopen the document and have it automatically
populate the userform with what they entered last time, so they only have to
edit it rather than retype it all.

No problem, I thought - just use userform_initialize to set the value of
each textbox/combobox with the value from the relevant bookmark in the
document. I've used this:

If ActiveDocument.Bookmarks("bmkPrice1").Range.Text = "" Then Exit Sub
--If they have filled the form in before, this bookmark won't be blank, so
I'm checking it first and not bothering with the rest of the sub if the
bookmark is blank

For i = 1 To 48
Me.Controls("txtAgencyRef" & CStr(i)).Value = ActiveDocument.Bookmarks _
("bmkAgencyRef" & CStr(i)).Range.Text
Next i

To all intents and purposes, this works. Problem is, it's feasible for some
bookmarks to be blank and when they are, the userform is pulling in a
paragraph mark or a space (because that's whats currently inside the bookmark
I guess).

It's doing what I tell it to do but this isn't what I want (typical woman,
huh?) - is there any other way I can pull in just the text from inside the
bookmark?

Because the doc is designed to work to the specifications of the program
it's going to be imported into, I can't get rid of the carriage returns in
the doc, which is structured something like this:

###AgencyRef1
[bookmark]
#Price1
[bookmark]
...etc

Also....

Submitting the edited details by clicking the command button is appending
the value of the controls to what is already in each bookmark - which is,
again, no good. I could clear all the bookmarks before entering the values in
them, but as the user is only going to be editing the userform contents this
may lead me to wipe out information that is actually needed.

This is an example of the type of thing I have coded onto the command
button, just in case you can see any rookie errors or 'stupid thinking':

For i = 1 To 48
ActiveDocument.Bookmarks("bmkAgencyRef" & CStr(i)).Range _
.InsertBefore Me.Controls("txtAgencyRef" & CStr(i)).Text
Next i

Can anyone think of a way round this? Am I a hopeless case? Shall I just
give up and tell the user to "Do it your @~#! self!" ? Anyone shedding any
light is welcome to my firstborn.

 

[Peter Hewett]

Hi Katherine

The way I handle it is to have two paths through the code. The code hooked to
Document_New (or AutoNew) just loads the UserForm using no, or default values. The code
hooked to the Document_Open (or AutoOpen) loads the controls from the document. Since you
know your are loading pre-edited data into the controls you can massage it as needed.

HTH + Cheers - Peter

Me again. My userform fills out an enormous number of bookmarks in a
structured document (which will then be imported into another program). The
user wants to be able to reopen the document and have it automatically
populate the userform with what they entered last time, so they only have to
edit it rather than retype it all.

No problem, I thought - just use userform_initialize to set the value of
each textbox/combobox with the value from the relevant bookmark in the
document. I've used this:

If ActiveDocument.Bookmarks("bmkPrice1").Range.Text = "" Then Exit Sub
--If they have filled the form in before, this bookmark won't be blank, so
I'm checking it first and not bothering with the rest of the sub if the
bookmark is blank

For i = 1 To 48
Me.Controls("txtAgencyRef" & CStr(i)).Value = ActiveDocument.Bookmarks _
("bmkAgencyRef" & CStr(i)).Range.Text
Next i

To all intents and purposes, this works. Problem is, it's feasible for some
bookmarks to be blank and when they are, the userform is pulling in a
paragraph mark or a space (because that's whats currently inside the bookmark
I guess).

It's doing what I tell it to do but this isn't what I want (typical woman,
huh?) - is there any other way I can pull in just the text from inside the
bookmark?

Because the doc is designed to work to the specifications of the program
it's going to be imported into, I can't get rid of the carriage returns in
the doc, which is structured something like this:

###AgencyRef1
[bookmark]
#Price1
[bookmark]
...etc

Also....

Submitting the edited details by clicking the command button is appending
the value of the controls to what is already in each bookmark - which is,
again, no good. I could clear all the bookmarks before entering the values in
them, but as the user is only going to be editing the userform contents this
may lead me to wipe out information that is actually needed.

This is an example of the type of thing I have coded onto the command
button, just in case you can see any rookie errors or 'stupid thinking':

For i = 1 To 48
ActiveDocument.Bookmarks("bmkAgencyRef" & CStr(i)).Range _
.InsertBefore Me.Controls("txtAgencyRef" & CStr(i)).Text
Next i

Can anyone think of a way round this? Am I a hopeless case? Shall I just
give up and tell the user to "Do it your @~#! self!" ? Anyone shedding any
light is welcome to my firstborn.

 

[Doug Robbins]

Sounds like you have the TabKeyBehaviour for the controls set to True. Set
it to False and it should then cause the focus to jump to the next control
in the tab order.

--
Please respond to the Newsgroup for the benefit of others who may be
interested. Questions sent directly to me will only be answered on a paid
consulting basis.

Hope this helps,
Doug Robbins - Word MVP

Excellent, thanks Chad. My firstborn will be in the post as soon as he
arrives.

I've mocked up a template using document properties as you suggested and
it
appears to be working like a charm.

But (there's always a but, isn't there), now I don't seem to be able to
tab
from one control to the next within the userform - hitting the tab key
just
enters an actual tab into the textbox. Is there a way to change this? I'm
figuring it's something simple I've forgotten to do.

Katherine,

This is one of the pitfalls of using bookmarks. You should use document
properties instead. In your template, create a custom document propety
(File>Properties>Custom) for each value that your userform will insert
into
the document. Then, in place of the existing bookmarks, insert
DOCPROPERTY
fields. Then in your code:

Me.Controls("txtAgencyRef" & Format(i)).Value =
ActiveDocument.CustomDocumentProperties("txtAgencyRef" & Format(i)).Value

and

ActiveDocument.CustomDocumentProperties("txtAgencyRef" & Format(i)).Value
=
Me.Controls("txtAgencyRef" & Format(i)).Value
Next i
ActiveDocument.Fields.Update

Then you won't be have to do all sorts of validation on the contents of a
bookmark range (other exceptions besides the one you are currently
dealing
with would eventually arise). Hope that helps.

Regards,
Chad

Me again. My userform fills out an enormous number of bookmarks in a
structured document (which will then be imported into another program). The
user wants to be able to reopen the document and have it automatically
populate the userform with what they entered last time, so they only
have to
edit it rather than retype it all.

No problem, I thought - just use userform_initialize to set the value
of
each textbox/combobox with the value from the relevant bookmark in the
document. I've used this:

If ActiveDocument.Bookmarks("bmkPrice1").Range.Text = "" Then Exit Sub
--If they have filled the form in before, this bookmark won't be blank,
so
I'm checking it first and not bothering with the rest of the sub if the
bookmark is blank

For i = 1 To 48
Me.Controls("txtAgencyRef" & CStr(i)).Value = ActiveDocument.Bookmarks
_
("bmkAgencyRef" & CStr(i)).Range.Text
Next i

To all intents and purposes, this works. Problem is, it's feasible for some
bookmarks to be blank and when they are, the userform is pulling in a
paragraph mark or a space (because that's whats currently inside the bookmark
I guess).

It's doing what I tell it to do but this isn't what I want (typical woman,
huh?) - is there any other way I can pull in just the text from inside
the
bookmark?

Because the doc is designed to work to the specifications of the
program
it's going to be imported into, I can't get rid of the carriage returns
in
the doc, which is structured something like this:

###AgencyRef1
[bookmark]
#Price1
[bookmark]
...etc

Also....

Submitting the edited details by clicking the command button is
appending
the value of the controls to what is already in each bookmark - which
is,
again, no good. I could clear all the bookmarks before entering the
values in
them, but as the user is only going to be editing the userform contents this
may lead me to wipe out information that is actually needed.

This is an example of the type of thing I have coded onto the command
button, just in case you can see any rookie errors or 'stupid
thinking':

For i = 1 To 48
ActiveDocument.Bookmarks("bmkAgencyRef" & CStr(i)).Range _
.InsertBefore Me.Controls("txtAgencyRef" & CStr(i)).Text
Next i

Can anyone think of a way round this? Am I a hopeless case? Shall I
just
give up and tell the user to "Do it your @~#! self!" ? Anyone shedding
any
light is welcome to my firstborn.

 

[Malcolm Smith]

If you do go back to ever using bookmarks as place holders on the
document; sometimes they are useful if ever the user is required to change
the text on the document in situ then rather than your
For i = 1 to 48 loop

you could have something like this (again, beware that this hasn't been
near a compiler, so it may be slightly flawed):


dim sBookmark as string

For each oControl in Me.Controls
if left$(oControl.Name, 6) = "txtbmk" then

if activedocument.bookmarks.exist(oControl.Name) then
oControl.Text = activedocument.bookmarks (oControl.Name).Range.Text
end if
next for


Then if you had a textbox with the name "txtbmkFirstName", for example,
and then a bookmark on the document with the same name then the code would
copy the data across.

This means that you can add and remove bookmarks on the template/document
and controls from the form without worry of having to check to see if your
code explodes or not.


As I have mentioned here before; sometimes Document Variables are the
right way to go and sometimes Bookmarks are the other way. It all depends
on the client and how they will use that particular document.

- Malc
www.dragondrop.com