TextBox backcolor

[saint1415]

So I have a Geographic Heat Map with Textboxes for all 50 states and 1 for DC. I need to see about adding to the following code to make the Textbox backcolor/fillcolor equal the state color.

Sub Paint()
Dim i As Integer
For i = 1 To 50
'First, paint the state shapes
Range("actorder").Value = i
ActiveSheet.Shapes(Range("actstate").Value).Fill.ForeColor.RGB = _
Range(Range("actcolorcode").Value).Interior.Color


ActiveSheet.Shapes(Range("acttext").Value).Select
With Selection
'Second, update the text boxes
.Text = Range("acttextvalue").Value

'(Optional) Format the text boxes
.ShapeRange.Fill.ForeColor.RGB = RGB(0, 0, 0)
.ShapeRange.Fill.Transparency = 1
.ShapeRange.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
.ShapeRange.TextFrame2.TextRange.Font.Shadow.Visible = False
.ShapeRange.TextFrame2.MarginLeft = 2.5
.ShapeRange.TextFrame2.MarginRight = 2.5
End With
Next i




End Sub

Any help is greatly appreciated. If you want a copy of the original file please let me know.

 

[DanteAmor]

Re: New to VBA. Need some TextBox backcolor help

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Explain a couple of examples what you need

 

[saint1415]

Re: New to VBA. Need some TextBox backcolor help

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.

Explain a couple of examples what you need

https://drive.google.com/open?id=15sbO-k051wuPDGPkP2BxiEz1LX0GxabO

I'm looking now to input the state abbreviation, then a line break, and then the data associated with the given state. Any help is greatly appreciated.

Thank you in advance.

 

[DanteAmor]

Re: New to VBA. Need some TextBox backcolor help

https://drive.google.com/open?id=15sbO-k051wuPDGPkP2BxiEz1LX0GxabO

I'm looking now to input the state abbreviation, then a line break, and then the data associated with the given state. Any help is greatly appreciated.

Thank you in advance.

Do not have problems with the color?
I executed the macro and the Textbox backcolor/fillcolor is equal the state color.

Where do you want to enter abbreviation and where do you want the result?

 

[saint1415]

Re: New to VBA. Need some TextBox backcolor help

Do not have problems with the color?
I executed the macro and the Textbox backcolor/fillcolor is equal the state color.

Where do you want to enter abbreviation and where do you want the result?

I corrected the color issue. I need to have the abbreviation on the first line in the text box and the number on the 2nd line.

Example:

FL
200

 

[DanteAmor]

Re: New to VBA. Need some TextBox backcolor help

Try this:

Sub Paint()


    Dim i As Integer
    For i = 1 To 51
        'First, paint the state shapes
        Range("actorder").Value = i
        ActiveSheet.Shapes(Range("actstate").Value).Fill.ForeColor.RGB = _
        Range(Range("actcolorcode").Value).Interior.Color
        
        ActiveSheet.Shapes(Range("acttext").Value).Select
        With Selection
            'Second, update the text boxes
[COLOR=#0000ff]            .Text = Range("acttextvalue").Value & vbCr & _[/COLOR]
[COLOR=#0000ff]                    Range("actstatevalu").Value[/COLOR]
            
            '(Optional) Format the text boxes
            .ShapeRange.Fill.ForeColor.RGB = _
            Range(Range("actcolorcode").Value).Interior.Color
            .ShapeRange.Fill.Transparency = 0
            .ShapeRange.TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
            .ShapeRange.TextFrame2.TextRange.Font.Shadow.Visible = False
            '.ShapeRange.TextFrame2.MarginLeft = 2.5
            '.ShapeRange.TextFrame2.MarginRight = 2.5
        End With
    Next i


End Sub