Re: Use of Macro/VBA

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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
--
macropod
[MVP - Microsoft Word]
-------------------------

"Jay" <Jay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5F601185-7A95-4E70-B0ED-9659CF091E34@xxxxxxxxxxxxxxxx
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

"Doug Robbins - Word MVP" wrote:

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.

--
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

"Jay" <Jay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:E327F109-526B-4BAC-9303-A20F184A316E@xxxxxxxxxxxxxxxx
> 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
>
> "Doug Robbins - Word MVP" wrote:
>
>> 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.
>>
>> -- >> 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
>>
>> "Jay" <Jay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:935338A3-1DC1-42BA-BE78-45C3E1853269@xxxxxxxxxxxxxxxx
>> > 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
>> >
>> > "Doug Robbins - Word MVP" wrote:
>> >
>> >> 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.
>> >>
>> >> -- >> >> 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
>> >>
>> >> "Jay" <Jay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:39AEA7D4-5931-44AF-B9C8-0C881F51E3A9@xxxxxxxxxxxxxxxx
>> >> > 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.
>> >> > "macropod" wrote:
>> >> >
>> >> >> 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
>> >> >> -- >> >> >> macropod
>> >> >> [MVP - Microsoft Word]
>> >> >> -------------------------
>> >> >>
>> >> >> "Jay" <Jay@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> news:4ABEA505-07A9-40A8-9EE8-98AB663F6EEF@xxxxxxxxxxxxxxxx
>> >> >> >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
>> >> >> >
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>




.

Relevant Pages

  • RE:reasons as requested
    ... Sub TestThis() ... > im sure i can get vba to do what i want with this function but at the moment ... as a pivot table for this instance would not ... >> first time it is used from the top. ...
    (microsoft.public.excel.programming)
  • Re: Match User Name and Password from Access table
    ... This is first time for me, ... > Doug Steele, Microsoft Access MVP ... >> Dim strUser as string ... >> Exit Sub ...
    (microsoft.public.access.security)
  • Re: Click Button added via VBA
    ... First time I tried it, ... soon as the CommandButton is loaded with the code...Word Crashes, ... Private Sub CommandButton1_Click ... Dim shp As Word.InlineShape ...
    (microsoft.public.office.developer.vba)
  • Re: Click Button added via VBA
    ... First time I tried it, ... soon as the CommandButton is loaded with the code...Word Crashes, ... Private Sub CommandButton1_Click ... Dim shp As Word.InlineShape ...
    (microsoft.public.office.developer.vba)
  • Re: Front End not Updating
    ... the first time, and this code step hides that error. ... >> I think all you need is one more step just before the End Sub: ... >> <MS ACCESS MVP> ...
    (microsoft.public.access.externaldata)