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]

No to all of those.

Maybe an easier question would be how do I wrote code for the text box that says if the number is negative, make it red and if it is positive, make it green?

 

[James006]

What kind of controls are you working with, in your worksheet : Forms controls or ActiveX controls ?

 

[Justinian]

Just a regular text box.

 

[James006]

You could test following macro to be stored in the respective Sheet module :

Private Sub TextBox1_Change()
   If Left(TextBox1.Text, 1) = "-" Then
      TextBox1.BackColor = vbRed
   Else
      TextBox1.BackColor = vbGreen
   End If
End Sub

 

[Justinian]

I tried that but the color portion is not working:

Book1
	B	C	D	E	F	G	H	I	J
6
7
8									+23
9
10
11
12
13
14
15
Sheet1

 

[Justinian]

 

[Justinian]

I found this code but it keeps the font green regardless of negtive value:

Sub Color_Text_InBox()
ActiveSheet.Shapes.Range(Array("TextBox 1")).Select

If Range("a1").Value < 0 Then
'<0 is better
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 176, 80)
.Transparency = 0
.Solid
End With
Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoTrue
Else
'color the text box to show "not better"
With Selection.ShapeRange.TextFrame2.TextRange.Font.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
Selection.ShapeRange.TextFrame2.TextRange.Font.Bold = msoTrue
End If

End Sub

 

[James006]

Hi again,

I know it might sound confusing ....

If I understanding correctly ...
1. You are not using any Control Object from Developper>Insert to select either an ActiveX or a Forms TextBox
2. But you are using a Shape textbox ... what you are calling regular

These very different objects have very different characteristics and features, and offer a lot more OR a lot less in terms of flexibility.

As a matter of fact, based on what your actual final intent is, you should pick the appropriate Object, fully adapted to your specific situation

Hope this clarifies

 

[Justinian]

Yes, I am going to insert > text box. The codce I posted above works but it does not change the font color to red when the number is negative.

What kind of text box should I use with your code?

 

[James006]

To have the maximum flexibility, would recommend the ActiveX TextBox ...