View Single Post
 
Old 09-12-2017, 07:13 AM
gmaxey gmaxey is offline Windows 7 32bit Office 2016
Expert
 
Join Date: May 2010
Location: Brasstown, NC
Posts: 1,635
gmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nicegmaxey is just really nice
Default

SR/SC

Advancing your solutions a bit, you could use content controls to input the daily addition or deduction and then leverage their exit events to calculate. I've attached a file to go along with this code. Note - The code goes in the ThisDocument module of the project.

Code:
Option Explicit
Dim oTblInput As Table, oTblRecord As Table
Private Sub Document_ContentControlOnExit(ByVal oCC As ContentControl, Cancel As Boolean)
  Select Case oCC.Title
    Case "Add"
      If IsNumeric(oCC.Range.Text) Then
        oCC.Range.Text = FormatCurrency(oCC.Range.Text, 2, True)
        oTblInput.Cell(1, 4).Range.Text = FormatCurrency(CDbl(oCC.Range.Text) + CDbl(fcnCellText(oTblInput.Cell(1, 4))), 2, True)
        With oTblRecord
          .Rows.Add oTblRecord.Rows(3)
          .Cell(3, 1).Range.Text = Format(Date, "MMMM dd, yyyy")
          .Cell(3, 2).Range.Text = oCC.Range.Text
          'After first run you can delete the blank row at the bottom of the record table.
        End With
      End If
    Case "Subtract"
      If IsNumeric(oCC.Range.Text) Then
        oCC.Range.Text = FormatCurrency(oCC.Range.Text, 2, True)
        oTblInput.Cell(2, 4).Range.Text = FormatCurrency(CDbl(fcnCellText(oTblInput.Cell(2, 4)) - CDbl(oCC.Range.Text)), 2, True)
      End If
    Case Else
 
  End Select
  ActiveDocument.Fields.Update
lbl_Exit:
  Exit Sub
End Sub
Private Sub Document_Open()
  Set oTblInput = ActiveDocument.Tables(1)
  Set oTblRecord = ActiveDocument.Tables(2)
  ActiveDocument.SelectContentControlsByTitle("Add").Item(1).Range.Text = vbNullString
  ActiveDocument.SelectContentControlsByTitle("Subtract").Item(1).Range.Text = vbNullString
lbl_Exit:
  Exit Sub
End Sub
Function fcnCellText(oCell As Cell) As String
  fcnCellText = Left(oCell.Range.Text, Len(oCell.Range.Text) - 2)
  If fcnCellText = vbNullString Then fcnCellText = "0"
lbl_Exit:
  Exit Function
End Function
Attached Files
File Type: docm Add.docm (37.1 KB, 12 views)
__________________
Greg Maxey
Please visit my web site at http://www.gregmaxey.com/
Reply With Quote