If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Word should catalog misspelled words to study. | rndthought | General Discussion | 38 | January 23rd, 2008 11:53 PM |
How to put graphics on envelopes? | Steve Koenig | General Discussion | 20 | September 14th, 2007 04:40 PM |
Word should allow to 'divide' page by 3 or 4, not just 2 | CandasK | Page Layout | 2 | February 28th, 2006 10:16 PM |
WP merge file to Word | sstires | Tables | 4 | February 14th, 2006 06:26 PM |
Table Design | A. Williams | Database Design | 3 | April 29th, 2005 07:02 PM |