Cash Forecast Report Commentary Update VBA

[Stupid Idiot]

Hey,

I just started to learn how to use VBA and i need help.
So i prepare a monthly cashflow forecast report that shows our ending balances for the next 3 month charted against our internal KPI, also there is some commentary in a text box that breaks down the major cash activities for the same period. Is there a way that i can use VBA to take these numbers for the cash activities (which are stored in a different sheet) and combine these figure with provided text templates to produce this commentary?

Thanks in advance

 

[kevin9999]

Short answer is yes. Here's a demo of how you can take values from a sheet & add them to some pre-existing text into a textbox.
With this kind of setup:

Book1
	A	B	C	D	E
1
2		Month	October	November	December
3		Budget	$1,500	$1,500	$1,500
4		Forecast	$1,250	$1,600	$1,500
5		Variance	-$250	$100	$0
6
Sheet2

Cell Formulas
Range	Formula
C5:E5	C5	=C4-C3

And this code:

Option Explicit
Sub Write_To_Textbox()
    
    'get month variables, and string variables
    Dim m1 As String, m2 As String, m3 As String
    Dim s1 As String, s2 As String, s3 As String
    m1 = Range("C2")
    m2 = Range("D2")
    m3 = Range("E2")
    
    'Build s1
    If Range("C4") < Range("C3") Then
        s1 = m1 & " is expecting a shortfall of " & Format(Range("C5"), "$0")
    ElseIf Range("C4") > Range("C3") Then
        s1 = "Revenue is expected to exceed budget in " & m1 & " by " & Format(Range("C5"), "$0")
    Else
        s1 = "Revenue is expected to match budget in " & m1
    End If
    
    'Build s2
    If Range("D4") < Range("D3") Then
        s2 = m2 & " is expecting a shortfall of " & Format(Range("D5"), "$0")
    ElseIf Range("D4") > Range("D3") Then
        s2 = "Revenue is expected to exceed budget in " & m2 & " by " & Format(Range("D5"), "$0")
    Else
        s2 = "Revenue is expected to match budget in " & m2
    End If
    
    'Build s3
    If Range("E4") < Range("E3") Then
        s3 = m3 & " is expecting a shortfall of " & Format(Range("E5"), "$0")
    ElseIf Range("E4") > Range("E3") Then
        s3 = "Revenue is expected to exceed budget in " & m3 & " by " & Format(Range("E5"), "$0")
    Else
        s3 = "Revenue is expected to match budget in " & m3
    End If
    
    'Put text into textbox
    With ActiveSheet.Shapes("Textbox 1")
        .TextFrame.Characters.Text = s1 & Chr(10) & s2 & Chr(10) & s3
    End With    
End Sub

You can get this kind of result in an existing textbox on the sheet: