How do I show a value in a Shape ? 2

[Recce]

Good day,

I make use of shapes with clours in my dashboard but, I would also like to draw a value into the shape or dislay a value in the shape.

Wold anyone know if this is possible ?

"We know nothing but, that what we know is not the truth..." - Me

 

[SkipVought]

Hi,

Design and store 10 shapes.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

...or dislay a value in the shape."

I missed the second part of your question.

Turn on your macro recorder and record adding a value to your shape, along with all the formatting you wish to display.

Post back with your recorded your recorded code if you need help customizing to your requirements.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Recce]

Hi Skip,

Thanks for this. I have tried to record a Macro but, for some reason it does not record the caracteristics of the shape or anything I do with the shape....

I do however manage to give the shape colour with some code that I got from the help files.

What I can do with the colour is below but,I have no idea how to set a Value to the shape:


Private Sub Worksheet_Change(ByVal Regions As Range)

Dim MyValue As Long
Dim MyRange
MyValue1 = Worksheets(1).Range("C19")
MyValue2 = Worksheets(1).Range("C21")
MyValue3 = Worksheets(1).Range("C23")

'*****Red
If MyValue1 >= 0 And MyValue1 <= 10 Then
Set myDocument = Worksheets(1)
Set MyRange = myDocument.Shapes.Range(Array("Rectangle 42"))
MyRange.Fill.ForeColor.RGB = _
RGB(255, 0, 0)

'*****Green
ElseIf MyValue1 >= 11 And MyValue1 <= 20 Then
Set myDocument = Worksheets(1)
Set MyRange = myDocument.Shapes.Range(Array("Rectangle 42"))
MyRange.Fill.ForeColor.RGB = _
RGB(255, 255, 0)

'*****Yellow
Else
Set myDocument = Worksheets(1)
Set MyRange = myDocument.Shapes.Range(Array("Rectangle 42"))
MyRange.Fill.ForeColor.RGB = _
RGB(0, 255, 0)

End If


'*****Red
If MyValue2 >= 0 And MyValue2 <= 10 Then
Set myDocument = Worksheets(1)
Set MyRange = myDocument.Shapes.Range(Array("Rectangle 43"))
MyRange.Fill.ForeColor.RGB = _
RGB(255, 0, 0)

'*****Green
ElseIf MyValue2 >= 11 And MyValue2 <= 20 Then
Set myDocument = Worksheets(1)
Set MyRange = myDocument.Shapes.Range(Array("Rectangle 43"))
MyRange.Fill.ForeColor.RGB = _
RGB(255, 255, 0)

'*****Yellow
Else
Set myDocument = Worksheets(1)
Set MyRange = myDocument.Shapes.Range(Array("Rectangle 43"))
MyRange.Fill.ForeColor.RGB = _
RGB(0, 255, 0)

End If


'*****Red
If MyValue3 >= 0 And MyValue3 <= 10 Then
Set myDocument = Worksheets(1)
Set MyRange = myDocument.Shapes.Range(Array("Rectangle 44"))
MyRange.Fill.ForeColor.RGB = _
RGB(255, 0, 0)

'*****Green
ElseIf MyValue3 >= 11 And MyValue3 <= 20 Then
Set myDocument = Worksheets(1)
Set MyRange = myDocument.Shapes.Range(Array("Rectangle 44"))
MyRange.Fill.ForeColor.RGB = _
RGB(255, 255, 0)

'*****Yellow
Else
Set myDocument = Worksheets(1)
Set MyRange = myDocument.Shapes.Range(Array("Rectangle 44"))
MyRange.Fill.ForeColor.RGB = _
RGB(0, 255, 0)

End If
End Sub




"We know nothing but, that what we know is not the truth..." - Me

 

[SkipVought]

...it does not record the caracteristics of the shape or anything I do with the shape..."

Hence my FIRST suggestion: Make the shapes that mimic the numeric digits 0 thru 9 and store them on a sheet. Use these shapes to construct you values.

The MACRO suggestion was regarding the second suggestion of adding a value to a shape. That CAN be recorded, along with FORMATTING the shape (colors, lines, font etc).

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveInIowa]

Once you have a reference to the shape, you can then modify the text via the TextFrame property.

Sub ChangeTextInShapes()
    Dim s As Shape
    
    For Each s In ActiveSheet.Shapes
        s.TextFrame.Characters.Text = Format(Now, "h:mm:ss AM/PM")
    Next
    
End Sub

 

[SkipVought]

Thanks Dave...

hence my suggestion regarding RECORDING macros and posting back to customize. Just so you know HOW it can be achieved.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[DaveInIowa]

Unfortunately recording a macro for this doesn't point you to the TextFrame property, but instead relies (as most macro recordings do) on the current Selection.

 

[SkipVought]

But it's the PROCESS that is important here. If you short-circuit the process, you miss this disconnect.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Recce]

Hi guys,

Thanks for the help and thanks for the code DaveInIowa...

It works brilliantly ....

"We know nothing but, that what we know is not the truth..." - Me

 

[Recce]

Sorry about this. Just one other thing. Would you guys know how one can make the values scroll ? or if it is even possible ?

"We know nothing but, that what we know is not the truth..." - Me

 

[SkipVought]

Why don't you post that question in another thread, since it is really not directly related to your original question.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Recce]

Agree, Sorry about that...

"We know nothing but, that what we know is not the truth..." - Me