1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Text box border macro based on if text box contains data

Discussion in 'Business Applications' started by ashleywanless, Sep 26, 2009.

Thread Status:
Not open for further replies.
Advertisement
  1. ashleywanless

    ashleywanless Thread Starter

    Joined:
    Jul 19, 2009
    Messages:
    22
    [​IMG] 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
     
  2. ckphilli

    ckphilli

    Joined:
    Apr 29, 2006
    Messages:
    4,393
    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.:)
     
  3. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  4. ashleywanless

    ashleywanless Thread Starter

    Joined:
    Jul 19, 2009
    Messages:
    22
    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
     
  5. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Can you post your sample workbook and I'll take a look at it.

    Regards,
    Rollin
     
  6. ashleywanless

    ashleywanless Thread Starter

    Joined:
    Jul 19, 2009
    Messages:
    22
    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
     
  7. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    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
     
  8. ashleywanless

    ashleywanless Thread Starter

    Joined:
    Jul 19, 2009
    Messages:
    22
    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.
     
  9. ashleywanless

    ashleywanless Thread Starter

    Joined:
    Jul 19, 2009
    Messages:
    22
    File should now be attached, its a 97-02 xls file
     

    Attached Files:

  10. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Change your IF statement so that it reads.

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

    Regards,
    Rollin
     
  11. ashleywanless

    ashleywanless Thread Starter

    Joined:
    Jul 19, 2009
    Messages:
    22
    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
     
  12. Rollin_Again

    Rollin_Again

    Joined:
    Sep 4, 2003
    Messages:
    4,912
    Yes just follow the same format.

    Regards,
    Rollin
     
  13. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/863893

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice