Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.word.tables
|
|||
|
|||
Word Table Calculation VBA
Okay, I think I'm getting there; thanks for all your help out there...
So here's where I stand: I have my rows of check boxes in a table; I need the user to be able to select only one choice [ed.: can't use buttons in a userform...don't ask, but it was forbidden) per row, which will be totalled (then multiplied by 60%) later. The check box groups successfully limit the user to one per row; and the total calculates for the first row. BUT, that's where I hit the wall: When I check a box in the second row, it changes the total in the first row. GRR! I know it must have to do with them being in groups [as required by the first bit of code, but likely interfering with the second], and I'm not sure how to track down where the flaw is. Here's what I've got so far; the top half I got from you folks, the rest I found as noted: Sub SumTableRowCheckBoxes() ' CheckExclusive Macro Dim oField As FormField For Each oField In Selection.Frames(1).Range.FormFields oField.CheckBox.Value = False Next oField ' FormField Value Macro ' from http://homepage.swissonline.ch/cindymeister/formfaq.htm Selection.FormFields(1).CheckBox.Value = True Dim ffldsChkBoxes As Word.FormFields Dim frmField As Word.FormField Dim CheckBoxValue As Integer Dim RunningCheckBoxSum As Integer Dim currRow As String Dim frmTotalName As Variant 'Variant because string not accepted ScreenUpdating = False If Selection.Information(wdWithInTable) = True Then 'MsgBox Selection.FormFields(1).Name & vbCr & Selection.FormFields(1).CheckBox.Value Set ffldsChkBoxes = Selection.Rows(1).Range.FormFields RunningCheckBoxSum = 0 For Each frmField In ffldsChkBoxes If frmField.Type = wdFieldFormCheckBox Then If frmField.CheckBox.Value = True Then CheckBoxValue = 0 CheckBoxValue = GetValueFromName(frmField.Name) RunningCheckBoxSum = RunningCheckBoxSum + CheckBoxValue End If End If Next currRow = Trim(Str(Selection.Cells(1).RowIndex)) frmTotalName = "Row" & currRow & "Total" ActiveDocument.FormFields(frmTotalName).Result = RunningCheckBoxSum Else MsgBox "Any checkbox or formfield running this macro must be in a table." End If End Sub Function GetValueFromName(FormFieldName As Variant) Dim ValueStartPos As Integer Dim FormFieldValue As Integer If InStr(FormFieldName, "Val") Then ValueStartPos = InStr(FormFieldName, "Val") + 3 FormFieldValue = Mid(FormFieldName, ValueStartPos) End If GetValueFromName = FormFieldValue End Function I'm so close to getting this thing wrapped up, which will make me very happy because I can then go back to my much easier Excel and Acrobat files...but they insist this one remain in Word. Cheers, TMan |
#2
Posted to microsoft.public.word.tables
|
|||
|
|||
Word Table Calculation VBA
I can't tell exactly what you have, but if you have a table with check boxes
in all columns except the last column and in the last column you have a textbox formfield (assumed to be given the bookmark names row1, row2, row 3, etc.) with data entry to those formfields not being enabled, and then you have a final row in the table in which you have a calculation type formfield that calculates the sum of the values of the other textbox formfields (=row1+row2+row3, etc) and you have the following macro run on exit from each of the checkbox formfields and you have the calculate on exit property for those formfields set, the textbox formfield at the end of each row will be updated with the column number of the checked box and the textbox formfield in the final row will be updated with the sum of the column numbers: Dim i As Long, j As Long, k As Long i = Selection.Information(wdEndOfRangeRowNumber) j = Selection.Information(wdEndOfRangeColumnNumber) With Selection.Tables(1).Rows(i) If .Cells(j).Range.FormFields(1).CheckBox.Value = True Then For k = 1 To .Cells.Count - 1 If k j Then .Cells(k).Range.FormFields(1).CheckBox.Value = False End If Next .Cells(.Cells.Count).Range.FormFields(1).result = j End If End With The user must tab out of the cells for this to work. -- 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 "TomorrowsMan" wrote in message oups.com... Okay, I think I'm getting there; thanks for all your help out there... So here's where I stand: I have my rows of check boxes in a table; I need the user to be able to select only one choice [ed.: can't use buttons in a userform...don't ask, but it was forbidden) per row, which will be totalled (then multiplied by 60%) later. The check box groups successfully limit the user to one per row; and the total calculates for the first row. BUT, that's where I hit the wall: When I check a box in the second row, it changes the total in the first row. GRR! I know it must have to do with them being in groups [as required by the first bit of code, but likely interfering with the second], and I'm not sure how to track down where the flaw is. Here's what I've got so far; the top half I got from you folks, the rest I found as noted: Sub SumTableRowCheckBoxes() ' CheckExclusive Macro Dim oField As FormField For Each oField In Selection.Frames(1).Range.FormFields oField.CheckBox.Value = False Next oField ' FormField Value Macro ' from http://homepage.swissonline.ch/cindymeister/formfaq.htm Selection.FormFields(1).CheckBox.Value = True Dim ffldsChkBoxes As Word.FormFields Dim frmField As Word.FormField Dim CheckBoxValue As Integer Dim RunningCheckBoxSum As Integer Dim currRow As String Dim frmTotalName As Variant 'Variant because string not accepted ScreenUpdating = False If Selection.Information(wdWithInTable) = True Then 'MsgBox Selection.FormFields(1).Name & vbCr & Selection.FormFields(1).CheckBox.Value Set ffldsChkBoxes = Selection.Rows(1).Range.FormFields RunningCheckBoxSum = 0 For Each frmField In ffldsChkBoxes If frmField.Type = wdFieldFormCheckBox Then If frmField.CheckBox.Value = True Then CheckBoxValue = 0 CheckBoxValue = GetValueFromName(frmField.Name) RunningCheckBoxSum = RunningCheckBoxSum + CheckBoxValue End If End If Next currRow = Trim(Str(Selection.Cells(1).RowIndex)) frmTotalName = "Row" & currRow & "Total" ActiveDocument.FormFields(frmTotalName).Result = RunningCheckBoxSum Else MsgBox "Any checkbox or formfield running this macro must be in a table." End If End Sub Function GetValueFromName(FormFieldName As Variant) Dim ValueStartPos As Integer Dim FormFieldValue As Integer If InStr(FormFieldName, "Val") Then ValueStartPos = InStr(FormFieldName, "Val") + 3 FormFieldValue = Mid(FormFieldName, ValueStartPos) End If GetValueFromName = FormFieldValue End Function I'm so close to getting this thing wrapped up, which will make me very happy because I can then go back to my much easier Excel and Acrobat files...but they insist this one remain in Word. Cheers, TMan |
Reply |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Word 97 in Windows XP to maintain formatting | Microsoft Word Help | |||
Word should allow to 'divide' page by 3 or 4, not just 2 | Page Layout | |||
Does Word have a QuickCorrect/Quick Word option like WordPerfect? | New Users | |||
In Word, how do I surpress headers and footers on page 2 | Microsoft Word Help | |||
Envelope Address | New Users |