About Us

Steve

Hi there,

I've created a form running various macro codes. One example of code would
be a quotation reference number which updates when the macro template is
fired.

As there are bulk quotations to produce, i'd like the user to avoid closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document were just fired.

Also a button that may leave a certain field un-refreshed. For example,
several quotes to be produced for the same company where the company name and
address field would remane un-refreshed.

Many thanks

Steve

Suzanne S. Barnhill

If the form is created as a template, and if your UserForms (or whatever
they are) are launched by an AutoNew macro, then every time a new document
is created based on the template, your macros will run. There is no need to
close and restart Word; just create a new document.

Alternatively, you can choose to either hide a UserForm or close it; in the
first instance, it will retain previous entries.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
Hi there,

I've created a form running various macro codes. One example of code would
be a quotation reference number which updates when the macro template is
fired.

As there are bulk quotations to produce, i'd like the user to avoid
closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document were just
fired.

Also a button that may leave a certain field un-refreshed. For example,
several quotes to be produced for the same company where the company name
and
address field would remane un-refreshed.

Many thanks

Steve

Steve

Hi Suzanne,

Thanks for your reply.

Here is my AutoNew macro. Is there something i need to add to make this work?

Sub AutoNew()

Order = System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print & Design\Templates\Quotation
Number.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print & Design\Templates\Quotation
Number.Txt", "MacroSettings", _
"Order") = Order
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Order").Range.InsertBefo re Format(Order, "200#")
ActiveDocument.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

"Suzanne S. Barnhill" wrote:

If the form is created as a template, and if your UserForms (or whatever
they are) are launched by an AutoNew macro, then every time a new document
is created based on the template, your macros will run. There is no need to
close and restart Word; just create a new document.

Alternatively, you can choose to either hide a UserForm or close it; in the
first instance, it will retain previous entries.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
Hi there,

I've created a form running various macro codes. One example of code would
be a quotation reference number which updates when the macro template is
fired.

As there are bulk quotations to produce, i'd like the user to avoid
closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document were just
fired.

Also a button that may leave a certain field un-refreshed. For example,
several quotes to be produced for the same company where the company name
and
address field would remane un-refreshed.

Many thanks

Steve

Graham Mayor

If you re-run the macro within the open document, as written it will simply
append the new number. To avoid this (and the need to unlock the form)
replace the bookmark with a form field (here shown as Text1) with the fillin
property disabled. The following version of the macro will then fill that
field with the current number every time the macro is run. Thus you can run
it from a toolbar button to update the number:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven Perry\Documents\Business\Ainsworth Print
& Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order") = Order
With ActiveDocument
.FormFields("Text1").Result = format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven Perry\Documents\Business\Master
File\Quotation File\Quotation ID_" & format(Order, "200#")
End With
End Sub

If you prefer your current approach then you are going to have to recode to
replace the content of the bookmark rather than add to it . See the example
at http://www.gmayor.com/word_vba_examples.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Suzanne,

Thanks for your reply.

Here is my AutoNew macro. Is there something i need to add to make
this work?

Sub AutoNew()

Order = System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print & Design\Templates\Quotation
Number.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print & Design\Templates\Quotation
Number.Txt", "MacroSettings", _
"Order") = Order
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Order").Range.InsertBefo re Format(Order,
"200#") ActiveDocument.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

"Suzanne S. Barnhill" wrote:

If the form is created as a template, and if your UserForms (or
whatever they are) are launched by an AutoNew macro, then every time
a new document is created based on the template, your macros will
run. There is no need to close and restart Word; just create a new
document.

Alternatively, you can choose to either hide a UserForm or close it;
in the first instance, it will retain previous entries.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
Hi there,

I've created a form running various macro codes. One example of
code would be a quotation reference number which updates when the
macro template is fired.

As there are bulk quotations to produce, i'd like the user to avoid
closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document were
just fired.

Also a button that may leave a certain field un-refreshed. For
example, several quotes to be produced for the same company where
the company name and
address field would remane un-refreshed.

Many thanks

Steve

Steve

Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it. Example the
address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

If you re-run the macro within the open document, as written it will simply
append the new number. To avoid this (and the need to unlock the form)
replace the bookmark with a form field (here shown as Text1) with the fillin
property disabled. The following version of the macro will then fill that
field with the current number every time the macro is run. Thus you can run
it from a toolbar button to update the number:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven Perry\Documents\Business\Ainsworth Print
& Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order") = Order
With ActiveDocument
.FormFields("Text1").Result = format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven Perry\Documents\Business\Master
File\Quotation File\Quotation ID_" & format(Order, "200#")
End With
End Sub

If you prefer your current approach then you are going to have to recode to
replace the content of the bookmark rather than add to it . See the example
at http://www.gmayor.com/word_vba_examples.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Suzanne,

Thanks for your reply.

Here is my AutoNew macro. Is there something i need to add to make
this work?

Sub AutoNew()

Order = System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print & Design\Templates\Quotation
Number.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print & Design\Templates\Quotation
Number.Txt", "MacroSettings", _
"Order") = Order
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Order").Range.InsertBefo re Format(Order,
"200#") ActiveDocument.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

"Suzanne S. Barnhill" wrote:

If the form is created as a template, and if your UserForms (or
whatever they are) are launched by an AutoNew macro, then every time
a new document is created based on the template, your macros will
run. There is no need to close and restart Word; just create a new
document.

Alternatively, you can choose to either hide a UserForm or close it;
in the first instance, it will retain previous entries.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
Hi there,

