Maintaining Text Box Font Setting (Excel) using VB
in [Word VBA]
|
From: golf4 on 21 May 2010 22:41 Hi, Everyone - I've run into this issue, and I can seem to figure it out. In my spreadsheet, I've included several text boxes at the bottom of the pages. The VB code I also have included is, when the pages are printed, alternately place a "P" in the text boxes. Then I formatted the text boxes to use WingDings II so that checkmarks SHOULD appear in the boxes. I've also included code that would (1) unprotect the sheet; (2) print the pages with the text boxes; and (3) reprotect the sheet. What keeps on happening is that, for some unknown reason (at least to me), the font in the text boxes keeps returning to Calibri before printing so all that shows in the boxes are "P"s. I would then go in, unprotect the sheet, change the font in the boxes and resave the workbook. The same thing will happen. What I'm wondering is whether there is a way I could modify my code so that the font setting, in the text boxes, would keep at WingDing II and not change? THANKS FOR THE CONTINUED HELP!!! Frank I've attached my code below: Sub PrintLRPHAmendment() 'Print completed PH Amendment' Sheet17.Activate With Sheet17 ..Unprotect "led52not" ..Shapes("Text Box 1").Select Selection.Characters.Text = "P" ..Shapes("Text Box 2").Select Selection.Characters.Text = "" ..Shapes("Text Box 3").Select Selection.Characters.Text = "" ..Shapes("Text Box 4").Select Selection.Characters.Text = "" ..PrintOut ..Shapes("Text Box 1").Select Selection.Characters.Text = "" ..Shapes("Text Box 2").Select Selection.Characters.Text = "P" ..PrintOut ..Shapes("Text Box 2").Select Selection.Characters.Text = "" ..Shapes("Text Box 3").Select Selection.Characters.Text = "P" ..PrintOut If Sheets("Data_Entry_Sheet").[H49] = "X" And Sheets("Data_Entry_Sheet").[k23] = "X" Then ..Shapes("Text Box 3").Select Selection.Characters.Text = "" ..Shapes("Text Box 4").Select Selection.Characters.Text = "P" ..PrintOut ..Protect "led52not" End If paperwarning = MsgBox("Insert your envelope now. Click YES to print your envelopes. Otherwise, click NO to Cancel.", vbYesNo, "Preparing to print envelopes for your LRPH Amendments") If paperwarning = 7 Then 'if user answers NO' Sheets("lrph_amendment").Protect ("led52not") ..Range("A12").Select Sheets("data_entry_sheet").Select [a1].Select ..Protect "led52not" 'Other protection/hiding code here' Exit Sub Else End If If paperwarning = 6 Then 'if answer is to print out envelopes Sheet24.PrintOut If Sheets("Data_Entry_Sheet").[H49] = "X" And Sheets("Data_Entry_Sheet").[k23] = "X" Then Sheet25.PrintOut End If ..Range("A12").Select Sheet2.Select [a1].Select ..Protect "led52not" End If End With End Sub
From: Doug Robbins - Word MVP on 21 May 2010 23:03 Forwarded to Microsoft.public.excel.programming. This group is for the use of VBA in Word. -- Hope this helps. Please reply to the newsgroup unless you wish to avail yourself of my services on a paid consulting basis. Doug Robbins - Word MVP, originally posted via msnews.microsoft.com "golf4" <golf4wff(a)hotmail.com> wrote in message news:4cef5384-3e48-415c-9ad1-2311d6efa734(a)t34g2000prd.googlegroups.com... > Hi, Everyone - > > I've run into this issue, and I can seem to figure it out. In my > spreadsheet, I've included several text boxes at the bottom of the > pages. The VB code I also have included is, when the pages are > printed, alternately place a "P" in the text boxes. Then I formatted > the text boxes to use WingDings II so that checkmarks SHOULD appear > in > the boxes. I've also included code that would (1) unprotect the > sheet; > (2) print the pages with the text boxes; and (3) reprotect the sheet. > What keeps on happening is that, for some unknown reason (at least to > me), the font in the text boxes keeps returning to Calibri before > printing so all that shows in the boxes are "P"s. I would then go in, > unprotect the sheet, change the font in the boxes and resave the > workbook. The same thing will happen. What I'm wondering is whether > there is a way I could modify my code so that the font setting, in > the > text boxes, would keep at WingDing II and not change? THANKS FOR THE > CONTINUED HELP!!! Frank > > > I've attached my code below: > > > Sub PrintLRPHAmendment() 'Print completed PH Amendment' > Sheet17.Activate > With Sheet17 > .Unprotect "led52not" > .Shapes("Text Box 1").Select > Selection.Characters.Text = "P" > .Shapes("Text Box 2").Select > Selection.Characters.Text = "" > .Shapes("Text Box 3").Select > Selection.Characters.Text = "" > .Shapes("Text Box 4").Select > Selection.Characters.Text = "" > .PrintOut > .Shapes("Text Box 1").Select > Selection.Characters.Text = "" > .Shapes("Text Box 2").Select > Selection.Characters.Text = "P" > .PrintOut > .Shapes("Text Box 2").Select > Selection.Characters.Text = "" > .Shapes("Text Box 3").Select > Selection.Characters.Text = "P" > .PrintOut > > > If Sheets("Data_Entry_Sheet").[H49] = "X" And > Sheets("Data_Entry_Sheet").[k23] = "X" Then > > > .Shapes("Text Box 3").Select > Selection.Characters.Text = "" > .Shapes("Text Box 4").Select > Selection.Characters.Text = "P" > .PrintOut > .Protect "led52not" > End If > > > paperwarning = MsgBox("Insert your envelope now. Click YES to print > your envelopes. Otherwise, click NO to Cancel.", vbYesNo, "Preparing > to print envelopes for your LRPH Amendments") > If paperwarning = 7 Then 'if user answers NO' > Sheets("lrph_amendment").Protect ("led52not") > .Range("A12").Select > Sheets("data_entry_sheet").Select > [a1].Select > .Protect "led52not" > 'Other protection/hiding code here' > Exit Sub > Else > End If > > > If paperwarning = 6 Then 'if answer is to print out envelopes > Sheet24.PrintOut > > > If Sheets("Data_Entry_Sheet").[H49] = "X" And > Sheets("Data_Entry_Sheet").[k23] = "X" Then > Sheet25.PrintOut > > > End If > .Range("A12").Select > Sheet2.Select > [a1].Select > .Protect "led52not" > End If > End With > End Sub
From: golf4 on 22 May 2010 14:51 On May 21, 8:03 pm, "Doug Robbins - Word MVP" <d...(a)REMOVECAPSmvps.org> wrote: > Forwarded to Microsoft.public.excel.programming. This group is for the use > of VBA in Word. > > -- > Hope this helps. > > Please reply to the newsgroup unless you wish to avail yourself of my > services on a paid consulting basis. > > Doug Robbins - Word MVP, originally posted via msnews.microsoft.com > > "golf4" <golf4...(a)hotmail.com> wrote in message > > news:4cef5384-3e48-415c-9ad1-2311d6efa734(a)t34g2000prd.googlegroups.com... > > > > > Hi, Everyone - > > > I've run into this issue, and I can seem to figure it out. In my > > spreadsheet, I've included several text boxes at the bottom of the > > pages. The VB code I also have included is, when the pages are > > printed, alternately place a "P" in the text boxes. Then I formatted > > the text boxes to use WingDings II so that checkmarks SHOULD appear > > in > > the boxes. I've also included code that would (1) unprotect the > > sheet; > > (2) print the pages with the text boxes; and (3) reprotect the sheet. > > What keeps on happening is that, for some unknown reason (at least to > > me), the font in the text boxes keeps returning to Calibri before > > printing so all that shows in the boxes are "P"s. I would then go in, > > unprotect the sheet, change the font in the boxes and resave the > > workbook. The same thing will happen. What I'm wondering is whether > > there is a way I could modify my code so that the font setting, in > > the > > text boxes, would keep at WingDing II and not change? THANKS FOR THE > > CONTINUED HELP!!! Frank > > > I've attached my code below: > > > Sub PrintLRPHAmendment() 'Print completed PH Amendment' > > Sheet17.Activate > > With Sheet17 > > .Unprotect "led52not" > > .Shapes("Text Box 1").Select > > Selection.Characters.Text = "P" > > .Shapes("Text Box 2").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 3").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 4").Select > > Selection.Characters.Text = "" > > .PrintOut > > .Shapes("Text Box 1").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 2").Select > > Selection.Characters.Text = "P" > > .PrintOut > > .Shapes("Text Box 2").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 3").Select > > Selection.Characters.Text = "P" > > .PrintOut > > > If Sheets("Data_Entry_Sheet").[H49] = "X" And > > Sheets("Data_Entry_Sheet").[k23] = "X" Then > > > .Shapes("Text Box 3").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 4").Select > > Selection.Characters.Text = "P" > > .PrintOut > > .Protect "led52not" > > End If > > > paperwarning = MsgBox("Insert your envelope now. Click YES to print > > your envelopes. Otherwise, click NO to Cancel.", vbYesNo, "Preparing > > to print envelopes for your LRPH Amendments") > > If paperwarning = 7 Then 'if user answers NO' > > Sheets("lrph_amendment").Protect ("led52not") > > .Range("A12").Select > > Sheets("data_entry_sheet").Select > > [a1].Select > > .Protect "led52not" > > 'Other protection/hiding code here' > > Exit Sub > > Else > > End If > > > If paperwarning = 6 Then 'if answer is to print out envelopes > > Sheet24.PrintOut > > > If Sheets("Data_Entry_Sheet").[H49] = "X" And > > Sheets("Data_Entry_Sheet").[k23] = "X" Then > > Sheet25.PrintOut > > > End If > > .Range("A12").Select > > Sheet2.Select > > [a1].Select > > .Protect "led52not" > > End If > > End With > > End Sub- Hide quoted text - > > - Show quoted text - Thank you............
From: golf4 on 22 May 2010 14:58 Just thought I'd try to bump my query once. Thanks "Doug Robbins - Word MVP" wrote: > Forwarded to Microsoft.public.excel.programming. This group is for the use > of VBA in Word. > > -- > Hope this helps. > > Please reply to the newsgroup unless you wish to avail yourself of my > services on a paid consulting basis. > > Doug Robbins - Word MVP, originally posted via msnews.microsoft.com > > "golf4" <golf4wff(a)hotmail.com> wrote in message > news:4cef5384-3e48-415c-9ad1-2311d6efa734(a)t34g2000prd.googlegroups.com... > > Hi, Everyone - > > > > I've run into this issue, and I can seem to figure it out. In my > > spreadsheet, I've included several text boxes at the bottom of the > > pages. The VB code I also have included is, when the pages are > > printed, alternately place a "P" in the text boxes. Then I formatted > > the text boxes to use WingDings II so that checkmarks SHOULD appear > > in > > the boxes. I've also included code that would (1) unprotect the > > sheet; > > (2) print the pages with the text boxes; and (3) reprotect the sheet. > > What keeps on happening is that, for some unknown reason (at least to > > me), the font in the text boxes keeps returning to Calibri before > > printing so all that shows in the boxes are "P"s. I would then go in, > > unprotect the sheet, change the font in the boxes and resave the > > workbook. The same thing will happen. What I'm wondering is whether > > there is a way I could modify my code so that the font setting, in > > the > > text boxes, would keep at WingDing II and not change? THANKS FOR THE > > CONTINUED HELP!!! Frank > > > > > > I've attached my code below: > > > > > > Sub PrintLRPHAmendment() 'Print completed PH Amendment' > > Sheet17.Activate > > With Sheet17 > > .Unprotect "led52not" > > .Shapes("Text Box 1").Select > > Selection.Characters.Text = "P" > > .Shapes("Text Box 2").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 3").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 4").Select > > Selection.Characters.Text = "" > > .PrintOut > > .Shapes("Text Box 1").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 2").Select > > Selection.Characters.Text = "P" > > .PrintOut > > .Shapes("Text Box 2").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 3").Select > > Selection.Characters.Text = "P" > > .PrintOut > > > > > > If Sheets("Data_Entry_Sheet").[H49] = "X" And > > Sheets("Data_Entry_Sheet").[k23] = "X" Then > > > > > > .Shapes("Text Box 3").Select > > Selection.Characters.Text = "" > > .Shapes("Text Box 4").Select > > Selection.Characters.Text = "P" > > .PrintOut > > .Protect "led52not" > > End If > > > > > > paperwarning = MsgBox("Insert your envelope now. Click YES to print > > your envelopes. Otherwise, click NO to Cancel.", vbYesNo, "Preparing > > to print envelopes for your LRPH Amendments") > > If paperwarning = 7 Then 'if user answers NO' > > Sheets("lrph_amendment").Protect ("led52not") > > .Range("A12").Select > > Sheets("data_entry_sheet").Select > > [a1].Select > > .Protect "led52not" > > 'Other protection/hiding code here' > > Exit Sub > > Else > > End If > > > > > > If paperwarning = 6 Then 'if answer is to print out envelopes > > Sheet24.PrintOut > > > > > > If Sheets("Data_Entry_Sheet").[H49] = "X" And > > Sheets("Data_Entry_Sheet").[k23] = "X" Then > > Sheet25.PrintOut > > > > > > End If > > .Range("A12").Select > > Sheet2.Select > > [a1].Select > > .Protect "led52not" > > End If > > End With > > End Sub > > . >
From: Peter T on 23 May 2010 07:05 Be sure to look in the .excel.programming group to see any replies you got there but not necessarily sent to the word group Regards, Peter T "golf4" <golf4(a)discussions.microsoft.com> wrote in message news:EC275757-5F8F-4A2D-8019-DE75B13BF609(a)microsoft.com... > Just thought I'd try to bump my query once. Thanks > > "Doug Robbins - Word MVP" wrote: > >> Forwarded to Microsoft.public.excel.programming. This group is for the >> use >> of VBA in Word. >> >> -- >> Hope this helps. >> >> Please reply to the newsgroup unless you wish to avail yourself of my >> services on a paid consulting basis. >> >> Doug Robbins - Word MVP, originally posted via msnews.microsoft.com >> >> "golf4" <golf4wff(a)hotmail.com> wrote in message >> news:4cef5384-3e48-415c-9ad1-2311d6efa734(a)t34g2000prd.googlegroups.com... >> > Hi, Everyone - >> > >> > I've run into this issue, and I can seem to figure it out. In my >> > spreadsheet, I've included several text boxes at the bottom of the >> > pages. The VB code I also have included is, when the pages are >> > printed, alternately place a "P" in the text boxes. Then I formatted >> > the text boxes to use WingDings II so that checkmarks SHOULD appear >> > in >> > the boxes. I've also included code that would (1) unprotect the >> > sheet; >> > (2) print the pages with the text boxes; and (3) reprotect the sheet. >> > What keeps on happening is that, for some unknown reason (at least to >> > me), the font in the text boxes keeps returning to Calibri before >> > printing so all that shows in the boxes are "P"s. I would then go in, >> > unprotect the sheet, change the font in the boxes and resave the >> > workbook. The same thing will happen. What I'm wondering is whether >> > there is a way I could modify my code so that the font setting, in >> > the >> > text boxes, would keep at WingDing II and not change? THANKS FOR THE >> > CONTINUED HELP!!! Frank >> > >> > >> > I've attached my code below: >> > >> > >> > Sub PrintLRPHAmendment() 'Print completed PH Amendment' >> > Sheet17.Activate >> > With Sheet17 >> > .Unprotect "led52not" >> > .Shapes("Text Box 1").Select >> > Selection.Characters.Text = "P" >> > .Shapes("Text Box 2").Select >> > Selection.Characters.Text = "" >> > .Shapes("Text Box 3").Select >> > Selection.Characters.Text = "" >> > .Shapes("Text Box 4").Select >> > Selection.Characters.Text = "" >> > .PrintOut >> > .Shapes("Text Box 1").Select >> > Selection.Characters.Text = "" >> > .Shapes("Text Box 2").Select >> > Selection.Characters.Text = "P" >> > .PrintOut >> > .Shapes("Text Box 2").Select >> > Selection.Characters.Text = "" >> > .Shapes("Text Box 3").Select >> > Selection.Characters.Text = "P" >> > .PrintOut >> > >> > >> > If Sheets("Data_Entry_Sheet").[H49] = "X" And >> > Sheets("Data_Entry_Sheet").[k23] = "X" Then >> > >> > >> > .Shapes("Text Box 3").Select >> > Selection.Characters.Text = "" >> > .Shapes("Text Box 4").Select >> > Selection.Characters.Text = "P" >> > .PrintOut >> > .Protect "led52not" >> > End If >> > >> > >> > paperwarning = MsgBox("Insert your envelope now. Click YES to print >> > your envelopes. Otherwise, click NO to Cancel.", vbYesNo, "Preparing >> > to print envelopes for your LRPH Amendments") >> > If paperwarning = 7 Then 'if user answers NO' >> > Sheets("lrph_amendment").Protect ("led52not") >> > .Range("A12").Select >> > Sheets("data_entry_sheet").Select >> > [a1].Select >> > .Protect "led52not" >> > 'Other protection/hiding code here' >> > Exit Sub >> > Else >> > End If >> > >> > >> > If paperwarning = 6 Then 'if answer is to print out envelopes >> > Sheet24.PrintOut >> > >> > >> > If Sheets("Data_Entry_Sheet").[H49] = "X" And >> > Sheets("Data_Entry_Sheet").[k23] = "X" Then >> > Sheet25.PrintOut >> > >> > >> > End If >> > .Range("A12").Select >> > Sheet2.Select >> > [a1].Select >> > .Protect "led52not" >> > End If >> > End With >> > End Sub >> >> . >>
|
Pages: 1 Prev: Can typing macros be used in Word? Next: Snel geld verdienen |