Using Office and Windows XP, SP3

[Jean Pereira]

I have created an expenses form using form fields which users can input up to
10 separate expenses complete with supporting info (1 expense per row) which
is manually typed in.

I now have to give users options to select on the form fields (of which
there are more than 24 in one field so can't use a normal combo box).

Created a form in VBA with a macro which popped up a box on entry to the
cell for users to select a team and expense codes, and coded it to put the
info into the relevant cell in a table. It works fine for the first row of
information but as soon as I enter a second row and choose different options,
it overwrites the info selected on row 1 (and I can see why it is doing that.

The code I have is as follows:

If OptBR.Value = True Then
ActiveDocument.FormFields("Team1").Result = "Business Recoveries"
ActiveDocument.FormFields("NomCodes1").Result = cboBRNomCodes
End If

How do I get it to work for rows 2-10?

I don't know how to program the info to go to the next row down or maybe at
the cursor location.

Thanks for any help or suggestions

Jean

 

[Jay Freedman]

Look at the "Bookmark" box in the Properties dialog for each form field in
the document. I think you'll find that the fields in the first row have
bookmark names of "Team1" and "NomCodes1", while those in the other rows
have other names (maybe just replacing the "1" with other row numbers?). The
bookmark name serves as the form field's name.

Your code needs to use the correct field name each time. It has to
"understand" the form field naming scheme in some way. If you post back and
explain the scheme, we can help with the code.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so
all may benefit.

 

[Jean Pereira]

Jay, yes the bookmark names for each row are different ie Team2, Team3 and
NomCodes2 etc. Because my code is telling it to go to Team1, that's why its
overwriting.

I thought maybe adding another sub called SecondRow and changing the code as
follows

If OptBR.Value = True And Team1 = "" Then

Else
SecondRow

and in the SecondRow sub change the FormField location to Team2 and NomCodes2

If I can get it to work for the second row then I can copy it for the
remaining rows

 

[Jay Freedman]

Hi Jean,

Don't think of proliferating more subroutines. It's very inefficient and
unnecessary.

Your macro can use code like this to determine which row contains the form field
that the user entered, and use that to construct the correct names for the
fields to fill in:

Dim currRow As String
currRow = CStr(Selection.Rows(1).Index)

If OptBR.Value = True Then
ActiveDocument.FormFields("Team" & currRow).Result = _
"Business Recoveries"
ActiveDocument.FormFields("NomCodes" & currRow).Result = _
cboBRNomCodes
End If

 

[Jean Pereira]

Thanks a million - I have to have this ready by Monday morning and I now
actually think it will be.

Jean

 

[Jean Pereira]

Jay, one last question. Using your code, with the cursor on row 2 (as the
first row contains column headings), the variable info is put on row 3 and if
the cursor is on row 3, the info is put on row 4 so users end up with a blank
row in between each row with info. When I stepped through it, the current
row is shown as 2.

 

[Jay Freedman]

The code is written to assume that the form field named "Team1" is in row 1,
"Team2" is in row 2, and so on. It sounds like your table has a heading row in
row 1, with "Team1" in row 2 and so on.

The simplest fix is to change this line

currRow = CStr(Selection.Rows(1).Index)

to this:

currRow = CStr(Selection.Rows(1).Index - 1)

That tells the macro "if the cursor is in row 2, use field names ending in 1"
and similar for the other rows.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.

 

[Jean Pereira]

Thanks again Jay, that fixed it.

Jean

The code is written to assume that the form field named "Team1" is in row 1,
"Team2" is in row 2, and so on. It sounds like your table has a heading row in
row 1, with "Team1" in row 2 and so on.

The simplest fix is to change this line

currRow = CStr(Selection.Rows(1).Index)

to this:

currRow = CStr(Selection.Rows(1).Index - 1)

That tells the macro "if the cursor is in row 2, use field names ending in 1"
and similar for the other rows.

--
Regards,
Jay Freedman
Microsoft Word MVP
Email cannot be acknowledged; please post all follow-ups to the newsgroup so all
may benefit.