VBA problem - text boxes

  • Thread starter Thread starter carljonesuk
  • Start date Start date
C

carljonesuk

How can i get round the problem that when i get a macro to make a tex
box and assign a macro to it (In which the text box is deleted aswel
as returning row heights to their original height) the text box numbe
is incremented so I can't reselect the text box after i have deselecte
it to delete it?

Any suggestionswould be useful!

Thanks

Carl Jone
 
If you are adding a textbox, it is a new textbox. You just need to assign a
name to it rather than keeping the default name. Show your code for adding
the textbox and I can tell you how to rename it. (also, what version of
Excel you will be using)
 
Im using Excel 2000. I also want to assign a macro to the text box.
know how to do this!! This is the code for the macro:-

Sheets("Quote").Select
Application.CommandBars("Drawing").Visible = True
ActiveSheet.Shapes.AddTextbox(msoTextOrientationDownward, 339.75
130.5, _
248.25, 149.25).Select
Selection.Characters.Text = "Back To Normal"
With Selection.Characters(Start:=1, Length:=14).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 14
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = xlHorizontal
.AutoSize = False
End With
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 40
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Range("G22").Selec
 
The below code always gives it the name Text Box 2 (change to fit your
needs).

Sub Tester2()
Sheets("Quote").Select
Application.CommandBars("Drawing").Visible = True

With ActiveSheet.Shapes.AddTextbox( _
msoTextOrientationDownward, _
339.75, _
130.5, _
248.25, _
149.25)

' Name the Textbox

.Name = "Text box 2"
.Select
End With
Selection.Characters.Text = "Back To Normal"
With Selection.Characters(Start:=1, Length:=14).Font
Name = "Arial"
FontStyle = "Bold"
Size = 14
Strikethrough = False
Superscript = False
Subscript = False
OutlineFont = False
Shadow = False
Underline = xlUnderlineStyleNone
ColorIndex = xlAutomatic
End With
With Selection
HorizontalAlignment = xlCenter
VerticalAlignment = xlCenter
Orientation = xlHorizontal
AutoSize = False
End With
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 40
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 64
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Range("G22").Select

End Sub
 
To refer to the textbox, you use the name of the textbox. When you add it,
you give it a specific name, so it always has the same name. That really is
the point of having a name isn't it.
 
Thanks It worked!

Look Forward to getting more of my problems!!!!

Thanks again

Carl Jone
 
Back
Top