PDA

View Full Version : Solved: Changing Number Format of Variable Values



mbake16
02-03-2009, 11:50 AM
Hello:

I'm working with a macro which takes cell values in a worksheet and assigns them to a Textbox as in the example macro attached. Is there a way to change the number format of these values before they are assigned to the Textbox?

The code in the attached example is also below for quick reference:

Sub SumRangeCells()

Dim RngCell As Range
Dim UnitSalesCell As Range
Dim PercentChgCell As Range
Dim PercentChgAvg As Long
Dim PercentChgSum As Long
Dim UnitSalesSum As Double
Dim Counter As Long

For Each RngCell In Range("a2:a4")
If RngCell.Font.Bold = True Then
Counter = Counter + 1
UnitSalesSum = UnitSalesSum + RngCell.Value
Set PercentChgCell = RngCell.Offset(0, 1)
PercentChgSum = PercentChgSum + PercentChgCell.Value
End If
Next

PercentChgAvg = PercentChgSum / Counter

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 200, 10, 90, 60).Select

With Selection
.Characters.Text = "Units: " & UnitSalesSum & Chr(10) & "%Chg: " & PercentChgAvg
.Characters.Font.FontStyle = "bold"
.Characters.Font.Size = "9"
.HorizontalAlignment = xlCenter
End With

End Sub


Any help would be great!!


Thanks,


Matt

Bob Phillips
02-03-2009, 12:03 PM
Sub SumRangeCells()

Dim RngCell As Range
Dim UnitSalesCell As Range
Dim PercentChgCell As Range
Dim PercentChgAvg As Double
Dim PercentChgSum As Double
Dim UnitSalesSum As Double
Dim Counter As Long

For Each RngCell In Range("A2:A4")
If RngCell.Font.Bold = True Then
Counter = Counter + 1
UnitSalesSum = UnitSalesSum + RngCell.Value
Set PercentChgCell = RngCell.Offset(0, 1)
PercentChgSum = PercentChgSum + PercentChgCell.Value
End If
Next

PercentChgAvg = PercentChgSum / Counter

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 200, 10, 90, 60).Select

With Selection
.Characters.Text = "Units: " & UnitSalesSum & Chr(10) & "%Chg: " & Format(PercentChgAvg, "0.0%")
.Characters.Font.FontStyle = "bold"
.Characters.Font.Size = "9"
.HorizontalAlignment = xlCenter
End With

End Sub

mbake16
02-03-2009, 03:36 PM
Awesome! Thank you so much for the help. Quick question: how do you set the format to turn the font red if the number is negative? I tried something similar to the following: ("#,##0_);[Red](#,##0)" but it doesn't work. Thank you again!

Bob Phillips
02-03-2009, 04:46 PM
Instead of Format(val, number_format)

use

Appliaction.Text(val, number_format)

mbake16
02-04-2009, 06:18 AM
Great tip, didn't know you could do it that way. It changes the number to a negative but does not display it in red font. Not a big deal but just want to confirm that it's not possibly to change the font to red for a negative number in the textbox, is that correct?

Bob Phillips
02-04-2009, 06:24 AM
Yes, that is ciorrect. You would have to test the actula value (i.e. not what it shows) and set the colour of that.

mbake16
02-04-2009, 09:49 AM
Thank you again for all your help! Very much appreciated.