1

I wrote code that calculates if-then conditional values and then appears in a message box. How do I export the the message box to MS Word? Is there a function that instead of Msgbox, I could export to MS Word? Could I convert it to word from Debug.print?

Sub Way_EatFresh()

Dim ws As Worksheet, wsB As Worksheet
Set ws = Worksheets("Overview")
Set wsB = Worksheets("Billing Rates")

Dim trueCount As Integer
Dim i As Integer
Dim Cst, Hrs


For i = 1 To 11

If ws.Range(Chr(65 + i) & "36").Value = "True" Then
    trueCount = trueCount + 1
    Cst = Cst + wsB.Range(Chr(66 + i) & "33").Value
    Hrs = Hrs + wsB.Range(Chr(66 + i) & "25").Value
    Scope = Scope + wsB.Range(Chr(66 + i) & "150").Value
End If

If ws.Range(Chr(65 + i) & "37").Value = "True" Then
    trueCount = trueCount + 1
    Cst = Cst + wsB.Range(Chr(66 + i) & "34").Value
    Hrs = Hrs + wsB.Range(Chr(66 + i) & "26").Value
    Scope = Scope + wsB.Range(Chr(66 + i) & "150").Value
End If

If ws.Range(Chr(65 + i) & "38").Value = "True" Then
    trueCount = trueCount + 1
    Cst = Cst + wsB.Range(Chr(66 + i) & "35").Value
    Hrs = Hrs + wsB.Range(Chr(66 + i) & "27").Value
    Scope = Scope + wsB.Range(Chr(66 + i) & "150").Value
End If

If ws.Range(Chr(65 + i) & "40").Value = "True" Then
    trueCount = trueCount + 1
    Cst = Cst + wsB.Range(Chr(66 + i) & "49").Value
    Hrs = Hrs + wsB.Range(Chr(66 + i) & "41").Value
    Scope = Scope + wsB.Range(Chr(66 + i) & "150").Value
End If

 If ws.Range(Chr(65 + i) & "41").Value = "True" Then
    trueCount = trueCount + 1
    Cst = Cst + wsB.Range(Chr(66 + i) & "50").Value
    Hrs = Hrs + wsB.Range(Chr(66 + i) & "42").Value
    Scope = Scope + wsB.Range(Chr(66 + i) & "150").Value
End If

 If ws.Range(Chr(65 + i) & "42").Value = "True" Then
    trueCount = trueCount + 1
    Cst = Cst + wsB.Range(Chr(66 + i) & "51").Value
    Hrs = Hrs + wsB.Range(Chr(66 + i) & "43").Value
    Scope = Scope + wsB.Range(Chr(66 + i) & "150").Value
End If

Next i

If trueCount > 0 Then
   MsgBox "Cost: " & Cst _
    & vbNewLine & "Hours: " & Hrs _
    & vbNewLine & "Scope: " & Scope _

End If

If trueCount = 0 Then
     MsgBox "Please select engagement components."
End If
End Sub
CodingNewb
  • 41
  • 4
  • To where in Word? A new document? An existing document? – Tim Williams Mar 16 '16 at 17:23
  • To a new word document – CodingNewb Mar 16 '16 at 17:24
  • Although I'd definitely settle for an existing document – CodingNewb Mar 16 '16 at 17:36
  • While there is no way to 'convert' your message box. You can use the variables you already have (Cst, Hrs, Scope) to write to a new microsoft word document. Just write to the 'WordDoc' variable as seen in this link: http://stackoverflow.com/questions/16418292/vba-open-word-from-excel – Kris B Mar 16 '16 at 18:23
  • Would this export the information generated by the excel macro to word? – CodingNewb Mar 16 '16 at 18:33
  • No, there's no function in Excel that will export to Word instead of displaying text in a MsgBox. You have to write that code yourself. There are *many* examples on StackOverflow and on the Internet, in general, that show how to do that. – Cindy Meister Mar 16 '16 at 19:44
  • Okay :( http://memesvault.com/wp-content/uploads/Okay-Guy-Meme-04.jpg – CodingNewb Mar 16 '16 at 19:54

1 Answers1

2

I use this type of code for my Job

Sub demo()
'If trueCount > 0 Then
Cst = "1"
hrs = "12"
scope = "none"
   Data = "Cost: " & Cst _
    & vbNewLine & "Hours: " & hrs _
    & vbNewLine & "Scope: " & scope
   'MsgBox Data
   Dim objWord
   Dim objDoc
   Set objWord = CreateObject("Word.Application")
   Set objDoc = objWord.Documents.Add
   objWord.Visible = True
   objDoc.Range.Text = Data
End Sub
Rahul
  • 10,830
  • 4
  • 53
  • 88