Triggering a label or text box to show a value 1

[nelson97]

I would like to use a label or textbox to display a changing value of a cell, say cell R2. And then have it trigger a color change. The code below is what I'm conceptually trying to achieve:

Private Sub TextBox1_Change()
TextBox1.Value = Range("R2").Value
With TextBox1
If .Value = 5 Then Range("F2").Interior.ColorIndex = 23

End With

End Sub

Please advise, Thank you!

 

[SkipVought]

hi,

Private Sub TextBox1_Change()
    If TextBox1.Value = 5 Then Range("F2").Interior.ColorIndex = 23
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    TextBox1.Value = Range("R2").Value
End Sub

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[macropod]

Hi nelson,

To update the colour of F2 before updating the textbox value, try something along the lines of:
If ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text = "5" Then Range("F2").Interior.ColorIndex = 23
ActiveSheet.Shapes("Text Box 1").TextFrame.Characters.Text = Range("R2").Value

Reverse these if you want to update the textbox before updating the colour of F2. In that case, though, the use of a simple '=R2' input via the formula bar for the text box and conditional formatting for F2 would give the same result.

Cheers

 

[nelson97]

Thank you both for the helpful suggestions.
Kip, question on the first part of the code. When I try to add the *second line, it complains. What param am I missing here?

Private Sub TextBox1_Change()
If TextBox1.Value = 5 Then Range("F2").Interior.ColorIndex = 23
* Else
Range("F2").Interior.ColorIndex = xlNone
End If


Thanks

 

[Edski]

  If TextBox1.Value = 5 Then 
    Range("F2").Interior.ColorIndex = 23
  *  Else
        Range("F2").Interior.ColorIndex = xlNone
    End If

 

[nelson97]

Not sure if this should go on a new thread...
This is my updated code below. This code is on sheet3. When I make cell changes on Sheet1, the textbox1 on sheet 3 doesn't update itself until i actually click on sheet3 to activate it. How could I get the TextBox to update itself without having to actually click on sheet3?

Private Sub TextBox1_Change()
If (TextBox1.Value >= 3) Then
Sheets("Sheet1").Range("I3").Interior.ColorIndex = 10
Else
If (TextBox1.Value >= 2) Then
Sheets("Sheet1").Range("I3").Interior.ColorIndex = 6
Else
If (TextBox1.Value >= 0) Then
Sheets("Sheet1").Range("I3").Interior.ColorIndex = 3
End If
End If

End If
End Sub

 

[SkipVought]

I don't see where textbox1 has changed!

Skip,

_{[red]Be advised:[/red]When Viscounts were guillotined just as they were disclosing where their jewels were hidden, it shows to go that you should...
Never hatchet your Counts before they chicken!}

 

[nelson97]

Here's the full cycle of a single activity I would do:
1. I make a change in Sheet1 that updates the value of R2 in Sheet3. There's is no issue with R2 getting updated with the current values. (Function of R2: =SUM(Q2:Q3) )
It's the textbox that doesn't "acquire" the value in R2 unless I click on sheet 3. I don't want to have to do this because sheet3 is there to simply hold my data and calculate.



Private Sub TextBox1_Change()
If (TextBox1.Value >= 3) Then
Sheets("Sheet1").Range("I3").Interior.ColorIndex = 10
Else
If (TextBox1.Value >= 2) Then
Sheets("Sheet1").Range("I3").Interior.ColorIndex = 6
Else
If (TextBox1.Value >= 0) Then
Sheets("Sheet1").Range("I3").Interior.ColorIndex = 3
End If
End If

End If

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
TextBox1.Value = Range("R2").Value
End Sub