I've created a form running various macro codes. One example of
code would be a quotation reference number which updates when the
macro template is fired.

As there are bulk quotations to produce, i'd like the user to avoid
closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document were
just fired.

Also a button that may leave a certain field un-refreshed. For
example, several quotes to be produced for the same company where
the company name and
address field would remane un-refreshed.

Many thanks

Steve

Graham Mayor

The following will reset all the form fields except for a field with the
bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

If you re-run the macro within the open document, as written it will
simply append the new number. To avoid this (and the need to unlock
the form) replace the bookmark with a form field (here shown as
Text1) with the fillin property disabled. The following version of
the macro will then fill that field with the current number every
time the macro is run. Thus you can run it from a toolbar button to
update the number:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
= Order With ActiveDocument
.FormFields("Text1").Result = format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
format(Order, "200#")
End With
End Sub

If you prefer your current approach then you are going to have to
recode to replace the content of the bookmark rather than add to it
. See the example at http://www.gmayor.com/word_vba_examples.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Suzanne,

Thanks for your reply.

Here is my AutoNew macro. Is there something i need to add to make
this work?

Sub AutoNew()

Order = System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", "MacroSettings", _
"Order") = Order
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Order").Range.InsertBefo re Format(Order,
"200#") ActiveDocument.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

"Suzanne S. Barnhill" wrote:

If the form is created as a template, and if your UserForms (or
whatever they are) are launched by an AutoNew macro, then every
time a new document is created based on the template, your macros
will run. There is no need to close and restart Word; just create
a new document.

Alternatively, you can choose to either hide a UserForm or close
it; in the first instance, it will retain previous entries.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
Hi there,

I've created a form running various macro codes. One example of
code would be a quotation reference number which updates when the
macro template is fired.

As there are bulk quotations to produce, i'd like the user to
avoid closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document were
just fired.

Also a button that may leave a certain field un-refreshed. For
example, several quotes to be produced for the same company where
the company name and
address field would remane un-refreshed.

Many thanks

Steve

Steve

Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field with the
bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

If you re-run the macro within the open document, as written it will
simply append the new number. To avoid this (and the need to unlock
the form) replace the bookmark with a form field (here shown as
Text1) with the fillin property disabled. The following version of
the macro will then fill that field with the current number every
time the macro is run. Thus you can run it from a toolbar button to
update the number:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
= Order With ActiveDocument
.FormFields("Text1").Result = format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
format(Order, "200#")
End With
End Sub

If you prefer your current approach then you are going to have to
recode to replace the content of the bookmark rather than add to it
. See the example at http://www.gmayor.com/word_vba_examples.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Suzanne,

Thanks for your reply.

Here is my AutoNew macro. Is there something i need to add to make
this work?

Sub AutoNew()

Order = System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", "MacroSettings", _
"Order") = Order
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Order").Range.InsertBefo re Format(Order,
"200#") ActiveDocument.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

"Suzanne S. Barnhill" wrote:

If the form is created as a template, and if your UserForms (or
whatever they are) are launched by an AutoNew macro, then every
time a new document is created based on the template, your macros
will run. There is no need to close and restart Word; just create
a new document.

Alternatively, you can choose to either hide a UserForm or close
it; in the first instance, it will retain previous entries.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
Hi there,

I've created a form running various macro codes. One example of
code would be a quotation reference number which updates when the
macro template is fired.

As there are bulk quotations to produce, i'd like the user to
avoid closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document were
just fired.

Also a button that may leave a certain field un-refreshed. For
example, several quotes to be produced for the same company where
the company name and
address field would remane un-refreshed.

Many thanks

Steve

Graham Mayor

In what way does it fail? The macro unlocks the form using your password 123
resets all the fields except the one called Address, but ignores any
dropdown fields, then relocks the form using your password? Ther address
field content is copied to a variable then written back to the field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field with
the bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

If you re-run the macro within the open document, as written it
will simply append the new number. To avoid this (and the need to
unlock the form) replace the bookmark with a form field (here
shown as Text1) with the fillin property disabled. The following
version of the macro will then fill that field with the current
number every time the macro is run. Thus you can run it from a
toolbar button to update the number:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
= Order With ActiveDocument
.FormFields("Text1").Result = format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_"
& format(Order, "200#")
End With
End Sub

If you prefer your current approach then you are going to have to
recode to replace the content of the bookmark rather than add to it
. See the example at http://www.gmayor.com/word_vba_examples.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Suzanne,

Thanks for your reply.

Here is my AutoNew macro. Is there something i need to add to make
this work?

Sub AutoNew()

Order = System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", "MacroSettings", _
"Order") = Order
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Order").Range.InsertBefo re Format(Order,
"200#") ActiveDocument.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation
ID_" & Format(Order, "200#")
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

"Suzanne S. Barnhill" wrote:

If the form is created as a template, and if your UserForms (or
whatever they are) are launched by an AutoNew macro, then every
time a new document is created based on the template, your macros
will run. There is no need to close and restart Word; just create
a new document.

Alternatively, you can choose to either hide a UserForm or close
it; in the first instance, it will retain previous entries.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
Hi there,

I've created a form running various macro codes. One example of
code would be a quotation reference number which updates when
the macro template is fired.

As there are bulk quotations to produce, i'd like the user to
avoid closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document
were just fired.

Also a button that may leave a certain field un-refreshed. For
example, several quotes to be produced for the same company
where the company name and
address field would remane un-refreshed.

Many thanks

Steve

Steve

