Use of Macro/VBA

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jay - 28 Mar 2007 02:55 GMT

I I am not much familiar with the programming aspect (I am in quality
engineering field), so, I have come to this place for help. Please help me!

I have designed a Customer Satisfaction Survey. There are 4 questions. For
every question, customer may choose Excellence/Very Good/Good/Poor. I have
designed a drop down list for above. Now I want to assign a number for each
choice. For example, if customer picks up 'Excellent', then numerical number
4 will automatically be assigned to that questions, if 'Very Good' then 3,
and so on. At the end there will be summation of the rating for 4 questions
and % Customer Satisfaction Score will be determined by Total/16*100.
We would very much appreciate your help in this regard.
Again, thanks in advance

Reply to this Message

macropod - 28 Mar 2007 11:20 GMT

Hi Jay,

Assuming you're using dropdown formfields:
. set each formfield's properties to 'calculate on exit'
. for each dropdown formfield create a set of nested fields coded as:
{SET Result1 {IF{REF Dropdown1}= "Excellent" 4 {IF{REF Dropdown1}= "Very Good" 3 {IF{REF Dropdown1}= "Good" 2 {IF{REF Dropdown1}=
"Poor" 1 0}}}}}
where Dropdown1 is the formfield's bookmark name, and incrementing Result1 for each formfield reference
. add the following field where you want the result to appear:
{=(Result1+Result2+Result3+Result4)/16*100 \# 0.00%}
this will give you percentages to 2 decimal places - drop the '.00' if you don't want decimals.
. protect your document for forms.

Note: This is a field code solution, rather than vba, and the field braces shown above (ie '{ }') are created in pairs via Ctrl-F9.

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

>I I am not much familiar with the programming aspect (I am in quality
> engineering field), so, I have come to this place for help. Please help me!
[quoted text clipped - 8 lines]
> We would very much appreciate your help in this regard.
> Again, thanks in advance

Reply to this Message

Jay - 01 Apr 2007 04:14 GMT

Hello macropod, I really appreciate your help. It worked at my home computer.
But I have an another kind of problem now. I emailed the protected form to my
office email address (outlook). But when I tried to use the form at office,
and hit the score text box, message comes that reference is missing. I do not
know what to do. The reason is that I will email the form to my customer for
filling up the survey. Please help me, macropod or any of you in the forum,
so that I can make the form workable.

Thanks in advance.

> Hi Jay,
>
[quoted text clipped - 24 lines]
> > We would very much appreciate your help in this regard.
> > Again, thanks in advance

Reply to this Message

Doug Robbins - Word MVP - 01 Apr 2007 07:00 GMT

Look at the Properties dialog for each of the formfields in your form and
make sure that the Bookmark name has not got deleted somehow. That is the
Dropdown1, Dropdown2 etc. It will make what you are doing more
understandable if you assign your own bookmarks names that bear some
relationship to the purpose of the dropdown formfield.

Signature

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

> Hello macropod, I really appreciate your help. It worked at my home
> computer.
[quoted text clipped - 51 lines]
>> > We would very much appreciate your help in this regard.
>> > Again, thanks in advance

Reply to this Message

Jay - 01 Apr 2007 16:14 GMT

Doug Good Morning and thanks. There may be other problem. I copied the
Satisfaction Survey form at home (xp professional) on a memory stick and
tried to open it from office computer (2000 professional), error message
comes that reference is missing. As per the dialog box, I opened the object
browser and saw the macro, "calculate" under members of Global classes. I
would appreciate any of your help to resolve this issue.
Jay

> Look at the Properties dialog for each of the formfields in your form and
> make sure that the Bookmark name has not got deleted somehow. That is the
[quoted text clipped - 57 lines]
> >> > We would very much appreciate your help in this regard.
> >> > Again, thanks in advance

Reply to this Message

Doug Robbins - Word MVP - 01 Apr 2007 17:18 GMT

There must be some problem unassociated with the method that Macropod has
given you because it does not rely on the use of Visual Basic.

What dialog box are you referring to? Sounds like you might have some
completely different mis-performing add-in on the machine with Office 2000.

Signature

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

> Doug Good Morning and thanks. There may be other problem. I copied the
> Satisfaction Survey form at home (xp professional) on a memory stick and
[quoted text clipped - 78 lines]
>> >> > We would very much appreciate your help in this regard.
>> >> > Again, thanks in advance

Reply to this Message

Jay - 03 Apr 2007 02:30 GMT

Hello Doug, thanks for your help. I am getting diaglog box message stating
"Compile Error: Can't find project or library". When I click, OK, then it
leads me to the macro, Calculate, highlighted with Yellow on VB editor.
But same thing works very fine on my home computer
Jit

> There must be some problem unassociated with the method that Macropod has
> given you because it does not rely on the use of Visual Basic.
[quoted text clipped - 84 lines]
> >> >> > We would very much appreciate your help in this regard.
> >> >> > Again, thanks in advance

Reply to this Message

Doug Robbins - Word MVP - 03 Apr 2007 07:43 GMT

Sorry, see my previous message. VB is not involved with the method that
Macropod has provided. Maybe you have some other code in the document.

Signature

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

> Hello Doug, thanks for your help. I am getting diaglog box message stating
> "Compile Error: Can't find project or library". When I click, OK, then it
[quoted text clipped - 104 lines]
>> >> >> > We would very much appreciate your help in this regard.
>> >> >> > Again, thanks in advance

Reply to this Message

Jay - 04 Apr 2007 02:52 GMT

Doug, I have used the following VB codes:
Sub Calculate()
Dim iTotal As Integer

If ActiveDocument.FormFields("Total").Result = "" Then
iTotal = iTotal +
ActiveDocument.FormFields("Dropdown1").DropDown.Value
iTotal = iTotal +
ActiveDocument.FormFields("Dropdown2").DropDown.Value
iTotal = iTotal +
ActiveDocument.FormFields("Dropdown3").DropDown.Value
iTotal = iTotal +
ActiveDocument.FormFields("Dropdown4").DropDown.Value
iTotal = iTotal +
ActiveDocument.FormFields("Dropdown5").DropDown.Value
iTotal = iTotal +
ActiveDocument.FormFields("Dropdown6").DropDown.Value
iTotal = iTotal +
ActiveDocument.FormFields("Dropdown7").DropDown.Value
iTotal = (iTotal / 28) * 100
ActiveDocument.FormFields("Total").Result = Str(iTotal)
End If

End Sub
This code works fine from my home computer, but not from my office.
Jay

> Sorry, see my previous message. VB is not involved with the method that
> Macropod has provided. Maybe you have some other code in the document.
[quoted text clipped - 107 lines]
> >> >> >> > We would very much appreciate your help in this regard.
> >> >> >> > Again, thanks in advance

Reply to this Message

Doug Robbins - Word MVP - 04 Apr 2007 08:23 GMT

That is not the way in which Macropod suggested that you do it.

Signature

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

> Doug, I have used the following VB codes:
> Sub Calculate()
[quoted text clipped - 153 lines]
>> >> >> >> > We would very much appreciate your help in this regard.
>> >> >> >> > Again, thanks in advance

Reply to this Message

macropod - 04 Apr 2007 08:44 GMT

Hi Jay,

This is the first time you've actually said you've used something *other than the solution I posted* - yet replying to my post,
which used field codes instead of vba, you said 'It worked at my home computer'. It would have helped if you'd said you were using
vba right at the start, instead of replying to a field-code solution saying you were getting macro-dependent errors.

Having said that, I'd be surprised if the code you've posted would work on either PC - you've got multiple continuing lines with no
line continuation code. All those:
iTotal = iTotal +
ActiveDocument.FormFields("Dropdown1").DropDown.Value
expressions should be coded as:
iTotal = iTotal + _
ActiveDocument.FormFields("Dropdown1").DropDown.Value
Note the ' _' after the '+' signs.

Also, because of the line:
If ActiveDocument.FormFields("Total").Result = "" Then
your code would also only work automatically in response to updates to the dropdown values until the first Dropdown value is
changed. Even running it manually would cause it not to update after being set the first time.

In any event, the code can be simplified quite a bit:
Sub Calculate()
With ActiveDocument
.FormFields("Total").Result = (.FormFields("Dropdown1").DropDown.Value + _
.FormFields("Dropdown2").DropDown.Value + .FormFields("Dropdown3").DropDown.Value + _
.FormFields("Dropdown4").DropDown.Value + .FormFields("Dropdown5").DropDown.Value + _
.FormFields("Dropdown6").DropDown.Value+ .FormFields("Dropdown7").DropDown.Value / 28) * 100
End Sub

BTW, you don't need a formfield to hold the results of your calculation - a 'Total' bookmark in the document would do.

Cheers

Signature

macropod
[MVP - Microsoft Word]
-------------------------

> Doug, I have used the following VB codes:
> Sub Calculate()
[quoted text clipped - 134 lines]
>> >> >> >> > We would very much appreciate your help in this regard.
>> >> >> >> > Again, thanks in advance

Reply to this Message

Jay - 07 Apr 2007 03:36 GMT

Hello macropod and Doug, I apologize for my miscommunication. As I am not
much familiar with the programming aspect in Words, I was confused. Any way,
other day, I talked to one of our office IT guy about this problem, and he
suggested following changes:
1.The macro will only run once, this is because you required the field to =
"". So if they changed an answer, the score would never change.
2)The reason you got a compile error was because of the references word was
making to other applications. This is a setting you can change on every
computer with word, but most likely all customers won't have the setting as
you do at home. The solution to this was easy. Just change the field from
text to a number. Then change the code to not use the Str() function.
3)To fix the problem described in number 1. Please edit each text field so
that it will run the Calculate routine every time it is entered or exited.
4)Adding the auto-calculate causes one minor problem. The problem is that
the scores all default to poor when the document is opened, and therefore
making a negative first impression on the person being surveyed. To prevent
that add a routine to the document that will run every time the document is
opened that will set all fields to Excellent.

He made some changes in the code, and now the Survey form is working very
fine. In fact I sent it to a few customers and got reply too.
Happy Easter to all of you!
Jay

> Hi Jay,
>
[quoted text clipped - 166 lines]
> >> >> >> >> > We would very much appreciate your help in this regard.
> >> >> >> >> > Again, thanks in advance

Reply to this Message