[ashleywanless]

Text box border macro based on if text box contains data
Hi i have been trying to create some worksheet code so that for a particular sheet if a textbox is blank the border does not show, but if this contains data the border does show. I have the below code, this works if the textbox contains no data but has a border, the code removes the border. However if the text box contains data but has no border the code doesnt draw the border. I am very new to vb so any help gratefully received.

code is:

Private Sub Worksheet_PivotTableUpdate(ByVal Target As PivotTable)

Sheets("LRCR by division-open").Select

Shapes("Text Box 1").Select

If TextBox1 = "" Then

Selection.ShapeRange.Fill.Visible = msoTrue

Selection.ShapeRange.Fill.Solid

Selection.ShapeRange.Fill.ForeColor.SchemeColor = 65

Selection.ShapeRange.Fill.Transparency = 0#

Selection.ShapeRange.Line.Weight = 0.75

Selection.ShapeRange.Line.DashStyle = msoLineSolid

Selection.ShapeRange.Line.Style = msoLineSingle

Selection.ShapeRange.Line.Transparency = 0#

Selection.ShapeRange.Line.Visible = msoFalse

Else

Selection.ShapeRange.Fill.Visible = msoFalse

Selection.ShapeRange.Fill.Solid

Selection.ShapeRange.Fill.Transparency = 0#

Selection.ShapeRange.Line.Weight = 0.75

Selection.ShapeRange.Line.DashStyle = msoLineSolid

Selection.ShapeRange.Line.Style = msoLineSingle

Selection.ShapeRange.Line.Transparency = 0#

Selection.ShapeRange.Line.Visible = msoTrue

Selection.ShapeRange.Line.ForeColor.SchemeColor = 64

Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

End If

End Sub


thanks

[ckphilli]

Just a shot here...I'm new to it myself...try adding

Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)

in the IF section just before ELSE

Just thinking the two sections should be identical except for the true/falses... and you said you had success with no data...just need it to fill when you enter data which is the IF section if I read it right.

Let me know how it goes...again, I'm very new to this also and still learning.

[Rollin_Again]

You need to see if the code under the ELSE statement is even firing. Put a messagebox as the first line after the ELSE and run the code where a border is supposed to be added. If you don't see the messagebox then you know that the code is not even firing.

Regards,
Rollin

[ashleywanless]

Hi,

Firstly thanks for the responses. I tried adding the line you suggested ckphilli but this didnt work. Rollin_Again i tried adding the message box and you were right, the code after else is not even firing.

The first part of the code works perfectly which is the if text box is blank but has a border remove it. Like i said i am pretty new to this so i thought ok if the second part of the code isnt working then i'll write the code in a different way for the second part, as another IF but this time obviously replacing the "" with <>"". However typing in <>"" into the code causes a compile error: Expected: expression. Any ideas why this is??

Any further help you can provide will be gratefully received.

Thanks

[Rollin_Again]

Can you post your sample workbook and I'll take a look at it.

Regards,
Rollin

[ashleywanless]

Hi Rollin,

Thanks i will post a copy of that one sheet in about 2 hours...its part of a much bigger overall dashboard so i just need to move it into a standalone workbook or the file size will be too big.

Thank you much appreciated. If you can figure it out let me know what i ahve been doing wrong and i'll make sure i dont do it again!!

Thanks

[Rollin_Again]

In the meantime try including the .Text property of the field in your IF statement. I would also advise to test for the IsEmpty property. If that doesn't work I'll take a look at the posted workbook later today.

If TextBox1.Text = "" or IsEmpty(TextBox1.Text) Then

Regards,
Rollin

[ashleywanless]

Hi Rollin,

Tried your suggestions but these didnt work. The .text solution gave me an object required error. The other solution gave no errors at all but again didnt remove the border if the text box was blank.

I have attached the file but have had to remove the pivot table, can you test this ok using a different trigger for the macro?

Thanks in advance for your help, it just seems to be an issue with the second part of the code.

[ashleywanless]

File should now be attached, its a 97-02 xls file

[Rollin_Again]

Change your IF statement so that it reads.

If ActiveSheet.Shapes("Text Box 1").OLEFormat.Object.Characters.Text = "" Then

Regards,
Rollin

[ashleywanless]

Rollin,

Works perfectly. I assume that any similar code where i need to select an OLB Object or use it as a variable i need to write the code in the same manner?

Thank you for all your help

[Rollin_Again]

Yes just follow the same format.

Regards,
Rollin