I get a visual basic warning box with no message.

I can only guess maybe conflict with another macros.

Please see below the macros i'm running:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven Perry\Documents\Business\Ainsworth Print
& Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order") = Order
With ActiveDocument
.FormFields("order").Result = Format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven Perry\Documents\Business\Master
File\Quotation File\Quotation ID_" & Format(Order, "200#")
End With
End Sub

-------------------------------

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer

'Set up the formatting codes in strCode
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub

'Eliminate blank paragraphs by looking for two carriage returns in a row
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop

'Strip off final paragraph mark
strAddress = Left(strAddress, Len(strAddress) - 1)
'Insert the modified address at the current insertion point
ActiveDocument.Unprotect Password:="123"
Selection.Range.Text = strAddress
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

-----------------------------------------

Dim sAdd As String
Dim sPassword As String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

In what way does it fail? The macro unlocks the form using your password 123
resets all the fields except the one called Address, but ignores any
dropdown fields, then relocks the form using your password? Ther address
field content is copied to a variable then written back to the field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field with
the bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

If you re-run the macro within the open document, as written it
will simply append the new number. To avoid this (and the need to
unlock the form) replace the bookmark with a form field (here
shown as Text1) with the fillin property disabled. The following
version of the macro will then fill that field with the current
number every time the macro is run. Thus you can run it from a
toolbar button to update the number:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
= Order With ActiveDocument
.FormFields("Text1").Result = format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_"
& format(Order, "200#")
End With
End Sub

If you prefer your current approach then you are going to have to
recode to replace the content of the bookmark rather than add to it
. See the example at http://www.gmayor.com/word_vba_examples.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Suzanne,

Thanks for your reply.

Here is my AutoNew macro. Is there something i need to add to make
this work?

Sub AutoNew()

Order = System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", "MacroSettings", _
"Order") = Order
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Order").Range.InsertBefo re Format(Order,
"200#") ActiveDocument.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation
ID_" & Format(Order, "200#")
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

"Suzanne S. Barnhill" wrote:

If the form is created as a template, and if your UserForms (or
whatever they are) are launched by an AutoNew macro, then every
time a new document is created based on the template, your macros
will run. There is no need to close and restart Word; just create
a new document.

Alternatively, you can choose to either hide a UserForm or close
it; in the first instance, it will retain previous entries.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
Hi there,

I've created a form running various macro codes. One example of
code would be a quotation reference number which updates when
the macro template is fired.

As there are bulk quotations to produce, i'd like the user to
avoid closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document
were just fired.

Also a button that may leave a certain field un-refreshed. For
example, several quotes to be produced for the same company
where the company name and
address field would remane un-refreshed.

Many thanks

Steve

Graham Mayor

The problem appears to be that you are unprotecting the document to insert
the address from Outlook directly into the document, much as you did earlier
with the number, coupled with some anomalies relating to the ResetForm
macro.

Your version of the InserrtAddress macro also unlocked the field with the
password 123 then locked it again without the password. It is better to
define the password as a string as in the earlier example and insert the
string variable name in the protect/unprotect commands.

It would be better if you inserted a form field - let's call it 'Address' so
it matches the Address field that the reset macro cannot find . You can then
insert the address from the macro without unlocking the form by using the
same method as before i.e.

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
If strAddress = "" Then Exit Sub
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop
strAddress = Left(strAddress, Len(strAddress) - 1)
ActiveDocument.FormFields("Address").Result = strAddress
End Sub

The ResetForm macro that I posted should replace your ResetForm not be used
in addition to it - and it appears from your listing that it now has no
name?

There remains some confusion over *your* ResetForm macro which appears to
refer to a field that doesn't exist. If it does and has nothing to do with
the InsertAddress function then you would need to rename the field at the
end of the above macro to reflect the form field name that you wish the
address to be inserted into.

I think that should have it covered

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I get a visual basic warning box with no message.

I can only guess maybe conflict with another macros.

Please see below the macros i'm running:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven Perry\Documents\Business\Ainsworth
Print & Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order") =
Order With ActiveDocument
.FormFields("order").Result = Format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
End With
End Sub

-------------------------------

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer

'Set up the formatting codes in strCode
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub

'Eliminate blank paragraphs by looking for two carriage returns in
a row iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop

'Strip off final paragraph mark
strAddress = Left(strAddress, Len(strAddress) - 1)
'Insert the modified address at the current insertion point
ActiveDocument.Unprotect Password:="123"
Selection.Range.Text = strAddress
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

-----------------------------------------

Dim sAdd As String
Dim sPassword As String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

In what way does it fail? The macro unlocks the form using your
password 123 resets all the fields except the one called Address,
but ignores any dropdown fields, then relocks the form using your
password? Ther address field content is copied to a variable then
written back to the field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all
fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field
with the bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
Password:="123" ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

If you re-run the macro within the open document, as written it
will simply append the new number. To avoid this (and the need to
unlock the form) replace the bookmark with a form field (here
shown as Text1) with the fillin property disabled. The following
version of the macro will then fill that field with the current
number every time the macro is run. Thus you can run it from a
toolbar button to update the number:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile,
"MacroSettings", "Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") = Order With ActiveDocument
.FormFields("Text1").Result = format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation
ID_" & format(Order, "200#")
End With
End Sub

If you prefer your current approach then you are going to have to
recode to replace the content of the bookmark rather than add to
it . See the example at
http://www.gmayor.com/word_vba_examples.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Suzanne,

Thanks for your reply.

