I need a Textbox with Formula from Sheet 2 to change text colors automatically w

Question

I need a Textbox with Formula from Sheet 2 to change text colors automatically when cell in Sheet 1 changes color.

HI there.

I am using Excel 2013

So I have Sheet 1 which is like a Seating Chart.

All names are shown in black and vacant seats are shown in red.

In Sheet 2 I have a Seating Map I had to copy and paste.

I created a Text Box for each of the seats and I linked a simple formula for example: ( ='Sheet1'!B1 )

The seats that are currently vacant are in red.

Once I change a cell from vacant to a name and change the color to black, the text box in Sheet 2 will update the name but stays red.

I would like my Seating Map to automatically change the names and color if possible.

Please help !

Thanks!!

Explanation / Answer

The only way I can think of is to have a Worksheet_Change procedure for Sheet1 that identifies the relevant textboxes on Sheet2 and changes the colour.

If the textboxes do not currently have names that identify them with the location on Sheet1 then you will need to set their names, e.g.

Sub NameTextBoxes()
Dim TB As TextBox
For Each TB In Sheets("Sheet2").TextBoxes
    TB.Name = Trim("TB" & Mid(TB.Formula, InStr(TB.Formula, "!") + 1))
Next
End Sub

Hi..

Now the textbox above will be named "TBB1":

The Worksheet_Change procedure in the module behind Sheet1 would be something like:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range
Dim stAddr As String
Dim stTB As String
Dim TB As TextBox
For Each C In Target.Cells
    stAddr = Replace(C.Address, "$", "")
    stTB = "TB" & stAddr
    On Error GoTo NoTB
    Set TB = ThisWorkbook.Sheets("Sheet2").TextBoxes(stTB)
    On Error GoTo 0
    If C.Value = "" Then
      TB.Interior.ColorIndex = 1 ' black
    Else
      TB.Interior.ColorIndex = 3 ' red
    End If
NextTB:
Next
Exit Sub
NoTB:
MsgBox "Text box " & stTB & " not found"
Resume NextTB
End Sub

Hope this help !!

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.