Need Help adding a TextBox thru VBA

  • Thread starter Thread starter NetWave128
  • Start date Start date
N

NetWave128

Sub AddDocumentation()
Dim MyShape As Object

On Error GoTo ErrorHandler

Set MyShape
ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 400, 200
400, 200)

MyShape.TextFrame.Characters.Text = "<Text Box Please Type Text Here>"
MyShape.TextFrame.Characters.Font.Name = "Arial"
MyShape.TextFrame.Characters.Text.Font.Size = 12

'the part below is what doesn't work
MyRange.Fill.ForeColor.SchemeColor = 43
MyShape.Fill.Visible = msoTrue
MyShape.Fill.Solid
MyShape.Shadow.Type = msoShadow14

Set MyShape = Nothing
Exit Sub '<-- if everything runs error-free, you will exit here
BEFORE the error handler.
ErrorHandler:
MsgBox "Sorry, an error occurred." & vbCrLf & "Number: " & Err.Numbe
& vbCrLf & "Description: " & Err.Description, vbCritical, "Error"
End Su
 
Hi
just a guess (not tested) but within the line
MyRange.Fill.ForeColor.SchemeColor = 43

you haven't defined MyRange. Maybe this line should be
Myshape.Fill.ForeColor.SchemeColor = 43
 
You have 2 things wrong here.

Firstly you have an extra .Text when setting the font size, and you use the
wrong object when setting the FillColour.

Sub AddDocumentation()
Dim MyShape As Object

On Error GoTo ErrorHandler

Set MyShape = ActiveSheet.Shapes.AddTextbox( _
msoTextOrientationHorizontal, 400, 200, 400, 200)

With MyShape
.TextFrame.Characters.Text = "<Text Box Please Type Text Here>"
.TextFrame.Characters.Font.Name = "Arial"
.TextFrame.Characters.Font.Size = 12

'the part below is what doesn't work
.Fill.ForeColor.SchemeColor = 43
.Fill.Visible = msoTrue
.Fill.Solid
.Shadow.Type = msoShadow14
End With

Set MyShape = Nothing
Exit Sub
ErrorHandler:
MsgBox "Sorry, an error occurred." & vbCrLf & "Number: " & _
Err.Number & vbCrLf & "Description: " & _
Err.Description, vbCritical, "Error"
End Sub

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Back
Top