Make the bottom border of textbox visible

[zinah]

Hi,

I have below macro that create boxes and I need to show only the bottom borders of these boxes, can anyone help me with that?

'''BUILD THE eCnt LABEL BOX'''    
    ew = galaxyF.Width / 2.3
    eh = galaxyF.Height / eCnt
    el = galaxyF.Left
    et = galaxyF.Top + (eh * (e - 1))
Set Empl_Lbl = rSht.Shapes.AddShape(msoShapeRectangle, el, et, ew, eh)
With Empl_Lbl
    .Name = "Empl_" & e & "_Lbl"
    .Placement = xlFreeFloating
    .Fill.ForeColor.RGB = RGB(255, 255, 255)
    .Fill.Visible = msoFalse
    .Line.ForeColor.RGB = RGB(100, 100, 100)
    .Line.Visible = False
    .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
    .TextFrame2.TextRange.Font.Size = 8
    .TextFrame2.TextRange.Font.Name = "Tahoma"
    .TextFrame.MarginLeft = 0.05 * pix
    .TextFrame.MarginRight = 0.05 * pix
    .TextFrame.MarginTop = 0.05 * pix
    .TextFrame.MarginBottom = 0.05 * pix
    .TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignLeft
    .TextFrame2.VerticalAnchor = msoAnchorMiddle
    .TextFrame2.AutoSize = msoAutoSizeNone
    .TextFrame.VerticalOverflow = xlOartVerticalOverflowOverflow
    .TextFrame.HorizontalOverflow = xlOartHorizontalOverflowOverflow
    .TextFrame2.WordWrap = msoTrue




.TextFrame2.TextRange.Characters.Text = "LABEL " & e & ":"
    .TextFrame2.TextRange.Font.Bold = msoTrue
End With

 

[DanteAmor]

Check my suggestion in post # 7.
upload the image to dropbox and paste the link here

 

[zinah]

Hi,
Here you go
https://www.dropbox.com/sh/oc0oegffzkjvfjc/AACaeNwrSmPpL4tcYukEuG2na?dl=0

What I want to do is to create macro that has loop to build lines under each shape (rectangular) that I created

 

[DanteAmor]

Immediately after creating the shape you must create the line. Example:

    el = galaxyF.Left
    et = galaxyF.Top + (eh * (e - 1))
    ew = galaxyF.Width / 2.3
    eh = galaxyF.Height / eCnt


    Set Empl_Lbl = rsht.Shapes.AddShape(msoShapeRectangle, el, et, ew, eh)
    