Here is my AutoNew macro. Is there something i need to add to
make this work?

Sub AutoNew()

Order = System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", _
"MacroSettings", "Order")

If Order = "" Then
Order = 1
Else
Order = Order + 1
End If

System.PrivateProfileString("D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt", "MacroSettings", _
"Order") = Order
ActiveDocument.Unprotect
ActiveDocument.Bookmarks("Order").Range.InsertBefo re
Format(Order, "200#") ActiveDocument.SaveAs
FileName:="D:\Profiles\Steven Perry\Documents\Business\Master
File\Quotation File\Quotation ID_" & Format(Order, "200#")
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
NoReset:=True

End Sub

"Suzanne S. Barnhill" wrote:

If the form is created as a template, and if your UserForms (or
whatever they are) are launched by an AutoNew macro, then every
time a new document is created based on the template, your
macros will run. There is no need to close and restart Word;
just create a new document.

Alternatively, you can choose to either hide a UserForm or
close it; in the first instance, it will retain previous
entries.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
Hi there,

I've created a form running various macro codes. One example
of code would be a quotation reference number which updates
when the macro template is fired.

As there are bulk quotations to produce, i'd like the user to
avoid closing
word and restarting just in order to refresh.

I'd like code for a button that would act as if the document
were just fired.

Also a button that may leave a certain field un-refreshed. For
example, several quotes to be produced for the same company
where the company name and
address field would remane un-refreshed.

Many thanks

Steve

Steve

I'm confused with the ResetForm macro and ResetFormFields macro.

Do these run together.

To clear my objective up, i require one button to reset the whole form and
one button to clear the whole form except the address field. This enables the
user to produce multiple quotes without have to insert the address again.

Sorry if i'm bugging you Graham.

"Graham Mayor" wrote:

The problem appears to be that you are unprotecting the document to insert
the address from Outlook directly into the document, much as you did earlier
with the number, coupled with some anomalies relating to the ResetForm
macro.

Your version of the InserrtAddress macro also unlocked the field with the
password 123 then locked it again without the password. It is better to
define the password as a string as in the earlier example and insert the
string variable name in the protect/unprotect commands.

It would be better if you inserted a form field - let's call it 'Address' so
it matches the Address field that the reset macro cannot find . You can then
insert the address from the macro without unlocking the form by using the
same method as before i.e.

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
If strAddress = "" Then Exit Sub
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop
strAddress = Left(strAddress, Len(strAddress) - 1)
ActiveDocument.FormFields("Address").Result = strAddress
End Sub

The ResetForm macro that I posted should replace your ResetForm not be used
in addition to it - and it appears from your listing that it now has no
name?

There remains some confusion over *your* ResetForm macro which appears to
refer to a field that doesn't exist. If it does and has nothing to do with
the InsertAddress function then you would need to rename the field at the
end of the above macro to reflect the form field name that you wish the
address to be inserted into.

I think that should have it covered
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I get a visual basic warning box with no message.

I can only guess maybe conflict with another macros.

Please see below the macros i'm running:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven Perry\Documents\Business\Ainsworth
Print & Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order") =
Order With ActiveDocument
.FormFields("order").Result = Format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
End With
End Sub

-------------------------------

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer

'Set up the formatting codes in strCode
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub

'Eliminate blank paragraphs by looking for two carriage returns in
a row iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop

'Strip off final paragraph mark
strAddress = Left(strAddress, Len(strAddress) - 1)
'Insert the modified address at the current insertion point
ActiveDocument.Unprotect Password:="123"
Selection.Range.Text = strAddress
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

-----------------------------------------

Dim sAdd As String
Dim sPassword As String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

In what way does it fail? The macro unlocks the form using your
password 123 resets all the fields except the one called Address,
but ignores any dropdown fields, then relocks the form using your
password? Ther address field content is copied to a variable then
written back to the field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all
fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field
with the bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
Password:="123" ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

If you re-run the macro within the open document, as written it
will simply append the new number. To avoid this (and the need to
unlock the form) replace the bookmark with a form field (here
shown as Text1) with the fillin property disabled. The following
version of the macro will then fill that field with the current
number every time the macro is run. Thus you can run it from a
toolbar button to update the number:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile,
"MacroSettings", "Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") = Order With ActiveDocument
.FormFields("Text1").Result = format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation
ID_" & format(Order, "200#")
End With
End Sub

If you prefer your current approach then you are going to have to
recode to replace the content of the bookmark rather than add to
it . See the example at
http://www.gmayor.com/word_vba_examples.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Graham Mayor

You are not bugging me

I hadn't appreciated that you required two macros to clear the form in
different ways; however you can so so with one, and for that purpose alone
the form does not require unprotecting:-

Sub ResetForm()
Dim oFld As FormFields
Dim i As Long
Dim sQuery As String
Dim sAdd As String

sQuery = MsgBox("Click Yes to clear the whole form" & vbCr & _
"Click No to leave the address", _
vbYesNoCancel, "Clear Form")
If sQuery = vbCancel Then Exit Sub
Set oFld = ActiveDocument.FormFields
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
If sQuery = vbNo Then
oFld("Address").Result = sAdd
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I'm confused with the ResetForm macro and ResetFormFields macro.

Do these run together.

To clear my objective up, i require one button to reset the whole
form and one button to clear the whole form except the address field.
This enables the user to produce multiple quotes without have to
insert the address again.

Sorry if i'm bugging you Graham.

"Graham Mayor" wrote:

