If statement - offset text placement (from shape or cell)

GeeWhiz7

Board Regular
Joined
Nov 22, 2021
Messages
214
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
Lets see if I can even describe this correctly...

I have a userform that uses option buttons to help place certain shapes and then assign text to/around those shapes.
Part of the userform is in the image below and an example of what I'm trying to do in the other image.

My question is around using the option buttons and offset code to help place text.
  • Excel Max has helped me figure out how to let the text spill out of the shape if no text location button is clicked, but I would like to change how I am placing text if one of them IS clicked.
  • Desired result example, if Task is clicked and Text Above is clicked then text would be placed above the shape.
My thought process is in the code snippet below.
  • First IF code checks which text button is pushed
  • If TextACheck (a for above) then vText variable gets the offset information although I haven't been able to make it work this way yet.
VBA Code:
If TextACheck = True Then
        vText = ActiveCell.Offset(-1, 0)
    ElseIf TextBCheck = True Then
        vText = ActiveCell.Offset(1, 0)
    ElseIf TextLtCheck = True Then
        vText = ActiveCell.Offset(0, -1)
    ElseIf TextRtCheck = True Then
        vText = ActiveCell.Offset(0, 1)
    Else
        'If none of these are true then it should just follow the placement below in S.Textframe With
    End If

  • Second IF is checking the Event Type clicked (only Task shown), in this case Task so TaskCheck = True
  • Rectangle gets added, positioned relative to the active selected cell

VBA Code:
 If TaskCheck = True Then
        
        Set S = ws.Shapes.AddShape(Type:=msoShapeRectangle, _
        Left:=c.Left, _
        Top:=c.Top, _
        Width:=c.Width + Dur, _
        Height:=c.Height)              
        S.Fill.ForeColor.RGB = RGB(R, G, B)

If no Text button was clicked, the textframe object adds the text (from string variable tLabel) as default userform behavior so that part is fine.
But, how do I do the following if a userform Text button IS pushed?
  • Place the textframe text offset from the shape itself perhaps per vText variable above, or if that's not even possible,
  • Place the text not inside the shape, but simply in a cell that where offset is defined by vText variable and keep the textframe text blank.
  • I know how to do the cell text by itself with a button, but when the textframe comes into play, I get lost and documentation is not that helpful so far.

VBA Code:
     With S.TextFrame
                        
            .Characters.Text = tLabel
            ' how can I apply the offset from above to where the text is placed?
            .Characters.Font.ColorIndex = 3
            .Characters.Font.Color = RGB(0, 0, 0)
            .Characters.Font.Name = "Century Gothic"
            .Characters.Font.FontStyle = "Bold"
            .Characters.Font.Size = 12
            .HorizontalAlignment = xlHAlignCenter
            .VerticalAlignment = xlVAlignCenter
'            .TextFrame.WordWrap = False
'            .HorizontalOverflow = 1
            
        End With


1656084021794.png
1656084212522.png
 

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes
Lets see if I can even describe this correctly...

I have a userform that uses option buttons to help place certain shapes and then assign text to/around those shapes.
Part of the userform is in the image below and an example of what I'm trying to do in the other image.

My question is around using the option buttons and offset code to help place text.
  • Excel Max has helped me figure out how to let the text spill out of the shape if no text location button is clicked, but I would like to change how I am placing text if one of them IS clicked.
  • Desired result example, if Task is clicked and Text Above is clicked then text would be placed above the shape.
My thought process is in the code snippet below.
  • First IF code checks which text button is pushed
  • If TextACheck (a for above) then vText variable gets the offset information although I haven't been able to make it work this way yet.
VBA Code:
If TextACheck = True Then
        vText = ActiveCell.Offset(-1, 0)
    ElseIf TextBCheck = True Then
        vText = ActiveCell.Offset(1, 0)
    ElseIf TextLtCheck = True Then
        vText = ActiveCell.Offset(0, -1)
    ElseIf TextRtCheck = True Then
        vText = ActiveCell.Offset(0, 1)
    Else
        'If none of these are true then it should just follow the placement below in S.Textframe With
    End If

  • Second IF is checking the Event Type clicked (only Task shown), in this case Task so TaskCheck = True
  • Rectangle gets added, positioned relative to the active selected cell

VBA Code:
 If TaskCheck = True Then
       
        Set S = ws.Shapes.AddShape(Type:=msoShapeRectangle, _
        Left:=c.Left, _
        Top:=c.Top, _
        Width:=c.Width + Dur, _
        Height:=c.Height)             
        S.Fill.ForeColor.RGB = RGB(R, G, B)

If no Text button was clicked, the textframe object adds the text (from string variable tLabel) as default userform behavior so that part is fine.
But, how do I do the following if a userform Text button IS pushed?
  • Place the textframe text offset from the shape itself perhaps per vText variable above, or if that's not even possible,
  • Place the text not inside the shape, but simply in a cell that where offset is defined by vText variable and keep the textframe text blank.
  • I know how to do the cell text by itself with a button, but when the textframe comes into play, I get lost and documentation is not that helpful so far.

VBA Code:
     With S.TextFrame
                       
            .Characters.Text = tLabel
            ' how can I apply the offset from above to where the text is placed?
            .Characters.Font.ColorIndex = 3
            .Characters.Font.Color = RGB(0, 0, 0)
            .Characters.Font.Name = "Century Gothic"
            .Characters.Font.FontStyle = "Bold"
            .Characters.Font.Size = 12
            .HorizontalAlignment = xlHAlignCenter
            .VerticalAlignment = xlVAlignCenter
'            .TextFrame.WordWrap = False
'            .HorizontalOverflow = 1
           
        End With


View attachment 67859View attachment 67860
Somehow I've found a solution that works for this.

Snippet code that checks the text location box is below. If user doesn't select a text location then the default text is placed in a textframe via code in last post.
If user does select a text location button then the textframe text gets deleted and the text just gets put in an offset cell location with the code below. Depending on the feedback from users, I may modify this to put the text into an added shape with properties that hide it in place from view that is just offset from the original. For now this works.

VBA Code:
If TextACheck = True Then
        ActiveCell.Offset(-1, 0) = tLabel
        S.TextFrame2.DeleteText
        
        ElseIf TextBCheck = True Then
    
            ActiveCell.Offset(1, 0) = tLabel
            S.TextFrame2.DeleteText
        
        ElseIf TextLtCheck = True Then
    
            ActiveCell.Offset(0, -1) = tLabel
            S.TextFrame2.DeleteText
        
        ElseIf TextRtCheck = True Then
    
            ActiveCell.Offset(0, 1) = tLabel
            S.TextFrame2.DeleteText
    
        Else
    'If none of these are true then it should just follow the placement below in S.Textframe With
    End If
 
Upvote 0
Solution

Forum statistics

Threads
1,223,231
Messages
6,170,884
Members
452,364
Latest member
springate

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top