Maintaining Text Box Font Setting (Excel) using 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
 
D

Doug Robbins - Word MVP

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 said:
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
Click to expand...
 
G

golf4

Forwarded to Microsoft.public.excel.programming.  This group is for theuse
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




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...

Thank you............
 
G

golf4

Just thought I'd try to bump my query once. Thanks

Doug Robbins - Word MVP said:
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 said:
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
Click to expand...

.
Click to expand...
 
P

Peter T

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 said:
Just thought I'd try to bump my query once. Thanks

Doug Robbins - Word MVP said:
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 said:
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
Click to expand...

.
Click to expand...
Click to expand...
 

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 through VB 4
Problem with IF Statements Within VBA Code 3
Query on Control Source in Userform Textbox 4
PowerPoint Change font for multiple text boxes 0
Using VB to Convert Single Worksheet to PDF then Email? 1
How to add text box with macro attached 1
Find each text box 1
TextBox Font 1

Top