Select a Combobox - display results in 2 others

[Andy S via OfficeKB.com]

i have a word document that i am trying to semi automate a few of the
functions i have put on the page...

Example...
I have 1 drop box and and 2 text boxes on my word document. i want to be able
to change JUST the salary drop down box and both the grade and the shift
premium would then adjust automatically.

Can this be done..

the thing i am struggling with is where would i store the data to say that if
the salary is £14,527 the Grade would be "A" and the shift premium would be
"15%"

please any help on this matter would be much appreciated.

i can email an example of this if needed

cheers

Andy

 

[Jean-Guy Marcil]

Andy S via OfficeKB.com was telling us:
Andy S via OfficeKB.com nous racontait que :

i have a word document that i am trying to semi automate a few of the
functions i have put on the page...

Example...
I have 1 drop box and and 2 text boxes on my word document. i want to
be able to change JUST the salary drop down box and both the grade
and the shift premium would then adjust automatically.

Can this be done..

the thing i am struggling with is where would i store the data to say
that if the salary is £14,527 the Grade would be "A" and the shift
premium would be "15%"

Before answering, how many grade and % values are we talking about?
Would these be subject to change regularly or are the values fairly stable?


--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Andy S via OfficeKB.com]

there are 6 Grades (A-F) and 2 % Values(15% and 30%)

the user would change the Salary amount using the combobox,

values for this..(Something like this):

Salary Grade Shift Payment
12,850 A 15%
14,527 B 15%
14,036 C 15%
17,571 D 30%
19,637 E 30%
22,198 F 30%

As for the data being static, in Principle Yes, although i could change this
year on year as needed

Many thnaks for you help

Andy

Andy S via OfficeKB.com was telling us:
Andy S via OfficeKB.com nous racontait que :

i have a word document that i am trying to semi automate a few of the
functions i have put on the page...

[quoted text clipped - 9 lines]

that if the salary is £14,527 the Grade would be "A" and the shift
premium would be "15%"

Before answering, how many grade and % values are we talking about?
Would these be subject to change regularly or are the values fairly stable?

 

[Word Heretic]

G'day "Andy S via OfficeKB.com" <u2194@uwe>,

You hook a macro into the RunOnExit property of the dropdown. It looks
at the value from its selection, unprotectes the document, scribbles
the answers into a couple of bookmarks or absolute table cells (eg,
row 2, cell 3), reprotects the document without resetting the fields,
and passes control back to the user.

Steve Hudson - Word Heretic

steve from wordheretic.com (Email replies require payment)
Without prejudice


Andy S via OfficeKB.com reckoned:

 

[Jean-Guy Marcil]

Andy S via OfficeKB.com was telling us:
Andy S via OfficeKB.com nous racontait que :

there are 6 Grades (A-F) and 2 % Values(15% and 30%)

the user would change the Salary amount using the combobox,

values for this..(Something like this):

Salary Grade Shift Payment
12,850 A 15%
14,527 B 15%
14,036 C 15%
17,571 D 30%
19,637 E 30%
22,198 F 30%

As for the data being static, in Principle Yes, although i could
change this year on year as needed

Many thnaks for you help

Andy

Andy S via OfficeKB.com was telling us:
Andy S via OfficeKB.com nous racontait que :

i have a word document that i am trying to semi automate a few of
the functions i have put on the page...

[quoted text clipped - 9 lines]

that if the salary is £14,527 the Grade would be "A" and the shift
premium would be "15%"

Before answering, how many grade and % values are we talking about?
Would these be subject to change regularly or are the values fairly
stable?

Try something like this to get you going:

Sub ChooseValues()

Dim SalaryLng As Long
Const fieldPercentName As String = "Percent"
Const fieldGradeName As String = "Grade"
Const fieldSalaryName As String = "Salary"

SalaryLng = CLng(ActiveDocument.FormFields(fieldSalaryName).Result)

Select Case SalaryLng
Case Is <= 13000
ActiveDocument.FormFields(fieldGradeName).Result = "A"
Case Is <= 14000
ActiveDocument.FormFields(fieldGradeName).Result = "B"
Case Is <= 15000
ActiveDocument.FormFields(fieldGradeName).Result = "C"
Case Is <= 18000
ActiveDocument.FormFields(fieldGradeName).Result = "D"
Case Is <= 20000
ActiveDocument.FormFields(fieldGradeName).Result = "D"
Case Is <= 23000
ActiveDocument.FormFields(fieldGradeName).Result = "E"
End Select

