Re: condition formula in Word 2003

You are welcome.

It may be a bit late now but it occurs that you could initially prompt the
user for the address and store that in the ini file for that user rather
than the branch number. This would mean a simple user form to gather the
data, but it would save you from having to make all those case statements
and the user would be responsible for ensuring the address is kept up to
date..

I have an example template if you are interested in this approach
http://www.gmayor.com/Extras/Address_Template.dot. It works on the same
principle as the macro in my last message, and can be modified to your
requirements. It includes a macro to reset the address.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

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




m2work wrote:
Hi Graham,

Greatly appreciate your time and effort.

Many thanks,
m2


"Graham Mayor" wrote:

OK - Plan B. Let's forget about the form and the check box (which
would also need to be in a protected form). Let's go the ini file
route.

Put a bookmark in the document template where you want the branch
information to appear and call that bookmark 'Branch'.

The following macro saved in the document template (not the normal
template) will insert whatever detail you want associated with a
particular branch number into the bookmark when you create a new
document from the template.

I have set up two fictitious addresses associated with branch
numbers 10 and
20. You will need to set up case statements for every address you
require and associate them with the branch numbers. Where the branch
number is not associated with an address nothing is entered in the
bookmark

When the macro runs, it checks for the presence of the entry in the
settings.ini text file. If it doesn't find the entry it prompts for
the branch number. I will have to assume your users know the number
of their branch? I have also added a macro to reset the number
should a user screw it up or while testing the various branch data..

Sub AutoNew()
'macro requires a bookmark in the document called 'Branch'!

Dim SettingsFile As String
Dim Branch As String
Dim rBranchText As Range
Dim i As Long

