Naming a text box in VBA

  • Thread starter Thread starter donh
  • Start date Start date
D

donh

Hi,

I'm using the following to create a text box and have added text
needed.

ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 8#, 40#, _
820#, 140#).Select


The problem I have is that although I am able to create a box using VBA
I'm stuck on how to delete with VBA as the text box doesn't have a name
to refer to and I don't know how to assign one.

Thanks in anticipation

DonH
 
Dim myTB as textbox
set mytb = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 8#, 40#,
_
820#, 140#)
with myTB
.name = "TB_" & .topleftcell.address(0,0)
end with

(Sometimes, I like to include the cell location in the name to make it
unique--but you don't have to.)

mytb.name = "whateveryouwanthere"
 
Hi Dave,

Sorry still not working, I've tried remming out various bits to get it
to work but just keep hitting errors The nearest Ive got is a blank
text box with the test appearing in A1.

If Dave isn't around over weekend if anyone else has a solution I would
love to hear from you.

Code I'm using is as follows

Sub PDFPrint()
'
Dim myTB As Shape
Set myTB = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal,
8#, 40#, _
820#, 140#)
With myTB
.Name = "TB_" & .TopLeftCell.Address(0, 0)
End With
Call Unl
Rows("3:3").RowHeight = 176.25

myTB.Name = "PDFBannerPrint"

Selection.Characters.Text = "Abbreviation List"

With Selection.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 13
End With

End Sub

Many thanks

DonH
 
OK got that sorted works now I played a bit more...




Hi Dave,

Sorry still not working, I've tried remming out various bits to get it
to work but just keep hitting errors The nearest Ive got is a blank
text box with the test appearing in A1.

If Dave isn't around over weekend if anyone else has a solution I would
love to hear from you.

Code I'm using is as follows

Sub PDFPrint()
'
Dim myTB As Shape
Set myTB = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal,
8#, 40#, _
820#, 140#)
With myTB
.Name = "TB_" & .TopLeftCell.Address(0, 0)
End With
Call Unl
Rows("3:3").RowHeight = 176.25

myTB.Name = "PDFBannerPrint"

Selection.Characters.Text = "Abbreviation List"

With Selection.Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 13
End With

End Sub

Many thanks

DonH
Click to expand...
 
Did it look like this?

Option Explicit
Sub PDFPrint()
Dim myTB As Shape

'try to delete the existing shape named PDFBannerPrint
'just in case it exists
On Error Resume Next
ActiveSheet.Shapes("PDFBannerPrint").Delete
On Error GoTo 0

Set myTB = ActiveSheet.Shapes.AddTextbox _
(msoTextOrientationHorizontal, _
8#, 40#, 820#, 140#)

With myTB
.Name = "PDFBannerPrint"
With .OLEFormat.Object
.Text = "Abbreviation List"
With .Characters(Start:=1, Length:=17).Font
.Name = "Arial"
.FontStyle = "Bold"
.Size = 13
End With
End With
End With

End Sub

(Actually, I didn't see your followup post until I was ready to post this--so I
thought "what the heck"...)
 
No Dave it didn't :-)

Sorry but this is very early days for me with VBA. I have a book but
unless a task fits exactly with what I'm trying to do I get lost very
easily.

I am at the end of a long project and perhaps it would be better to say
what I want to achieve in the end in the hope you/someone can help.

My last task is to Insert a text box with abbreviations at the top of
my worksheet, set a print area and send/print it to a PDF file and of
course remove the text box once done. The PDF part would of been my
next step. I recently saw a post on saving a file based on cell
contents and was hoping that I could cobble all the bits together but I
am out of my depth.

Any help would be appreciated.

Don
 
If you need help creating a PDF file, you should post what program you use. You
may get better answers with more information.

As for saving the workbook, maybe something like:

With ActiveWorkbook
.SaveAs Filename:=.Worksheets("sheet999").Range("x99").Value & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End With

Change the sheetname, cell address and include the path if you need to:

With ActiveWorkbook
.SaveAs Filename:="C:\somefoldernamehere\" _
& ".Worksheets("sheet999").Range("x99").Value & ".xls", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
End With

No Dave it didn't :-)

Sorry but this is very early days for me with VBA. I have a book but
unless a task fits exactly with what I'm trying to do I get lost very
easily.

I am at the end of a long project and perhaps it would be better to say
what I want to achieve in the end in the hope you/someone can help.

My last task is to Insert a text box with abbreviations at the top of
my worksheet, set a print area and send/print it to a PDF file and of
course remove the text box once done. The PDF part would of been my
next step. I recently saw a post on saving a file based on cell
contents and was hoping that I could cobble all the bits together but I
am out of my depth.

Any help would be appreciated.

Don
Click to expand...
 
Dave,

Thanks. Its difficult getting the balance between waffle and info.

I think the PDF writer is Adobe Distiller? but I'll check when I go
into work.

Anoher problem I've hit though is that there seems to be a limit of 250
charactors to the text box. My abbreviation list requires around 400
(most of which are spaces to format the text)

Any suggestions on how to get around this limitation.


Many thanks

DonH
 
Maybe you can add the text in chunks. I used chunks of 250 (something less than
255).

Option Explicit
Sub PDFPrint()
Dim myTB As Shape
Dim iCtr As Long
Dim myStr As String
Dim mySubStr As String

'a long string
myStr = Application.Rept("asdf ", 500)

'try to delete the existing shape named PDFBannerPrint
'just in case it exists
On Error Resume Next
ActiveSheet.Shapes("PDFBannerPrint").Delete
On Error GoTo 0

Set myTB = ActiveSheet.Shapes.AddTextbox _
(msoTextOrientationHorizontal, _
8#, 40#, 820#, 140#)

'you'll have to add back the formatting that you want...
With myTB
.Name = "PDFBannerPrint"
iCtr = 1
Do While iCtr < Len(myStr)
mySubStr = Mid(myStr, iCtr, 250)
.TextFrame.Characters(iCtr).Insert String:=mySubStr
iCtr = iCtr + 250
Loop
End With

End Sub
Dave,

Thanks. Its difficult getting the balance between waffle and info.

I think the PDF writer is Adobe Distiller? but I'll check when I go
into work.

Anoher problem I've hit though is that there seems to be a limit of 250
charactors to the text box. My abbreviation list requires around 400
(most of which are spaces to format the text)

Any suggestions on how to get around this limitation.

Many thanks

DonH
Click to expand...
 
Dave,

Many thanks I've now got that in place and working.

I have been into work and they use distiller for saving their PDF's.
Can this be automated?


DonH
 
I don't know. I used distiller a couple of times a few years ago, but that was
all manual.

Maybe someone will jump in--or maybe you can find something on google???
Dave,

Many thanks I've now got that in place and working.

I have been into work and they use distiller for saving their PDF's.
Can this be automated?

DonH
Click to expand...
 
I don't know. I used distiller a couple of times a few years ago, but that was
all manual.

Maybe someone will jump in--or maybe you can find something on google???
Click to expand...

<250 plus line snipped>

Must you quote hundreds of useless lines of post for a two line reply?
Please trim your posts. :/ The same goes for DonH, too.
 
Zilbandy said:
Must you quote hundreds of useless lines of post for a two line reply?
Please trim your posts. :/ The same goes for DonH, too.
Click to expand...

Sorry hadn't realised.

DonH
 
(My Opinion only)

On the contrary, I like, when answering posts. (Most of my activity) to have
the whole post to hand. I refuse to answer trimmed posts or those from
mirror sites who plagiarize my answers without permission. (It takes far
more effort and/or a change of setting in my newsreader

This is an issue on dial-up but a text post with hundreds of threads rarely
exceeds a hundred kb

--
HTH
Nick Hodge
Microsoft MVP - Excel
Southampton, England
(e-mail address removed)
www.nickhodge.co.uk
 
For the most part, I refrain from snipping (for the same reason Nick
suggested)--unless it seems to make sense. And in this case, it probably would
have been a good idea--especially since the subject changed from the original
post.

But one of the good things about top posting is that you (or anyone else) can
read replies without going through the whole thread.

And if you've noticed, the posts in ms excel's public newsgroups are
overwhelmingly top posted. Yes, this differs from the standard(?) usenet rules.

But I guess I'm wondering why you quoted any of the previous posts since none of
it was germane to your point <bg>.
 
And if you've noticed, the posts in ms excel's public newsgroups are
overwhelmingly top posted. Yes, this differs from the standard(?) usenet rules.

But I guess I'm wondering why you quoted any of the previous posts since none of
it was germane to your point <bg>.
Click to expand...

Ooooooh, you got me! ::fallen down dead:: :) I was having a senior
moment when I posted that. I do that occasionally, but the doctor says
I'm getting much better. :)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Text into a VBA Text Box 6
Importing text into a Text Box 2
Control Source Property 2
Display cell contents in a text box (shape) and linking 2
Dates American English? 2
How to add text box with macro attached 1
VBA code to "skip" empty rows 5
import text to Text box 7

Back
Top