Maintaining Text Box Font Setting through VB

G

golf4

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
 
G

golf4

Will the printer accept Wingdings?




Hi, Everyone -
Click to expand...
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
Click to expand...
I've attached my code below:
Click to expand...
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
Click to expand...
If Sheets("Data_Entry_Sheet").[H49] = "X" And
Sheets("Data_Entry_Sheet").[k23] = "X" Then
Click to expand...
.Shapes("Text Box 3").Select
Selection.Characters.Text = ""
.Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
.PrintOut
.Protect "led52not"
End If
Click to expand...
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
Click to expand...
If paperwarning = 6 Then 'if answer is to print out envelopes
Sheet24.PrintOut
Click to expand...
If Sheets("Data_Entry_Sheet").[H49] = "X" And
Sheets("Data_Entry_Sheet").[k23] = "X" Then
Sheet25.PrintOut
Click to expand...
End If
.Range("A12").Select
Sheet2.Select
[a1].Select
.Protect "led52not"
End If
End With
End Sub- Hide quoted text -
Click to expand...

- Show quoted text -
Click to expand...

Thanks for the response, WingDings does work on the machines and other
sheets within the same workbook. Strange, and can't seem to figure
this ojne out......
 
J

Jim Cone

Worksheets("Sheet17").Unprotect "led52not"
With Worksheets("Sheet17") .Shapes("Text Box 2").TextFrame.Characters
.Text = "P"
.Font.Name = "Wingdings 2"
End With
--
Jim Cone
Portland, Oregon USA
http://www.contextures.com/excel-sort-addin.html





"golf4" <[email protected]>
wrote in message
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:
-snip-
 

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

Maintaining Text Box Font Setting (Excel) using VB 4
Problem with IF Statements Within VBA Code 3
Changing the test of checkboxes (shapes) programmatically? 4
Clearing combo boxes and a text box 2
Query on Control Source in Userform Textbox 4
Link a text box based on drop down box with four options MS Word 365 0
VBA Grouping Rectangle/Text Boxes 5
Align text in a text box 2

Top