 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / December 2006Naming a text box in VBA
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, > [quoted text clipped - 11 lines] > > DonH  SignatureDave Peterson Thanks Dave but I'm getting a runtime error 13 type mistmatch when I try that DonH > Dim myTB as textbox > set mytb = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 8#, 40#, [quoted text clipped - 24 lines] > > > > DonH Sorry, try dimming myTB as a shape: Dim myTB As Shape > Thanks Dave but I'm getting a runtime error 13 type mistmatch when I > try that [quoted text clipped - 33 lines] > > > > Dave Peterson SignatureDave Peterson 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 > Sorry, try dimming myTB as a shape: > [quoted text clipped - 37 lines] > > > > > > Dave Peterson OK got that sorted works now I played a bit more... > Hi Dave, > [quoted text clipped - 76 lines] > > > > > > > > Dave Peterson 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"...) > OK got that sorted works now I played a bit more... > [quoted text clipped - 82 lines] > > > > > > Dave Peterson SignatureDave Peterson 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 > Did it look like this? > [quoted text clipped - 115 lines] > > > > > > > > Dave Peterson 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 :-) > [quoted text clipped - 139 lines] > > > > Dave Peterson SignatureDave Peterson 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 > If you need help creating a PDF file, you should post what program you use. You > may get better answers with more information. [quoted text clipped - 159 lines] > > > > > > Dave Peterson 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, > [quoted text clipped - 180 lines] > > > > Dave Peterson SignatureDave Peterson 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 > Maybe you can add the text in chunks. I used chunks of 250 (something less than > 255). [quoted text clipped - 216 lines] > > > > > > Dave Peterson 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, > [quoted text clipped - 229 lines] > > > > Dave Peterson SignatureDave Peterson >I don't know. I used distiller a couple of times a few years ago, but that was >all manual. [quoted text clipped - 7 lines] >> I have been into work and they use distiller for saving their PDF's. >> Can this be automated? <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. SignatureZilbandy > Must you quote hundreds of useless lines of post for a two line reply? > Please trim your posts. :/ The same goes for DonH, too. 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 SignatureHTH Nick Hodge Microsoft MVP - Excel Southampton, England nick_hodgeTAKETHISOUT@zen.co.ukANDTHIS www.nickhodge.co.uk >> Must you quote hundreds of useless lines of post for a two line reply? >> Please trim your posts. :/ The same goes for DonH, too. > > Sorry hadn't realised. > > DonH 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>. > >I don't know. I used distiller a couple of times a few years ago, but that was > >all manual. [quoted text clipped - 15 lines] > -- > Zilbandy SignatureDave Peterson >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>. 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. :) SignatureZilbandy Thanks anyway Dave! > 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??? Thanks Dave but I'm getting a runtime error 13 type mistmatch when I try that DonH > Dim myTB as textbox > set mytb = ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 8#, 40#, [quoted text clipped - 24 lines] > > > > DonH
|
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.