Changing Textbox text colour with a button push

[Liquid Len]

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.

 

[Datsmart]

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?

 

[Datsmart]

This will cycle a Controls Textbox back ground color between Yellow and Green when run.

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

 

[Liquid Len]

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?

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

 

[Datsmart]

This code will work with a Textbox from the Drawing Toolbar named "Text Box 1".

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

 

[Liquid Len]

This code will work with a Textbox from the Drawing Toolbar named "Text Box 1".

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

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?

 

[Datsmart]

If you want the user to click the text box before running the macro just remove this line of code.

ActiveSheet.Shapes("Text Box 1").Select

Then the code will work with just the selected text box.

 

[Liquid Len]

Hi John,

Thanks for you help, all is working well. I have even managed to do some more automation than I had originally planned.

 

[Datsmart]

Glad you were able to work things out.