The problem appears to be that you are unprotecting the document to
insert the address from Outlook directly into the document, much as
you did earlier with the number, coupled with some anomalies
relating to the ResetForm macro.

Your version of the InserrtAddress macro also unlocked the field
with the password 123 then locked it again without the password. It
is better to define the password as a string as in the earlier
example and insert the string variable name in the protect/unprotect
commands.

It would be better if you inserted a form field - let's call it
'Address' so it matches the Address field that the reset macro
cannot find . You can then insert the address from the macro without
unlocking the form by using the same method as before i.e.

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
If strAddress = "" Then Exit Sub
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop
strAddress = Left(strAddress, Len(strAddress) - 1)
ActiveDocument.FormFields("Address").Result = strAddress
End Sub

The ResetForm macro that I posted should replace your ResetForm not
be used in addition to it - and it appears from your listing that it
now has no name?

There remains some confusion over *your* ResetForm macro which
appears to refer to a field that doesn't exist. If it does and has
nothing to do with the InsertAddress function then you would need to
rename the field at the end of the above macro to reflect the form
field name that you wish the address to be inserted into.

I think that should have it covered
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I get a visual basic warning box with no message.

I can only guess maybe conflict with another macros.

Please see below the macros i'm running:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
= Order With ActiveDocument
.FormFields("order").Result = Format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
End With
End Sub

-------------------------------

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer

'Set up the formatting codes in strCode
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub

'Eliminate blank paragraphs by looking for two carriage returns
in a row iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop

'Strip off final paragraph mark
strAddress = Left(strAddress, Len(strAddress) - 1)
'Insert the modified address at the current insertion point
ActiveDocument.Unprotect Password:="123"
Selection.Range.Text = strAddress
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

-----------------------------------------

Dim sAdd As String
Dim sPassword As String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

In what way does it fail? The macro unlocks the form using your
password 123 resets all the fields except the one called Address,
but ignores any dropdown fields, then relocks the form using your
password? Ther address field content is copied to a variable then
written back to the field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all
fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field
with the bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
Password:="123" ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

If you re-run the macro within the open document, as written it
will simply append the new number. To avoid this (and the need
to unlock the form) replace the bookmark with a form field
(here shown as Text1) with the fillin property disabled. The
following version of the macro will then fill that field with
the current number every time the macro is run. Thus you can
run it from a toolbar button to update the number:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile,
"MacroSettings", "Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") = Order With ActiveDocument
.FormFields("Text1").Result = format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation
ID_" & format(Order, "200#")
End With
End Sub

If you prefer your current approach then you are going to have
to recode to replace the content of the bookmark rather than
add to it . See the example at
http://www.gmayor.com/word_vba_examples.htm

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve

A much better function although a couple of issues.

Text form fields are reset only. Dropdown fields remaine.

Are you aware when running the macro the cursor runs to every form field?
It's quite ugly.

"Graham Mayor" wrote:

You are not bugging me

I hadn't appreciated that you required two macros to clear the form in
different ways; however you can so so with one, and for that purpose alone
the form does not require unprotecting:-

Sub ResetForm()
Dim oFld As FormFields
Dim i As Long
Dim sQuery As String
Dim sAdd As String

sQuery = MsgBox("Click Yes to clear the whole form" & vbCr & _
"Click No to leave the address", _
vbYesNoCancel, "Clear Form")
If sQuery = vbCancel Then Exit Sub
Set oFld = ActiveDocument.FormFields
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
If sQuery = vbNo Then
oFld("Address").Result = sAdd
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I'm confused with the ResetForm macro and ResetFormFields macro.

Do these run together.

To clear my objective up, i require one button to reset the whole
form and one button to clear the whole form except the address field.
This enables the user to produce multiple quotes without have to
insert the address again.

Sorry if i'm bugging you Graham.

"Graham Mayor" wrote:

The problem appears to be that you are unprotecting the document to
insert the address from Outlook directly into the document, much as
you did earlier with the number, coupled with some anomalies
relating to the ResetForm macro.

Your version of the InserrtAddress macro also unlocked the field
with the password 123 then locked it again without the password. It
is better to define the password as a string as in the earlier
example and insert the string variable name in the protect/unprotect
commands.

It would be better if you inserted a form field - let's call it
'Address' so it matches the Address field that the reset macro
cannot find . You can then insert the address from the macro without
unlocking the form by using the same method as before i.e.

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
If strAddress = "" Then Exit Sub
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop
strAddress = Left(strAddress, Len(strAddress) - 1)
ActiveDocument.FormFields("Address").Result = strAddress
End Sub

The ResetForm macro that I posted should replace your ResetForm not
be used in addition to it - and it appears from your listing that it
now has no name?

There remains some confusion over *your* ResetForm macro which
appears to refer to a field that doesn't exist. If it does and has
nothing to do with the InsertAddress function then you would need to
rename the field at the end of the above macro to reflect the form
field name that you wish the address to be inserted into.

I think that should have it covered
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I get a visual basic warning box with no message.

I can only guess maybe conflict with another macros.

Please see below the macros i'm running:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
= Order With ActiveDocument
.FormFields("order").Result = Format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
End With
End Sub

-------------------------------

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer

'Set up the formatting codes in strCode
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub

'Eliminate blank paragraphs by looking for two carriage returns
in a row iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop

'Strip off final paragraph mark
strAddress = Left(strAddress, Len(strAddress) - 1)
'Insert the modified address at the current insertion point
ActiveDocument.Unprotect Password:="123"
Selection.Range.Text = strAddress
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

-----------------------------------------

