Working With a Text Box (Shape - not userform control)

[Ark68]

I have a textbox (shape - not a form control) named 'mapme5text' on my worksheet "MapMe5" (aka ws_mapme5). I would like to add dynamic text to that text box. I have this code in which I am attempting, and failing, to apply a dynamic custom message to that text box.

Sub btn_mapme4_Click()
    Stop
    ref = zone & Format(corridor, "00") & Format(segment, "00")
    segref = Application.WorksheetFunction.Match(ref, ws_segments.Columns(1), 0)
    segdesc = ws_segments.Cells(segref, 6)
    segtype = ws_segments.Cells(segref, 7)
    segdist = ws_segments.Cells(segref, 8)
    segsurf1 = ws_segments.Cells(segref, 12)
    segsurf2 = Application.WorksheetFunction.VLookup(segsurf1, ws_lists.Range("C:D"), 2, False)
    segice = ws_segments.Cells(segref, 11)
    msg1 = segdesc & "   " & segtype
    msg2 = segdist & " m.   (" & segsurf2 & ")"
    msg3 = "Ice: " & segice
    msg4 = "Notice: Unavailable"
    msg5 = "Hazard: Unavailable"
    msgmain = msg1 & Chr(13) & msg2 & Chr(13) & msg3 & Chr(13) & msg4 & Chr(13) & msg5 & Chr(13)
    Sheets("MapMe5").Shapes("mapme5text").Text = msgmain
    Sheets("MapMe5").Activate
End Sub

The line in red is leaving me with an error "Object doesn't support this property or method."

1) How do I apply the custom message (msgmain) to my text box? Clearly what I have tried isn't it. I tried .value instead of text and got the same error. BTW, ws_mapme5 is protected. Would it have to be unprotected to apply the message to the text box?
2) Can I use Chr(13) within my message concatenation to break the lines?
3) Is it possible to resize the text box shape based on the contents of the text box? I wish to avoid any dead space in the text box after the end of the message. This is so that I can move a button closer to the bottom of the text box.
4) How can I move the button to below the text box, once it's resized - if at all possible.

Thank you in advance for all the support.

 

[Domenic]

Maybe something like this...

Sub btn_mapme4_Click()
    
    ref = zone & Format(corridor, "00") & Format(segment, "00")
    segref = Application.WorksheetFunction.Match(ref, ws_segments.Columns(1), 0)
    segdesc = ws_segments.Cells(segref, 6)
    segtype = ws_segments.Cells(segref, 7)
    segdist = ws_segments.Cells(segref, 8)
    segsurf1 = ws_segments.Cells(segref, 12)
    segsurf2 = Application.WorksheetFunction.VLookup(segsurf1, ws_lists.Range("C:D"), 2, False)
    segice = ws_segments.Cells(segref, 11)
    msg1 = segdesc & "   " & segtype
    msg2 = segdist & " m.   (" & segsurf2 & ")"
    msg3 = "Ice: " & segice
    msg4 = "Notice: Unavailable"
    msg5 = "Hazard: Unavailable"
    
    msgmain = msg1 & Chr(13) & msg2 & Chr(13) & msg3 & Chr(13) & msg4 & Chr(13) & msg5 & Chr(13)
    
    Dim shp As Shape
    Set shp = Sheets("MapMe5").Shapes("mapme5text")
    
    With shp
        .TextFrame2.TextRange.Text = msgmain
        .TextFrame2.AutoSize = msoAutoSizeShapeToFitText
    End With
    
    With btn_mapme4
        '.Left = shp.Left
        .Top = shp.Top + shp.Height + 10
    End With
        
    Sheets("MapMe5").Activate
    
End Sub

Hope this helps!

 

[Ark68]

Thank you so much Domenic, this is awesome!
Wondering if this can be tweaked a bit?

