asked on

Totaling a table based on check boxes

Hi everyone,

I have two tables in a Microsoft Word file. I've also inserted some check box form fields at the start of each row. The attached screenshot shows what it looks like.

Is it possible to somehow have the total amount inserted into the cell opposite the Total label? So if someone ticks a $50 row in the first table and say a $30 and $40 row in the second table, then the total of $120 would automatically appear in the cell next to Total at the end of the second table?

I'm not sure if there's some sort of totalling function within Word or whether VBA would be needed.

Appreciate any advice.
Picture-16.png

GrahamSkan

It would be possible to use fields calculation, but it would be very cumbersome.

Use some code like this. Make it the Exit macro for each of the checkboxes

Sub ExitMacro()
    Dim tbl As Table
    Dim r As Integer
    Dim total As Single
    
    Set tbl = ActiveDocument.Tables(1)
    For r = 2 To tbl.Rows.Count - 1
        If tbl.Cell(r, 1).Range.FormFields(1).CheckBox.Value Then
           total = total + GetValue(tbl.Cell(r, 4).Range)
        End If
    Next r
    ActiveDocument.Unprotect 'password
    tbl.Cell(r, 4).Range.Text = Format(total, "Currency")
    ActiveDocument.Protect wdAllowOnlyFormFields, True ',password
End Sub

Function GetValue(strText As String) As Single
    Dim p As Integer
    
    p = 1
    Do Until IsNumeric(Mid$(strText, p, 1))
        p = p + 1
    Loop
    GetValue = Val(Mid$(strText, p))
End Function

Open in new window

CAE5942

ASKER

Thanks for the reply,

When I double click on a check box to go into the options, I click on the drop down menu for 'run macro on exit'. There's only one option in there that says: none, and I can't even select that one in order to create a macro.

I'm not sure what I'm doing wrong. Do you have any suggestions?

GrahamSkan

Open the VBA editor (Alt+F11). Copy and paste the code into a module.

Remove the document protection. For each checkbox control, right click, select properties, and then find the macro called ExitMacro in the Exit dropdown. Select it and click OK.

Reprotect the document after they are all done.

CAE5942

ASKER

Thanks - I've managed to paste the code in and apply the macro to each check box.

I'm not getting any addition happening though. The attached screenshot shows what it looks like. You'll notice that in the second row of the first table, a zero dollar amount appears, ie. $0.00. Not sure why this is happening when the total for both tables needs to be inserted into the last row of the second table. I'm also getting the following error:

Run-time error '4605': This method or property is not available because the object referes to a protected area of the document.

Any ideas?
Picture-19.png

GrahamSkan

I did test the code and it worked in the test. It was designed to show the method, so only works on the first table.

However, I don't know why it isn't working. The error message that you get is very surprising, since the only line (13 in the snippet box above) where that is likely to happen is immediately preceded buy one that removes the protection.

Can I suggest that you post the (bits of) the document that you are working on? It tells us much more than a picture.

CAE5942

ASKER

Ok I've attached a part of the document. Would be grateful if you could take a look.
test.doc

ASKER CERTIFIED SOLUTION

GrahamSkan

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

CAE5942

ASKER

Thanks very much,

I think the reason it didn't work on my side is that I was using Word for mac 2004 to test. I'll check out the new code in Windows 2007 version.

If I wanted to add more tables to the mix, would I just have to alter the number in the following code snippet (from line 16):

ActiveDocument.Tables(2)

By changing 2 to 3 etc.?

GrahamSkan

That would be the simplest. However, if the document gets really complicated wrt tables, it might be worth while bookmarking them.

CAE5942

ASKER

Ok great - thanks again for the help.