Dim sAdd As String
Dim sPassword As String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

In what way does it fail? The macro unlocks the form using your
password 123 resets all the fields except the one called Address,
but ignores any dropdown fields, then relocks the form using your
password? Ther address field content is copied to a variable then
written back to the field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all
fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field
with the bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro

Suzanne S. Barnhill

Turning screen updating off might help.

--
Suzanne S. Barnhill
Microsoft MVP (Word)
Words into Type
Fairhope, Alabama USA

"Steve" wrote in message
...
A much better function although a couple of issues.

Text form fields are reset only. Dropdown fields remaine.

Are you aware when running the macro the cursor runs to every form field?
It's quite ugly.

"Graham Mayor" wrote:

You are not bugging me

I hadn't appreciated that you required two macros to clear the form in
different ways; however you can so so with one, and for that purpose
alone
the form does not require unprotecting:-

Sub ResetForm()
Dim oFld As FormFields
Dim i As Long
Dim sQuery As String
Dim sAdd As String

sQuery = MsgBox("Click Yes to clear the whole form" & vbCr & _
"Click No to leave the address", _
vbYesNoCancel, "Clear Form")
If sQuery = vbCancel Then Exit Sub
Set oFld = ActiveDocument.FormFields
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
If sQuery = vbNo Then
oFld("Address").Result = sAdd
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I'm confused with the ResetForm macro and ResetFormFields macro.

Do these run together.

To clear my objective up, i require one button to reset the whole
form and one button to clear the whole form except the address field.
This enables the user to produce multiple quotes without have to
insert the address again.

Sorry if i'm bugging you Graham.

"Graham Mayor" wrote:

The problem appears to be that you are unprotecting the document to
insert the address from Outlook directly into the document, much as
you did earlier with the number, coupled with some anomalies
relating to the ResetForm macro.

Your version of the InserrtAddress macro also unlocked the field
with the password 123 then locked it again without the password. It
is better to define the password as a string as in the earlier
example and insert the string variable name in the protect/unprotect
commands.

It would be better if you inserted a form field - let's call it
'Address' so it matches the Address field that the reset macro
cannot find . You can then insert the address from the macro without
unlocking the form by using the same method as before i.e.

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
If strAddress = "" Then Exit Sub
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop
strAddress = Left(strAddress, Len(strAddress) - 1)
ActiveDocument.FormFields("Address").Result = strAddress
End Sub

The ResetForm macro that I posted should replace your ResetForm not
be used in addition to it - and it appears from your listing that it
now has no name?

There remains some confusion over *your* ResetForm macro which
appears to refer to a field that doesn't exist. If it does and has
nothing to do with the InsertAddress function then you would need to
rename the field at the end of the above macro to reflect the form
field name that you wish the address to be inserted into.

I think that should have it covered
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I get a visual basic warning box with no message.

I can only guess maybe conflict with another macros.

Please see below the macros i'm running:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings", "Order")
= Order With ActiveDocument
.FormFields("order").Result = Format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation ID_" &
Format(Order, "200#")
End With
End Sub

-------------------------------

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer

'Set up the formatting codes in strCode
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress = Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True)
'If user cancelled out of 'Select Name' dialog, quit
If strAddress = "" Then Exit Sub

'Eliminate blank paragraphs by looking for two carriage returns
in a row iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop

'Strip off final paragraph mark
strAddress = Left(strAddress, Len(strAddress) - 1)
'Insert the modified address at the current insertion point
ActiveDocument.Unprotect Password:="123"
Selection.Range.Text = strAddress
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True

End Sub

-----------------------------------------

Dim sAdd As String
Dim sPassword As String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields, Password:="123"
ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

In what way does it fail? The macro unlocks the form using your
password 123 resets all the fields except the one called Address,
but ignores any dropdown fields, then relocks the form using your
password? Ther address field content is copied to a variable then
written back to the field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all
fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field
with the bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro

Graham Mayor

If the tracking of each field bothers you, as Suzanne says you can stop the
screen updating for the duration,
Add the line
Application.ScreenUpdating = False
before the line beginning sQuery
and
Application.ScreenUpdating = True
before End Sub

The issue with drop down fields is more complicated as dropdown fields have
a limited number of pre-defined entries. You would have to reset each
dropdown field individually to whatever setting you wanted as its initial
display. None of those settings will be the nul entry used to clear the
form.. As an alternative to leaving the dropdown fields untouched, Replace
the For Next loop with the following version in which the dropdown fields
will be set to the first items in the dropdown lists.

For i = 1 To oFld.Count
oFld(i).Select
If oFld(i).Type = wdFieldFormDropDown Then
oFld(i).Result = oFld(i).DropDown.ListEntries(1).name
Else
oFld(i).Result = ""
End If
Next

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
A much better function although a couple of issues.

Text form fields are reset only. Dropdown fields remaine.

Are you aware when running the macro the cursor runs to every form
field? It's quite ugly.

"Graham Mayor" wrote:

You are not bugging me

I hadn't appreciated that you required two macros to clear the form
in different ways; however you can so so with one, and for that
purpose alone the form does not require unprotecting:-

Sub ResetForm()
Dim oFld As FormFields
Dim i As Long
Dim sQuery As String
Dim sAdd As String

sQuery = MsgBox("Click Yes to clear the whole form" & vbCr & _
"Click No to leave the address", _
vbYesNoCancel, "Clear Form")
If sQuery = vbCancel Then Exit Sub
Set oFld = ActiveDocument.FormFields
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
If sQuery = vbNo Then
oFld("Address").Result = sAdd
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I'm confused with the ResetForm macro and ResetFormFields macro.

