Maintaining Text Box Font Setting (Excel) using VB
in [Programming]
|
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: Doug Robbins - Word MVP on 22 May 2010 17:54 That achieves nothing, but if you must do, you should not send it to the wrong newsgroup as well. -- 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)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: watermark Next: Entry data sheet automaticlly updates or adds data to differant sh |