Contitional Format Text Box

  • Thread starter Thread starter NigelGraham
  • Start date Start date
N

NigelGraham

I use Text Boxes to display information prominently so
Management don't have to tax their brains when looking for
headline figures.
The Box content is provided by linking to a cell with the
data in it.
What they now want is that the box be red if the targets
are not met and green if met.
I have all that data in cells and can conditionaly format
them but they want the actual box background colour to
reflect the content of a cell only inches away.
There are about 60 such boxes on a sheet so it is not as
simple as just changeing their colour also the spread
sheet can display the performance of 32 different
locations so if correctly coloured for one it would be
wrong for the next location looked at.
I have read the help and it was very unhelpful.
 
Why use textboxes? Why not just use a summary sheet of the targets and use
conditional formatting on these?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Bob,
I thought about that but when printed it does not have the
same visual impact needed for management to pick up the
data quickly.
If it is not possible I may have to rethink but most
things in Excel are possible.

Nigel Graham.
..... looking across the railway sidings at Ilford Essex
wishing I was in Poole.
 
Nigel,

It is possible that we could trap the input and when it changes format of
the text box, but you say 60 textboxes (so presumably 60 cells to test), and
various conditions, Here is an example (based upon 3 textboxes)

Private Sub Worksheet_Change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1,B1,C1")) Is Nothing Then
With Target
Select Case Target.Address(False, False)
Case "A1": FormatTextBox .value, Me.TextBox1
Case "B1": FormatTextBox .value, Me.TextBox2
Case "C1": FormatTextBox .value, Me.TextBox3
End Select
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Sub FormatTextBox(value, ctl As msforms.TextBox)
Select Case value
Case Is > 100: ctl.BackColor = RGB(255, 0, 0)
Case Is > 50: ctl.BackColor = RGB(255, 120, 0)
Case Else: ctl.BackColor = RGB(0, 255, 0)
End Select
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top