Do these run together.

To clear my objective up, i require one button to reset the whole
form and one button to clear the whole form except the address
field. This enables the user to produce multiple quotes without
have to insert the address again.

Sorry if i'm bugging you Graham.

"Graham Mayor" wrote:

The problem appears to be that you are unprotecting the document to
insert the address from Outlook directly into the document, much as
you did earlier with the number, coupled with some anomalies
relating to the ResetForm macro.

Your version of the InserrtAddress macro also unlocked the field
with the password 123 then locked it again without the password. It
is better to define the password as a string as in the earlier
example and insert the string variable name in the
protect/unprotect commands.

It would be better if you inserted a form field - let's call it
'Address' so it matches the Address field that the reset macro
cannot find . You can then insert the address from the macro
without unlocking the form by using the same method as before i.e.

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr
strAddress =
Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True) If
strAddress = "" Then Exit Sub iDoubleCR = InStr(strAddress,
vbCr & vbCr) Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop
strAddress = Left(strAddress, Len(strAddress) - 1)
ActiveDocument.FormFields("Address").Result = strAddress
End Sub

The ResetForm macro that I posted should replace your ResetForm not
be used in addition to it - and it appears from your listing that
it now has no name?

There remains some confusion over *your* ResetForm macro which
appears to refer to a field that doesn't exist. If it does and has
nothing to do with the InsertAddress function then you would need
to rename the field at the end of the above macro to reflect the
form field name that you wish the address to be inserted into.

I think that should have it covered
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I get a visual basic warning box with no message.

I can only guess maybe conflict with another macros.

Please see below the macros i'm running:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") = Order With ActiveDocument
.FormFields("order").Result = Format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation
ID_" & Format(Order, "200#")
End With
End Sub

-------------------------------

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer

'Set up the formatting codes in strCode
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress =
Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True) 'If
user cancelled out of 'Select Name' dialog, quit If
strAddress = "" Then Exit Sub

'Eliminate blank paragraphs by looking for two carriage returns
in a row iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop

'Strip off final paragraph mark
strAddress = Left(strAddress, Len(strAddress) - 1)
'Insert the modified address at the current insertion point
ActiveDocument.Unprotect Password:="123"
Selection.Range.Text = strAddress
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
NoReset:=True

End Sub

-----------------------------------------

Dim sAdd As String
Dim sPassword As String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
Password:="123" ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

In what way does it fail? The macro unlocks the form using your
password 123 resets all the fields except the one called Address,
but ignores any dropdown fields, then relocks the form using your
password? Ther address field content is copied to a variable then
written back to the field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all
fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field
with the bookmark name 'Address'

Sub ResetFormFields()
Dim bProtected As Boolean
Dim oFld As FormFields
Dim i As Long
Dim sAdd As String
Dim sPassword as String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True,
Password:=sPassword End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

Thanks for that, it works a treat.

One other thing. I'd like to reset the form but not all of it.
Example the address field remains un-reset.

Sub ResetForm()
'
' ResetForm Macro

Steve

It works but still not smoothly.

Thank you for your time and help guys.

Steve

"Graham Mayor" wrote:

If the tracking of each field bothers you, as Suzanne says you can stop the
screen updating for the duration,
Add the line
Application.ScreenUpdating = False
before the line beginning sQuery
and
Application.ScreenUpdating = True
before End Sub

The issue with drop down fields is more complicated as dropdown fields have
a limited number of pre-defined entries. You would have to reset each
dropdown field individually to whatever setting you wanted as its initial
display. None of those settings will be the nul entry used to clear the
form.. As an alternative to leaving the dropdown fields untouched, Replace
the For Next loop with the following version in which the dropdown fields
will be set to the first items in the dropdown lists.

For i = 1 To oFld.Count
oFld(i).Select
If oFld(i).Type = wdFieldFormDropDown Then
oFld(i).Result = oFld(i).DropDown.ListEntries(1).name
Else
oFld(i).Result = ""
End If
Next

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
A much better function although a couple of issues.

Text form fields are reset only. Dropdown fields remaine.

Are you aware when running the macro the cursor runs to every form
field? It's quite ugly.

"Graham Mayor" wrote:

You are not bugging me

I hadn't appreciated that you required two macros to clear the form
in different ways; however you can so so with one, and for that
purpose alone the form does not require unprotecting:-

Sub ResetForm()
Dim oFld As FormFields
Dim i As Long
Dim sQuery As String
Dim sAdd As String

sQuery = MsgBox("Click Yes to clear the whole form" & vbCr & _
"Click No to leave the address", _
vbYesNoCancel, "Clear Form")
If sQuery = vbCancel Then Exit Sub
Set oFld = ActiveDocument.FormFields
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
If sQuery = vbNo Then
oFld("Address").Result = sAdd
End If
End Sub

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I'm confused with the ResetForm macro and ResetFormFields macro.

Do these run together.

To clear my objective up, i require one button to reset the whole
form and one button to clear the whole form except the address
field. This enables the user to produce multiple quotes without
have to insert the address again.

Sorry if i'm bugging you Graham.

"Graham Mayor" wrote:

The problem appears to be that you are unprotecting the document to
insert the address from Outlook directly into the document, much as
you did earlier with the number, coupled with some anomalies
relating to the ResetForm macro.

Your version of the InserrtAddress macro also unlocked the field
with the password 123 then locked it again without the password. It
is better to define the password as a string as in the earlier
example and insert the string variable name in the
protect/unprotect commands.

