Add a Textbox in Excel
The textbox we are talking about is an ActiveX control. Follow these steps to add a textbox control in your Excel worksheet.
1) Open Excel worksheet. From the above tabs, select the Developer tab and click "Design Mode".
2) Next, click Insert Option (before Design Mode inside Developer tab) and click "Text Box" under "ActiveX Controls".
3) Place the textbox anywhere in your worksheet.
4) This is important. Make sure the "Design Mode" (under the Developer tab) is still active. To change the border color of the textbox dynamically using VBA, we have change the BoderStyle property from none to single.
Therefore, right click the textbox and click Properties to open the properties window.
Find BorderStyle property in the properties window and choose frmBorderStyleSingle option.
5) Now you must Turn off design mode. Click the "Developer tab" and turn off "Design Mode".
Here's a condition. Let us assume, when you enter some data (or some text) in cell F4, it should automically change the textbox border color.
We can toggle the color change also. That is, if cell F4 has value, change color to Red and if its empty, change color to "Black".
The Macro to change textbox border color
Option Explicit Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$F$4" Then If Target.Value <> "" Then TextBox1.BorderColor = vbRed Else TextBox1.BorderColor = vbBlack End If End If End Sub
The macro to change border color is written inside Worksheet_Change event. So whenever it senses a change in cell F4, it first checks if the box has value or its empty and accordingly, changes the color of the border.
Change Textbox Border Color when it has Focus
This is how you can change textbox border color when the focus is on the textbox.
Option Explicit Private Sub TextBox1_GotFocus() TextBox1.BorderColor = vbRed End Sub
So, this is what we learned here today.
1) How to add a textbox control in an Excel worksheet.
2) How to change default properties like BorderStyle of a textbox control.
3) How to change border color of a textbox control dynamically using VBA.
That's it. 🙂