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
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