Changing Textbox text colour with a button push

L

Liquid Len

New Member
Joined
May 23, 2007
Messages
4
Hi All,

Hopefully this request for help is very simple. I would like to be able to do the following;

When a user selects a textbox, they could then push a Command Button which would change the text box colour to a pre-defined colour.

Thanks in advance.
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
Sort by date Sort by votes
Welcome to the board!


This is do-able, but not simple.
Are you comfortable with VBA?
Are you able to create the buttons and assign code to them?
Will need to build a couple of macros in VBA, what "pre-defined" colors do you want to use?
Is your textbox from the Controls Toolbar?
 
Upvote 0
This will cycle a Controls Textbox back ground color between Yellow and Green when run.
Code: 
Sub ChangeTextBoxYellowGreen()
    For Each Sh In ActiveSheet.Shapes
        If Sh.Type = msoOLEControlObject Then
            If TypeName(Sh.OLEFormat.Object.Object) = "TextBox" Then
                If Sh.OLEFormat.Object.Object.BackColor = &HC0FFC0 Then
                    Sh.OLEFormat.Object.Object.BackColor = &HFFFFF
                Else
                    Sh.OLEFormat.Object.Object.BackColor = &HC0FFC0
                End If
            End If
        End If
    Next Sh
End Sub
 
Upvote 0
Welcome to the board!


This is do-able, but not simple.
Are you comfortable with VBA?
Are you able to create the buttons and assign code to them?
Will need to build a couple of macros in VBA, what "pre-defined" colors do you want to use?
Is your textbox from the Controls Toolbar?
Click to expand...

Hi John,

Thanks for the speedy reply. In answer to your questions,

I can use VBA, but I would not describe myself as comfortable. I have produced some excel macro's in the past
I can create buttons and assign code to them
Colours are not important
The textbox is from the Drawings Toolbar (would it help to change this?)

Thanks again
 
Upvote 0
This code will work with a Textbox from the Drawing Toolbar named "Text Box 1".
Code: 
Sub ChangeTextBoxYellowGreen()
ActiveSheet.Shapes("Text Box 1").Select
    If Selection.ShapeRange.Fill.ForeColor.SchemeColor = 26 Then
        Selection.ShapeRange.Fill.ForeColor.SchemeColor = 42
    Else
       Selection.ShapeRange.Fill.ForeColor.SchemeColor = 26
    End If
[a1].Select
End Sub
 
Upvote 0
This code will work with a Textbox from the Drawing Toolbar named "Text Box 1".
Code: 
Sub ChangeTextBoxYellowGreen()
ActiveSheet.Shapes("Text Box 1").Select
    If Selection.ShapeRange.Fill.ForeColor.SchemeColor = 26 Then
        Selection.ShapeRange.Fill.ForeColor.SchemeColor = 42
    Else
       Selection.ShapeRange.Fill.ForeColor.SchemeColor = 26
    End If
[a1].Select
End Sub
Click to expand...

Hi John,

Thanks again for the reply. If the text box name is unknown, ie the user can click on any textbox in the sheet, how can I adapt this code to work?
 
Upvote 0
If you want the user to click the text box before running the macro just remove this line of code.
Code: 
ActiveSheet.Shapes("Text Box 1").Select
Then the code will work with just the selected text box.
 
Upvote 0
Hi John,

Thanks for you help, all is working well. I have even managed to do some more automation than I had originally planned.
 
Upvote 0
You must log in or register to reply here.

Similar threads

C
  • Question
Preventing graphs colours from changing
Replies
0
Views
110
Corleone
C
F
  • Question
Userform textbox launches another userform
Replies
8
Views
381
FedElecQaEng
F
A
  • Question
VBA to change cell colour based on content
Replies
3
Views
195
Snakehips
Snakehips
T
  • Question
Refer to a sheet selected by combobox
Replies
2
Views
99
dmt32
D
I
  • Solved
Text in Textbox to be added to stored code on command button
2
Replies
15
Views
446
ipbr21054
I

Forum statistics

Threads
1,215,198
Messages
6,123,593
Members
449,109
Latest member
Sebas8956

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top