1) The text box is being autosized (cool!), but can we restict the sizing to be only in the height, maintaining a static width?
2) Is it possible to format specific portions of the message? For example, msg4 bolding the text "Notice:" and colouring it blue, while maintaining the variable text (in this case I've used a static value of unavailable) as the default text box format, msg5 the same but coloured red instead of blue?

 

[Domenic]

Thank you so much Domenic, this is awesome!

You're very welcome, glad I could help.

Wondering if this can be tweaked a bit?

1) The text box is being autosized (cool!), but can we restict the sizing to be only in the height, maintaining a static width?
2) Is it possible to format specific portions of the message? For example, msg4 bolding the text "Notice:" and colouring it blue, while maintaining the variable text (in this case I've used a static value of unavailable) as the default text box format, msg5 the same but coloured red instead of blue?

Try the following code...

Sub btn_mapme4_Click()
    
    ref = zone & Format(corridor, "00") & Format(segment, "00")
    segref = Application.WorksheetFunction.Match(ref, ws_segments.Columns(1), 0)
    segdesc = ws_segments.Cells(segref, 6)
    segtype = ws_segments.Cells(segref, 7)
    segdist = ws_segments.Cells(segref, 8)
    segsurf1 = ws_segments.Cells(segref, 12)
    segsurf2 = Application.WorksheetFunction.VLookup(segsurf1, ws_lists.Range("C:D"), 2, False)
    segice = ws_segments.Cells(segref, 11)
    msg1 = segdesc & "   " & segtype
    msg2 = segdist & " m.   (" & segsurf2 & ")"
    msg3 = "Ice: " & segice
    msg4 = "Notice: Unavailable"
    msg5 = "Hazard: Unavailable"
    
    msgmain = msg1 & Chr(13) & msg2 & Chr(13) & msg3 & Chr(13) & msg4 & Chr(13) & msg5 & Chr(13)
    
    Dim shp As Shape
    Set shp = Sheets("MapMe5").Shapes("mapme5text")
    
    With shp
    
        Dim origWidth As Single
        origWidth = .width
        
        .TextFrame2.TextRange.Text = msgmain
        .TextFrame2.AutoSize = msoAutoSizeShapeToFitText
        
        .width = origWidth
        
        Dim pos As Long
        pos = InStr(1, msgmain, "Notice:", vbTextCompare)
        If pos > 0 Then
            With .TextFrame2.TextRange.Characters(pos, 7)
                .Font.Bold = msoTrue
                .Font.Fill.ForeColor.RGB = RGB(0, 0, 255)
            End With
        End If
        pos = InStr(1, msgmain, "Hazard:", vbTextCompare)
        If pos > 0 Then
            With .TextFrame2.TextRange.Characters(pos, 7)
                .Font.Bold = msoTrue
                .Font.Fill.ForeColor.RGB = RGB(255, 0, 0)
            End With
        End If
        
    End With
    
    With btn_mapme4
        '.Left = shp.Left
        .Top = shp.Top + shp.Height + 10
    End With
        
    Sheets("MapMe5").Activate
    
End Sub

By the way, it's considered good programming practice to declare all variables. So I would suggest you do so. Also, I would suggest that you add Option Explicit at the very top of your module. This will force the explicit declaration of variables, and it will help catch errors that may occur.

Cheers!

 

[Ark68]

Thanks Domenic, this works wonderfully. I appreciate your help.
And yes, Option Explicit. I've been told more than once, and I always start a fresh project using it. But when the hamsters have the energy and are on a roll with a concept, sometimes those variable declarations fall to the wayside. Just a habit I haven't forced myself into yet. I need more discipline!!

 

[Domenic]

Thanks Domenic, this works wonderfully. I appreciate your help.

That's great, I'm glad I could help. And thanks for your feedback.

And yes, Option Explicit. I've been told more than once, and I always start a fresh project using it.

Good.

But when the hamsters have the energy and are on a roll with a concept, sometimes those variable declarations fall to the wayside.

I like the hamster analogy.