[B][COLOR=#0000ff]    Set empl_lin = rsht.Shapes.AddLine(el, et + eh, el + ew, et + eh).Line[/COLOR][/B]
    
    With Empl_Lbl
        .Name = "Empl_" & e & "_Lbl"
        .Placement = xlFreeFloating
        .Fill.ForeColor.RGB = RGB(255, 255, 255)
        .Fill.Visible = msoFalse
        .Line.ForeColor.RGB = RGB(100, 100, 100)
        .Line.Visible = False
        .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
        .TextFrame2.TextRange.Font.Size = 8
        .TextFrame2.TextRange.Font.Name = "Tahoma"
        .TextFrame.MarginLeft = 0.05 * pix
        .TextFrame.MarginRight = 0.05 * pix
        .TextFrame.MarginTop = 0.05 * pix
        .TextFrame.MarginBottom = 0.05 * pix
        .TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignLeft
        .TextFrame2.VerticalAnchor = msoAnchorMiddle
        .TextFrame2.AutoSize = msoAutoSizeNone
        .TextFrame.VerticalOverflow = xlOartVerticalOverflowOverflow
        .TextFrame.HorizontalOverflow = xlOartHorizontalOverflowOverflow
        .TextFrame2.WordWrap = msoTrue
        .TextFrame2.TextRange.Characters.Text = "LABEL " & e & ":"
        .TextFrame2.TextRange.Font.Bold = msoTrue
    End With

 

[zinah]

OMG! that's exactly what I want, thank you so much.
But when I ran the macro the lines took exactly the size of the Emp_Lbl what I need is the line size to take the size of galaxyF.Width, then name the new lines as

.Name = "Empl_" & e & "_line"

How can I do that?

 

[zinah]

I made it with this macro

    ew = galaxyF.Width - Empl_Lbl.Width    eh = Empl_Lbl.Height
    el = Empl_Lbl.Left + Empl_Lbl.Width
    et = Empl_Lbl.Top
    Ll = galaxyF.Left
   


Set Empl_Txt = rSht.Shapes.AddShape(msoShapeRectangle, el, et, ew, eh)
Set Empl_Lin = rSht.Shapes.AddLine(Ll, et + eh, el + ew, et + eh).Line


With Empl_Txt
    .Name = "Empl_" & e & "_Txt"
    .Placement = xlFreeFloating
    .Fill.ForeColor.RGB = RGB(255, 255, 255)
    .Fill.Visible = msoFalse
    .Line.ForeColor.RGB = RGB(100, 100, 100)
    .Line.Visible = False
    
    .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
    .TextFrame2.TextRange.Font.Size = 8
    .TextFrame2.TextRange.Font.Name = "Tahoma"
    .TextFrame.MarginLeft = 0.05 * pix
    .TextFrame.MarginRight = 0.05 * pix
    .TextFrame.MarginTop = 0.05 * pix
    .TextFrame.MarginBottom = 0.05 * pix
    .TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignLeft
    .TextFrame2.VerticalAnchor = msoAnchorMiddle
    .TextFrame2.AutoSize = msoAutoSizeNone
    .TextFrame.VerticalOverflow = xlOartVerticalOverflowOverflow
    .TextFrame.HorizontalOverflow = xlOartHorizontalOverflowOverflow
    .TextFrame2.WordWrap = msoTrue


.TextFrame2.TextRange.Characters.Text = "TEXT " & Format(e, "00000")
    .TextFrame2.TextRange.Font.Bold = msoFalse




End With

I only need now to name these lines

[COLOR=#333333].Name = "Empl_" & e & "_line"[/COLOR]

 

[zinah]

And here's the final successful macro, thank you soooo much for your help!

'''BUILD THE eCnt TEXT BOX'''    ew = galaxyF.Width - Empl_Lbl.Width
    eh = Empl_Lbl.Height
    el = Empl_Lbl.Left + Empl_Lbl.Width
    et = Empl_Lbl.Top
    Ll = galaxyF.Left
   


Set Empl_Txt = rSht.Shapes.AddShape(msoShapeRectangle, el, et, ew, eh)




With Empl_Txt
    .Name = "Empl_" & e & "_Txt"
    .Placement = xlFreeFloating
    .Fill.ForeColor.RGB = RGB(255, 255, 255)
    .Fill.Visible = msoFalse
    .Line.ForeColor.RGB = RGB(100, 100, 100)
    .Line.Visible = False
    
    .TextFrame2.TextRange.Font.Fill.ForeColor.RGB = RGB(0, 0, 0)
    .TextFrame2.TextRange.Font.Size = 8
    .TextFrame2.TextRange.Font.Name = "Tahoma"
    .TextFrame.MarginLeft = 0.05 * pix
    .TextFrame.MarginRight = 0.05 * pix
    .TextFrame.MarginTop = 0.05 * pix
    .TextFrame.MarginBottom = 0.05 * pix
    .TextFrame2.TextRange.ParagraphFormat.Alignment = msoAlignLeft
    .TextFrame2.VerticalAnchor = msoAnchorMiddle
    .TextFrame2.AutoSize = msoAutoSizeNone
    .TextFrame.VerticalOverflow = xlOartVerticalOverflowOverflow
    .TextFrame.HorizontalOverflow = xlOartHorizontalOverflowOverflow
    .TextFrame2.WordWrap = msoTrue


    .TextFrame2.TextRange.Characters.Text = "TEXT " & Format(e, "00000")
    .TextFrame2.TextRange.Font.Bold = msoFalse


End With


Set Empl_Lin = rSht.Shapes.AddLine(Ll, et + eh, el + ew, et + eh)


With Empl_Lin
    .Name = "Empl_" & e & "_line"
    .Placement = xlFreeFloating
    .Fill.Visible = msoFalse
    .Line.ForeColor.RGB = RGB(100, 100, 100)
    .Line.DashStyle = msoLineSysDash
    .Line.Visible = True
    
End With


Next e

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.