If SalaryLng <= 15000 Then
ActiveDocument.FormFields(fieldPercentName).Result = "15%"
Else
ActiveDocument.FormFields(fieldPercentName).Result = "30%"
End If

End Sub



--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Doug Robbins - Word MVP]

I would maintain the data in a separate document and use a modification of
the following to load it into a listbox (or combobox) on a UserForm:

This routine loads a listbox with client details stored in a table in a
separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

there are 6 Grades (A-F) and 2 % Values(15% and 30%)

the user would change the Salary amount using the combobox,

values for this..(Something like this):

Salary Grade Shift Payment
12,850 A 15%
14,527 B 15%
14,036 C 15%
17,571 D 30%
19,637 E 30%
22,198 F 30%

As for the data being static, in Principle Yes, although i could change
this
year on year as needed

Many thnaks for you help

Andy

Andy S via OfficeKB.com was telling us:
Andy S via OfficeKB.com nous racontait que :

i have a word document that i am trying to semi automate a few of the
functions i have put on the page...

[quoted text clipped - 9 lines]

that if the salary is £14,527 the Grade would be "A" and the shift
premium would be "15%"

Before answering, how many grade and % values are we talking about?
Would these be subject to change regularly or are the values fairly
stable?

 

[Andy S via OfficeKB.com]

I have tried to follow your instructions have done exactly as follows:

I have added a form called "userform1" then on that added a listbox
"listbox1" and also a button called" command"button1"

i have also added a new document to my desktop called: "salary.doc" which
contains the following

Salary Grade Shift Payment
12,850 A 15%
14,527 B 15%
14,036 C 15%
17,571 D 30%
19,637 E 30%
22,198 F 30%

in my main document called "Contract Template.doc"

on this document i have three text boxes from the forms controls, they are
called "txtGrade", "txtSalary" and "txtPremium"

the code that you gave me populates the listbox with tthe data stored in the
Salary document..

however when i click the button, the code falls down, how do i get the
chosen text from the selection boxes to populate the three appropriate
txtboxes on my document...

Many thanks

Andy

I would maintain the data in a separate document and use a modification of
the following to load it into a listbox (or combobox) on a UserForm:

This routine loads a listbox with client details stored in a table in a
separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize() and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.

there are 6 Grades (A-F) and 2 % Values(15% and 30%)

[quoted text clipped - 30 lines]

 

[Doug Robbins - Word MVP]

In the command button click event you would need

Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.FormFields("txtSalary").Result = ListBox1.Value
ListBox1.BoundColumn = 2

ActiveDocument.FormFields("txtGrade").Result = ListBox1.Value
ListBox1.BoundColumn = 3

ActiveDocument.FormFields("txtPremium").Result = ListBox1.Value
UserForm1.Hide
End Sub

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

I have tried to follow your instructions have done exactly as follows:

I have added a form called "userform1" then on that added a listbox
"listbox1" and also a button called" command"button1"

i have also added a new document to my desktop called: "salary.doc" which
contains the following

Salary Grade Shift Payment
12,850 A 15%
14,527 B 15%
14,036 C 15%
17,571 D 30%
19,637 E 30%
22,198 F 30%

in my main document called "Contract Template.doc"

on this document i have three text boxes from the forms controls, they are
called "txtGrade", "txtSalary" and "txtPremium"

the code that you gave me populates the listbox with tthe data stored in
the
Salary document..

however when i click the button, the code falls down, how do i get the
chosen text from the selection boxes to populate the three appropriate
txtboxes on my document...

Many thanks

Andy

I would maintain the data in a separate document and use a modification of
the following to load it into a listbox (or combobox) on a UserForm:

This routine loads a listbox with client details stored in a table in a
separate
document (which makes it easy to maintain with additions, deletions etc.),
that document being saved as Clients.Doc for the following code.

On the UserForm, have a list box (ListBox1) and a Command Button
(CommandButton1) and use the following code in the UserForm_Initialize()
and
the CommandButton1_Click() routines

