Pizza Tips

StephenRay · #1 09-04-2017, 11:05 AM

Friends, my son is delivering Pizza. He can make $80 some evenings.
We want to keep track of money he brings in each night by typing in two or three numerals amounting to his newTips. like 50 or 80 or 100

We would open the old document & type the 50 or 80 or 100 at the top-left in the document. - Then run the Macro.
NewTips will be the first word in the document.
Then we want to add this to the oldTotal, the second word in the document.
After the macro runs, we want the new total to replace the old total.

Before we run the Macro, After we type in the newTips of $50 this is what the document will look like:
50
30 Total
10 Monday, September 4, 2017
10 Saturday, September 2, 2017
10 Friday, September 1, 2017

Here's my attempt to use Range in a Macro,
Sub Macro11()
'
' Macro11 Macro
'
'
Dim newTips As Range

Set newTips = ActiveDocument.Range(Start:=ActiveDocument.Words(1 ).Start, End:=ActiveDocument.Words(1).End)
Dim oldTotal As Range
Set oldTotal = ActiveDocument.Range(Start:=ActiveDocument.Words(2 ).Start, End:=ActiveDocument.Words(2).End)

Dim newTotal As DataObject
Dim strClip As String

Set newTotal = New DataObject
newTotal = newTips + oldTotal

newTotal.SetText strClip

newTotal.PutInClipboard

Selection.HomeKey Unit:=wdStory
Selection.MoveRight Unit:=wdWord, Count:=1, Extend:=wdExtend
Selection.Delete Unit:=wdCharacter, Count:=1
Selection.PasteAndFormat (wdFormatOriginalFormatting)

End Sub

slaycock · #2 09-04-2017, 01:27 PM

Try the following

Code:

Sub UpdateMyTips()

Dim myNewTip As Long
Dim myOldTotal As Long
    
    myNewTip = CLng(ActiveDocument.Paragraphs(1).Range.Words(1).text)
    
    myOldTotal = CLng(ActiveDocument.Paragraphs(2).Range.Words(1).text)
    
   With ActiveDocument.Paragraphs(2).Range
   
        .Words(1).text = CStr(myNewTip + myOldTotal) & " "
        .InsertAfter text:=myNewTip & " " & Format(Now(), "DDDD, MMMM d, yyyy") & vbCrLf
    End With
    ActiveDocument.Paragraphs(1).Range.text = " " & vbCrLf
End Sub

slaycock · #3 09-05-2017, 02:11 AM

Or if you really wanted to play around with ranges, but much less readable/understandable than above

Code:

Sub UpdateMyTips()

Dim myNewTip As Long
Dim myOldTotal As Long
    
   With ActiveDocument.Paragraphs(2).Range.Words(1)
        With .Previous(unit:=wdParagraph).Words(1)
            myNewTip = CLng(.Text)
            .Text = " " & vbCrLf
        End With
        myOldTotal = CLng(.Words(1).Text)
        .Words(1).Text = CStr(myNewTip + myOldTotal) & " "
        .Paragraphs(1).Range.InsertAfter Text:=myNewTip & " " & Format(Now(), "DDDD, MMMM d, yyyy") & vbCrLf
    End With
End Sub

StephenRay · #4 09-05-2017, 07:16 AM

slaycock, Thank you for your reply. I am at work now, here in Kansas. I will study and digest your solution. Your solution looks like you know a lot. I tried to do the same thing in Excel but it never did work either. I will try your solution soon, but I am working a lot of hours these days. I got a good VBA Book, but I need to see lots of examples to go along with the theory. Thanks again!

slaycock · #5 09-05-2017, 07:33 AM

I wouldn't be at the level I am without the feedback and examples from this forum.

The difficult bit is breaking the dependency on macro recording and getting to the level of understanding the object model.

A good tip is the use of F1 when in the VBA ide as it brings up help pages on the keywords highlighted by the cursor. If you pay very close attention to the return values of methods and properties things soon start to become a little clearer.

Good luck on your adventures.

StephenRay · #6 09-05-2017, 12:58 PM

Slaycock, Omigosh! The first one works! I finally needed a break and decided to play for a while. It is simple, easy, functional, nothing fancy, just what we need to keep track of things. And I didn't realize all that functionality is possible, like insertafter. I have a lot of studying to do. Beautiful! Thanks!

StephenRay · #7 09-06-2017, 12:40 PM

Slaycock, For withdrawals, that is to subtract $$ from the total I made another macro to be executed with another HotKey. It subtracts just fine!

Now I am trying to figure out how to make the withdrawal amount a red color & Bold. I tried placing some code at the end of the code by selecting (mynewtip would be the withdrawal), the first word of the third line. But sometimes it will change the wrong part of the line to Bold! And I can't find an example of how to make it red or do this using Range.

Thanks in advance for your suggestions.

slaycock · #8 09-11-2017, 12:26 PM

You need to get to grips with using intellisense to review your options and F1 to bring up the help page for a keyword.

When you type in a legal keyword and follow it with a '.' then the VBA IDE will pop up a list of properties and methods that you can use next. If you don't see this then you need to go to

Tools->Options which will bring up a dialog box.

Select the editor tab

Tick all the boxes in the Code Settings frame.

To get you on your way more quickly consider the line

.Words(1).Text = CStr(myNewTip + myOldTotal) & " "

You can then progress using

Code:

        
        .Words(1).Text = CStr(myNewTip + myOldTotal) & " "
        .Words(1).Font.Color = wdColorRed
        .Words(1).Font.Bold = True

or

Code:

With .Words(1)
            .Text = CStr(myNewTip + myOldTotal) & " "
            .Font.Color = wdColorRed
            .Font.Bold = True

        End With

Good luck playing with intellisense

PS Intellisense won't pull up properties and methods that are deprecated but which still work. Color is one such property. It has been replaced by ColorIndex which limits the color selection to the narrower range offered by the theme colors.

gmaxey · #9 09-12-2017, 07:13 AM

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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Addition	James__S	Word	1	08-18-2016 06:20 PM
Addition of new colors	marif300	Project	3	05-20-2016 07:32 AM
Addition Formula is Giving a Date!!!	MikeM3	Excel	9	01-15-2016 02:41 AM
Need tips for creating a specific word format	DomDom	Word	5	09-24-2014 01:24 AM
Putting a horizontal line in a Word Document -- should be simple, right?	biotechguy	Word	4	05-10-2010 11:58 AM