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.
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?
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.
- 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