Camera Tool Unwanted Copying

[Justinian]

I am creating a dashboard that requires the camera tool. The reason is because I have custom formatting (red if negative number and green if positive) and I cannot use custom formatting on shapes, which is what my dashboard consists of. Each section of my dashboard is a square and one each square, I have a donut chart showing work order created vs completed. In the middle of that chart, I want to display the difference between created and completed but I cannot use a shape with custom formatting so I am using the camera tool. The problem is when I open the workbook or make changes, sometimes the camera image copies on top of itself.

Any idea why this happens or how I can stop it?

 

[Justinian]

I may not be able to use that. The reason I use a standard textbox is so I can eliminate the border and fill so all you see is the font number as I am placing the textbox over another shape for use in my dashboard.

 

[James006]

Hi again,

Below is a starting point to test a Shape Text Box with No Borders , Transparent for your Dashboard, populated with the number located in cell A1 ...

Sub TestShapeTextBox()
Dim myCell As Range
Dim myTextBox As TextBox
    
Set myCell = ActiveCell
    
    With myCell
        Set myTextBox = .Parent.TextBoxes.Add(Top:=.Top, Left:=.Left, Width:=50, Height:=30)
    End With
        
    With myTextBox
        With .Font
            .Name = "Calibri"
            .FontStyle = "Italic"
            .Size = 12
            .ColorIndex = xlAutomatic
        End With
        ' Populate TextBox with the Value located in cell A1
        .Text = Range("A1").Value
        .HorizontalAlignment = xlCenter
        ' Adjust Font Color For Negative and Positive Numbers
        If Left(.Text, 1) = "-" Then
            .Font.Color = vbRed
        Else
            .Font.Color = vbGreen
        End If
        .ShapeRange.Fill.Visible = msoFalse
        .ShapeRange.Line.Visible = msoFalse
    End With
End Sub

Hope this will help

 

[Justinian]

This works but I have to run the macro each time I calculate and when that happens, it creates a new textbox instead of formatting the extant textbox.

 

[James006]

You are welcome

 

[Justinian]

That is not what I was requesting. I need to have only one textbox altered as there are many on my dashboard.

 

[Justinian]

Can anyone else help?

 

[James006]

Sub UpdateShapeTextBox()
    With ActiveSheet.Shapes("TextBox 1")
        ' Populate TextBox with the Value located in cell A1
        .TextFrame.Characters.Text = Range("A1").Value
        ' Adjust Font Color For Negative and Positive Numbers
        If Left(.TextFrame.Characters.Text, 1) = "-" Then
            .TextFrame.Characters.Font.Color = vbRed
        Else
            .TextFrame.Characters.Font.Color = vbGreen
        End If
    End With
End Sub

 

[Justinian]

How do I get the code to run on its own and through multiple text boxes?

 

[James006]

Really appreciate the way you do keep on showing your gratitude.

 

[Justinian]

Um, ok, I am grateful but so far, your code has not been working.