It would be better if you inserted a form field - let's call it
'Address' so it matches the Address field that the reset macro
cannot find . You can then insert the address from the macro
without unlocking the form by using the same method as before i.e.

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr
strAddress =
Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True) If
strAddress = "" Then Exit Sub iDoubleCR = InStr(strAddress,
vbCr & vbCr) Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop
strAddress = Left(strAddress, Len(strAddress) - 1)
ActiveDocument.FormFields("Address").Result = strAddress
End Sub

The ResetForm macro that I posted should replace your ResetForm not
be used in addition to it - and it appears from your listing that
it now has no name?

There remains some confusion over *your* ResetForm macro which
appears to refer to a field that doesn't exist. If it does and has
nothing to do with the InsertAddress function then you would need
to rename the field at the end of the above macro to reflect the
form field name that you wish the address to be inserted into.

I think that should have it covered
--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
I get a visual basic warning box with no message.

I can only guess maybe conflict with another macros.

Please see below the macros i'm running:

Sub AutoNew()
Dim SettingsFile As String
Dim Order As String
SettingsFile = "D:\Profiles\Steven
Perry\Documents\Business\Ainsworth Print &
Design\Templates\Quotation Number.Txt"
Order = System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") If Order = "" Then
Order = 1
Else
Order = Order + 1
End If
System.PrivateProfileString(SettingsFile, "MacroSettings",
"Order") = Order With ActiveDocument
.FormFields("order").Result = Format(Order, "200#")
.SaveAs FileName:="D:\Profiles\Steven
Perry\Documents\Business\Master File\Quotation File\Quotation
ID_" & Format(Order, "200#")
End With
End Sub

-------------------------------

Public Sub InsertAddressFromOutlook()
Dim strCode As String, strAddress As String
Dim iDoubleCR As Integer

'Set up the formatting codes in strCode
strCode = "PR_GIVEN_NAME PR_SURNAME" & vbCr & _
"PR_COMPANY_NAME" & vbCr & _
"PR_POSTAL_ADDRESS" & vbCr

'Display the 'Select Name' dialog, which lets the user choose
'a name from their Outlook address book
strAddress =
Application.GetAddress(AddressProperties:=strCode, _
UseAutoText:=False, DisplaySelectDialog:=1, _
RecentAddressesChoice:=True, UpdateRecentAddresses:=True) 'If
user cancelled out of 'Select Name' dialog, quit If
strAddress = "" Then Exit Sub

'Eliminate blank paragraphs by looking for two carriage returns
in a row iDoubleCR = InStr(strAddress, vbCr & vbCr)
Do While iDoubleCR 0
strAddress = Left(strAddress, iDoubleCR - 1) & _
Mid(strAddress, iDoubleCR + 1)
iDoubleCR = InStr(strAddress, vbCr & vbCr)
Loop

'Strip off final paragraph mark
strAddress = Left(strAddress, Len(strAddress) - 1)
'Insert the modified address at the current insertion point
ActiveDocument.Unprotect Password:="123"
Selection.Range.Text = strAddress
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
NoReset:=True

End Sub

-----------------------------------------

Dim sAdd As String
Dim sPassword As String
sPassword = "123"
Set oFld = ActiveDocument.FormFields
'Unprotect the file
If ActiveDocument.ProtectionType wdNoProtection Then
bProtected = True
ActiveDocument.Unprotect Password:=sPassword
End If
'Format all fields as not hidden
sAdd = oFld("Address").Result
For i = 1 To oFld.Count
oFld(i).Select
Selection.Font.Hidden = False
If oFld(i).Type wdFieldFormDropDown Then
oFld(i).Result = ""
End If
Next
oFld("Address").Result = sAdd
'Reprotect the document.
If bProtected = True Then
ActiveDocument.Protect _
Type:=wdAllowOnlyFormFields, NoReset:=True, Password:=sPassword
End If
End Sub

Sub ResetForm()
'
' ResetForm Macro
'
'
ActiveDocument.Unprotect Password:="123"
ActiveDocument.Protect Type:=wdAllowOnlyFormFields,
Password:="123" ActiveDocument.FormFields("Address").Select

End Sub

"Graham Mayor" wrote:

In what way does it fail? The macro unlocks the form using your
password 123 resets all the fields except the one called Address,
but ignores any dropdown fields, then relocks the form using your
password? Ther address field content is copied to a variable then
written back to the field.

--

Graham Mayor - Word MVP

My web site www.gmayor.com
Word MVP web site http://word.mvps.org

Steve wrote:
Hi Graham,

The code you gave fails.

The document is protected upon opening.

The code runs when the document is unprotected but resets all
fields.

Regards

Steve

"Graham Mayor" wrote:

The following will reset all the form fields except for a field
with the bookmark name 'Address'

Sub ResetFormFields()

Thread Tools
Show Printable Version
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Form VBA code for checking fields	Leslie Pollak	Microsoft Word Help	1	December 14th 07 03:52 PM
Using a form to fill in field code text	Marc	Microsoft Word Help	1	May 2nd 07 01:08 PM
Refresh Form option buttons	Susan	Microsoft Word Help	1	February 17th 06 12:40 AM
Form Letter Mail Merge using field code: {Database}	jyan	Mailmerge	8	December 19th 05 09:18 PM
Refresh fields by code - specifically {INCLUDEPICTURE}	john smith	Mailmerge	2	December 14th 04 04:24 AM

Menu

About Us