Start:
SettingsFile = Options.DefaultFilePath(wdStartupPath) &
"\Settings.ini" Branch = System.PrivateProfileString(SettingsFile, _
"Branch Number", "Branch")
If Branch = "" Then
Branch = InputBox("Enter the local branch number", "Branch
number") GoTo UpdateSettings
End If
Set rBranchText = ActiveDocument.Bookmarks("Branch").Range

Select Case Branch
Case Is = 10
rBranchText.Text = "1 Smith Street" & vbCr & _
"Manchester" & vbCr & "M1 2AZ"
Case Is = 20
rBranchText.Text = "2 Any Street" & vbCr & _
"London" & vbCr & "W1 1EW"
Case Else
rBranchText.Text = ""
End Select

With ActiveDocument
.Bookmarks.Add "Branch", rBranchText
End With
Exit Sub
UpdateSettings:
System.PrivateProfileString(SettingsFile, "Branch Number", _
"Branch") = Branch
GoTo Start:
End Sub

Sub ResetBranch()
Dim SettingsFile As String
Dim Branch As String
Dim sQuery As String
SettingsFile = Options.DefaultFilePath(wdStartupPath) &
"\Settings.ini"

Branch = System.PrivateProfileString(SettingsFile, _
"Branch Number", "Branch")
sQuery = InputBox("Reset branch number?", "Reset", Branch)
If sQuery = "" Then Exit Sub
Branch = sQuery
System.PrivateProfileString(SettingsFile, "Branch Number", _
"Branch") = Branch
End Sub


--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

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


m2work wrote:
Hi Graham,

I tried, but that does not really work. It always gives me a
run-time error '5941': The requested member of the collection does
not exist. I have already added the drop down list and 2 bookmarks
labeled as Text1 and Text2.

In addition, if I do a form, I need to protect the form in order to
get drop down to work. Unfortunately, I was designing this as a
quotation form, so the users must be able to enter details.

All I was trying to accomplish is if checkbox for Branch 10 is
checked, the branch address, tel/fax no. for branch 10 will display
at the bottom of the page. If checkbox for Branch 20 is checked,
the branch address, tel/fax no. for branch 20 will display at the
bottom of the page.



Many thanks again,
m2


"Graham Mayor" wrote:

If this is a form, you could use a dropdown field to select the
Branch and run a macro to fill in other fields accordingly - a
variation on the following which puts an address in form field
Text1 and a phone number in Text2

Sub OnExitDD1()
'fills text field based on content of _
dropdown field

Dim oFld As FormFields
Set oFld = ActiveDocument.FormFields
Select Case oFld("Dropdown1").Result
Case Is = "Bristol"
oFld("Text1").Result = "1 Some Street" & vbCr _
& "Market Place" & vbCr & _
"Bristol"
oFld("Text2").Result = "123-4567"
Case Is = "London"
oFld("Text1").Result = "1 Another Street" & vbCr _
& "Some Place" & vbCr & _
"London"
oFld("Text2").Result = "911-1111"
Case Is = "Manchester"
oFld("Text1").Result = "1 Athird Street" & vbCr _
& "Yetanother Place" & vbCr & _
"Manchester"
oFld("Text2").Result = "456-9999"
Case Else
'Do nothing
End Select
End Sub

If your organisation stores the local addresses in Options > User
Information then you could insert a variety of data dependant on
looking for a unique string related to the branch e.g. the
following will look in the user address information for the City
and fill in Text1 and Text2 with the same information as above

Sub AutoNew()
'fills text field based on content of UserAddress
Dim oFld As FormFields
Dim sAddr As String

Set oFld = ActiveDocument.FormFields
sAddr = Application.UserAddress

If InStr(1, sAddr, "Bristol") Then _
oFld("Text1").Result = "1 Some Street" & vbCr _
& "Market Place" & vbCr & _
"Bristol"
oFld("Text2").Result = "123-4567"
If InStr(1, sAddr, "London") Then _
oFld("Text1").Result = "1 Another Street" & vbCr _
& "Some Place" & vbCr & _
"London"
oFld("Text2").Result = "911-1111"
If InStr(1, sAddr, "Manchester") Then _
oFld("Text1").Result = "1 Athird Street" & vbCr _
& "Yetanother Place" & vbCr & _
"Manchester"
oFld("Text2").Result = "456-9999"
End Sub

If you simply want to insert the UserAddress in Text1 you could
replace all of the latter with

Dim oFld As FormFields
Dim sAddr As String
Set oFld = ActiveDocument.FormFields
sAddr = Application.UserAddress
oFld("Text1").Result = Replace(sAddr, Chr(10), "")

You could also store the required information for each branch in an
ini file for the branch and automatically insert the information
from the ini file. See
http://www.gmayor.com/automatic_numbering_documents.htm for
techniques.

There are lots of ways to approach this depending on what you have
locally or what you can set up.

--
<>>< ><<> ><<> <>>< ><<> <>>< <>><<>
Graham Mayor - Word MVP

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



m2work wrote:
ok. Thanks

I haven't really found any sites that have good tutorials on
Select Case. Sorry. I'd write it for you if I had the time, but
I don't right now.

Perhaps you could repost asking for help with creating a Select
Case or If statement macro to help you out with your check boxes.

"m2work" wrote:

Thanks for the reply.

Unfortunately, I have not done any macros before. Is there a
tutorial online on how to do the Select Case macro?

Thanks,
m2


"finalword" wrote:

I think you are going to need to write a macro for this one and
attach it to the check box field through the field properties.
A Select Case macro should do the trick.

"m2work" wrote:

Hello,

I am working in a company that has mutliple branches. I would
like to use 1 form for all the branches. I have set up just
the name of the branch at the bottom of the page with
checkboxes. What I would like to do is - if they check on any
given box, the information for that specific branch will
display underneath the company logo. Is there a way to do
this instead of listing 15 addresses, tel nos and fax nos at
the bottom of the page?

Any help is greatly appreciated.

Thanks,
m2


.

Relevant Pages

  • Project Error
    ... Private Declare Sub Sleep Lib "Kernel32" ... Dim strDataSrc As String ...
    (microsoft.public.vb.bugs)
  • Re: condition formula in Word 2003
    ... The following macro saved in the document template ... Sub AutoNew() ... Dim SettingsFile As String ...
    (microsoft.public.word.docmanagement)
  • Re: condition formula in Word 2003
    ... The following macro saved in the document template ... Sub AutoNew() ... Dim SettingsFile As String ...
    (microsoft.public.word.docmanagement)
  • Re: Is there a way to prevent a RichTextBox from scrolling?
    ... Private _isRegex As Boolean ... Public Sub New(ByVal thispattern As String, ... Dim entry As tDict ...
    (microsoft.public.dotnet.framework.windowsforms.controls)
  • Re: Form refresh code
    ... Are you aware when running the macro the cursor runs to every form field? ... Sub ResetForm() ... Dim oFld As FormFields ... Dim sQuery As String ...
    (microsoft.public.word.docmanagement)
Loading