Private Sub UserForm_Initialize()
Dim sourcedoc As Document, i As Integer, j As Integer, myitem As
Range,
m As Long, n As Long
' Modify the path in the following line so that it matches where you
saved Clients.doc
Application.ScreenUpdating = False
' Open the file containing the client details
Set sourcedoc = Documents.Open(FileName:="e:\worddocs\Clients.doc")
' Get the number or clients = number of rows in the table of client
details less one
i = sourcedoc.Tables(1).Rows.Count - 1
' Get the number of columns in the table of client details
j = sourcedoc.Tables(1).Columns.Count
' Set the number of columns in the Listbox to match
' the number of columns in the table of client details
ListBox1.ColumnCount = j
' Define an array to be loaded with the client data
Dim MyArray() As Variant
'Load client data into MyArray
ReDim MyArray(i, j)
For n = 0 To j - 1
For m = 0 To i - 1
Set myitem = sourcedoc.Tables(1).Cell(m + 2, n + 1).Range
myitem.End = myitem.End - 1
MyArray(m, n) = myitem.Text
Next m
Next n
' Load data into ListBox1
ListBox1.List() = MyArray
' Close the file containing the client details
sourcedoc.Close SaveChanges:=wdDoNotSaveChanges
End Sub

Private Sub CommandButton1_Click()
Dim i As Integer, Addressee As String
Addressee = ""
For i = 1 To ListBox1.ColumnCount
ListBox1.BoundColumn = i
Addressee = Addressee & ListBox1.Value & vbCr
Next i
ActiveDocument.Bookmarks("Addressee").Range.InsertAfter Addressee
UserForm2.Hide
End Sub

The Initialize statement will populate the listbox with the data from the
table and then when a client is selected in from the list and the command
button is clicked, the information for that client will be inserted into a
bookmark in the document. You may want to vary the manner in which it is
inserted to suit our exact requirements, but hopefully this will get you
started.

To make it easy for you, the code has been written so that it will deal
with
any number of clients and any number of details about each client. It
assumes that the first row of the table containing the client details is a
header row.

there are 6 Grades (A-F) and 2 % Values(15% and 30%)

[quoted text clipped - 30 lines]

Would these be subject to change regularly or are the values fairly
stable?

 

[Andy S via OfficeKB.com]

Hi Doug,

That works a n absolute treat...

i do however have one other problem relating to this...

how do i get the Userform to display when i open the document?

Ideally i would like to have a new buton on the menu bar which when clicked
would pop up the Userform
any ideas please?

Andy

In the command button click event you would need

Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.FormFields("txtSalary").Result = ListBox1.Value
ListBox1.BoundColumn = 2

ActiveDocument.FormFields("txtGrade").Result = ListBox1.Value
ListBox1.BoundColumn = 3

ActiveDocument.FormFields("txtPremium").Result = ListBox1.Value
UserForm1.Hide
End Sub

I have tried to follow your instructions have done exactly as follows:

[quoted text clipped - 105 lines]

 

[Word Heretic]

G'day "Andy S via OfficeKB.com" <u2194@uwe>,

An AutoOpen macro (see Help) or a Document_Open event in the
ThisDocument module (see Help) can spot the opening of a document.
AutOpen runs on every doc open, a Document_Open only runs when
documents of that template are opened.

Then you need to use an Ontime command to delay the call of the user
form until after Word has settled the document in place properly.
There should be an example at word.mvps.org/FAQs


Steve Hudson - Word Heretic

steve from wordheretic.com (Email replies require payment)
Without prejudice


Andy S via OfficeKB.com reckoned:

Hi Doug,

That works a n absolute treat...

i do however have one other problem relating to this...

how do i get the Userform to display when i open the document?

Ideally i would like to have a new buton on the menu bar which when clicked
would pop up the Userform
any ideas please?

Andy

In the command button click event you would need

Private Sub CommandButton1_Click()
ListBox1.BoundColumn = 1
ActiveDocument.FormFields("txtSalary").Result = ListBox1.Value
ListBox1.BoundColumn = 2

ActiveDocument.FormFields("txtGrade").Result = ListBox1.Value
ListBox1.BoundColumn = 3

ActiveDocument.FormFields("txtPremium").Result = ListBox1.Value
UserForm1.Hide
End Sub

I have tried to follow your instructions have done exactly as follows:

[quoted text clipped - 105 lines]

Would these be subject to change regularly or are the values fairly
stable?