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?

 

[James006]

your code has not been working

Fine ...

Question 1 : Have you tested it ? Yes / No ?

Question 2 : Why on earth would you need to get the code to run on its own ... if it not working ...!!!

 

[Justinian]

Yes, I have tested it.

Sorry, let me clarify.

Your code from yesterday afternoon created multiple textboxes whenever the code runs instead of just updating the extant textbox. Your last code runs but only when I hit alt + F8. Once it runs, only the number appears (as opposed to the "+" or "-" that precedes the number in cell A1).

 

[James006]

Let's move on ...

All the suggested macros are tested beforehand ... so I do regret to inform you ... that they do produce the expected result ...

Now, you say you tested latest macro ... Fine ... BUT DOES IT WORK ... ???

1. More clearly, is the latest number copied to the Shape or not ???

2. Now the sign in front of the Number ... what is the precise Format you are using in the Source cell ???

3. Is it the General Format ? the Text Format ? ... Hope it is NOT a CUSTOM Format ....

 

[Justinian]

I just re-wrote the code and tested twice. The macro works when hitting alt + F8 but there is no "+" preceding the number when it is positive.

1. Yes.

2. Custom formatting ([Color10]+General;[Red]-General)

3. What should I sue instead?

 

[James006]

I am so incompetent that I did not guess it was the Custom format you are using that was making you lose the sign in the copy process ...

You can select any format with one single exception : CUSTOM ... !!! Would go with General ...

As a consequence, the sign is something will you need to add manually since you want it part of the Copy Process ...

Now to come to how things can be automated ...

First step for you is to write down the complete List of your "Couples" : " Source Cell - TextBox Name "

Then will come the question about what makes sense for you :
1. One Bulk Update ... i.e one macro does it all
or
2. As soon as you update a given Source Cell ... its display in your dashboard is automatically updated ...

 

[Justinian]

I have the source cells in A1 through A12 with textbox 1 in A1, textbox 2 in cell A2, and so on.

It would be nice if the macro ran as soon as the source cells are updated, if possible. If not, I can live with adding a button to run the macro.

 

[James006]

So to be clear about the Couples ...

Cell A1 is linked to TextBox1
Cell A2 is linked to TextBox2
Cell A3 is linked to TextBox3
etc ....
down to 12

Is that right ???

 

[James006]

Let me assume ... your answer is Yes...

Private Sub Worksheet_Change(ByVal Target As Range)
' Update Shape TextBoxes with Inputs in Cells A1 to A12
' 7 April 2023
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, Range("A1:A12")) Is Nothing Then Exit Sub
Dim i As Long
i = Target.Row
    With ActiveSheet.Shapes("TextBox " & i)
        .TextFrame.Characters.Text = Target.Value
        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]

Ok, I have everything set but I must have done something wrong. When I change the values in A1 through A12, the textboxes couples with those cells also change but the font remains black. I inserted your code into "This Workbook."

 

[James006]

I inserted your code into "This Workbook."

For your info, an event macro is related to the Sheet ....

SO .... it needs to be stored in the respective Sheet Module ...