Query on Control Source in Userform Textbox

[golf4]

Hi, everybody -

BOY O' BOY --- I continue to improve our agency's Income Calculation
Tool with the help of numerous people here. I have a slight problem in
one of my VB codings. My particular problem is related to the portion
of the code that is seperated and in brackets. I have created a
specific userform that includes a number of textboxes where the user
enter data that feeds over to the spreadsheet's Data Entry Sheet. The
Data Entry Sheet then calculates the family's income and rent whish
then feeds over to all of the applicable program letters and
worksheets.

Within the userform, I have a data entry textbox with the Control
Source set to enter the data entered into the textbox to feed into the
field Data_Entry_Sheet!B75. What's supposed to happen is: if
Data_Entry_Sheet!b75 is populated, via the userform's textbox,
Worksheet 8 should be printed out - if the field is empty, Worksheet 8
should not be printed. WHAT IS HAPPENING --- everytime I run the code,
even when Data_Entry_Sheet!b75 is empty, the worksheet prints out!!!

Sorry for the extended query. I'm hoping one of the experts here can
give me a clue why this is happening and how I can fix the code.

CODE BELOW:

Sub PrintTTPWorksheet() 'Print S8 Annuals and Interims'
PaperWarning = ktMsgBox("PRIOR TO PRINTING ANY WORKSHEETS:" & vbCrLf &
"Please verify whether any houshold members qualify" & vbCrLf & "for
the Earned Income Disregard. If you have verified all" & vbCrLf &
"household members, press YES to continue the printing" & vbCrLf &
"process." & vbCrLf & "If you need to enter additional earned income
disregard" & vbCrLf & "data, press NO and enter the data." _
, vbYesNo + vbCritical, "REMEMBER TO CHECK ALL HOUSEHOLD MEMBERS FOR
FOR MEID" _
, PromptColor:=vbRed _
, BackColor:=&HFFCC00 _
, Chime:=True _
, FontName:="TAHOMA" _
, FontSize:=20 _
, WaitSecond:=0 _
, ChimeColor:=&H808000)

If PaperWarning = 6 Then
[A1].Select
Sheet3.Select
Sheet3.PrintOut
Sheet3.Select
Sheet3.PrintOut
Sheet1.Select
Sheet1.PrintOut
Sheet4.Select
Sheet4.PrintOut
Sheet14.Activate
With Sheet14
..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
..Shapes("Text Box 3").Select
Selection.Characters.Text = ""
..Shapes("Text Box 4").Select
Selection.Characters.Text = "P"
..PrintOut
..Shapes("Text Box 4").Select
Selection.Characters.Text = ""
..Range("A12").Select
Sheet2.Select


[[[If ("Data_Entry_Sheet!B75") >"" Then
Sheet8.PrintOut]]]


PaperWarning = ktMsgBox("Insert your envelopes now. Click YES to print
your envelopes." & vbCrLf & "Otherwise, click NO to Cancel." _
, vbYesNo + vbCritical, "PREPARING TO PRINT YOUR ENVELOPES FOR SECTION
8 AMENDMENTS" _
, PromptColor:=vbRed _
, BackColor:=&HFFCC00 _
, Chime:=False _
, FontName:="TAHOMA" _
, FontSize:=16 _
, WaitSecond:=0 _
, ChimeColor:=&H808000)
If PaperWarning = 6 Then
Sheet24.PrintOut
Sheet25.PrintOut
PaperWarning = ktMsgBox("If this is a MOVE, check MLS to see if there
is already an Alternate Address entered. Click YES if there is no
Alternate Address to remove. Otherwise, click NO and remove the
Alternate Address." _
, vbYesNo + vbCritical, "CHECK MLS FOR AN ALTERNATE ADDRESS" _
, PromptColor:=vbRed _
, BackColor:=&HFFCC00 _
, Chime:=True _
, FontName:="TAHOMA" _
, FontSize:=16 _
, WaitSecond:=10 _
, ChimeColor:=&H808000)
If PaperWarning = 6 Then
[A1].Select
..Protect "led52not"
End If
End If
End If
End With
End If
End Sub

THANKS FOR ALL THE HELP!!!!!

Golf

 

[Jake Marx]

Hi Golf,

[[[If ("Data_Entry_Sheet!B75") >"" Then
Sheet8.PrintOut]]]

You forgot to tell VBA that you wanted to deal with a Range. So your code
is comparing the string "Data_Entry_Sheet!B75" with an empty string (""),
and the previous will always be greater than the latter.

Instead, you could use:

If Len(Sheets("Data_Entry_Sheet").Range("B75").Value) Then

This will force a printout only if cell B75 on Data_Entry_Sheet has a value.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi, everybody -

BOY O' BOY --- I continue to improve our agency's Income Calculation
Tool with the help of numerous people here. I have a slight problem in
one of my VB codings. My particular problem is related to the portion
of the code that is seperated and in brackets. I have created a
specific userform that includes a number of textboxes where the user
enter data that feeds over to the spreadsheet's Data Entry Sheet. The
Data Entry Sheet then calculates the family's income and rent whish
then feeds over to all of the applicable program letters and
worksheets.

Within the userform, I have a data entry textbox with the Control
Source set to enter the data entered into the textbox to feed into the
field Data_Entry_Sheet!B75. What's supposed to happen is: if
Data_Entry_Sheet!b75 is populated, via the userform's textbox,
Worksheet 8 should be printed out - if the field is empty, Worksheet 8
should not be printed. WHAT IS HAPPENING --- everytime I run the code,
even when Data_Entry_Sheet!b75 is empty, the worksheet prints out!!!

Sorry for the extended query. I'm hoping one of the experts here can
give me a clue why this is happening and how I can fix the code.

CODE BELOW:

[[[If ("Data_Entry_Sheet!B75") >"" Then
Sheet8.PrintOut]]]

<snip>

 

[golf4]

Hi, Jake -
Thanks so much for your response. Your code suggestion works like a
charm.

I was hoping, as long as I had you on the line, to ask a follow-up
related question. When I originally designed our Income Calculation
Tool, users would enter data directly into fields in the Data Entry
Sheet. Because the Data Entry Sheet contains 100s of formulas, command
buttons, etc., I have both the specific cells and the sheet password
protected. Since I now have users using userform textboxes to enter
data into the Data Entry Sheet, I'm finding that when I protect the
cells that are tied together with the specific textbox (Control
Source), I receive the error message "Exception Occured". I'm
wondering whether there is a way to resolve this while still
protecting the data entry method via userform textboxes?

Thanks again for the help,

Golf

Hi Golf,

[[[If ("Data_Entry_Sheet!B75") >"" Then
Sheet8.PrintOut]]]

You forgot to tell VBA that you wanted to deal with a Range. So your code
is comparing the string "Data_Entry_Sheet!B75" with an empty string (""),
and the previous will always be greater than the latter.

Instead, you could use:

If Len(Sheets("Data_Entry_Sheet").Range("B75").Value) Then

This will force a printout only if cell B75 on Data_Entry_Sheet has a value.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi, everybody -

BOY O' BOY --- I continue to improve our agency's Income Calculation
Tool with the help of numerous people here. I have a slight problem in
one of my VB codings. My particular problem is related to the portion
of the code that is seperated and in brackets. I have created a
specific userform that includes a number of textboxes where the user
enter data that feeds over to the spreadsheet's Data Entry Sheet. The
Data Entry Sheet then calculates the family's income and rent whish
then feeds over to all of the applicable program letters and
worksheets.

Within the userform, I have a data entry textbox with the Control
Source set to enter the data entered into the textbox to feed into the
field Data_Entry_Sheet!B75. What's supposed to happen is: if
Data_Entry_Sheet!b75 is populated, via the userform's textbox,
Worksheet 8 should be printed out - if the field is empty, Worksheet 8
should not be printed. WHAT IS HAPPENING --- everytime I run the code,
even when Data_Entry_Sheet!b75 is empty, the worksheet prints out!!!

Sorry for the extended query. I'm hoping one of the experts here can
give me a clue why this is happening and how I can fix the code.

CODE BELOW:

[[[If ("Data_Entry_Sheet!B75") >"" Then
Sheet8.PrintOut]]]

<snip>

 

[Jake Marx]

Hi Golf,

Excel gives you a way of protecting a worksheet's contents from direct user
input while leaving it open to programmatic change. Just set the
UserInterfaceOnly argument of the Protect method to True:

Sheets("Sheet1").Protect Password:="****", UserInterfaceOnly:=True

This will allow your UserForm to modify the cells (via the ControlSource
property) but will stop users from changing the values directly.

This statement only allows programmatic changes during the current session
of Excel. Once you close and reopen the workbook, it will be
fully-protected. So in order to have this work all the time, you'll have to
use that statement each time the workbook is opened. You can put that line
of code in the Workbook_Open event routine. Since you have to put the
password in your code, you should also protect your VBA Project with a
password (via Tools | VBAProject Properties in the VBE).

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi, Jake -
Thanks so much for your response. Your code suggestion works like a
charm.

I was hoping, as long as I had you on the line, to ask a follow-up
related question. When I originally designed our Income Calculation
Tool, users would enter data directly into fields in the Data Entry
Sheet. Because the Data Entry Sheet contains 100s of formulas, command
buttons, etc., I have both the specific cells and the sheet password
protected. Since I now have users using userform textboxes to enter
data into the Data Entry Sheet, I'm finding that when I protect the
cells that are tied together with the specific textbox (Control
Source), I receive the error message "Exception Occured". I'm
wondering whether there is a way to resolve this while still
protecting the data entry method via userform textboxes?

Thanks again for the help,

Golf

Hi Golf,

[[[If ("Data_Entry_Sheet!B75") >"" Then
Sheet8.PrintOut]]]

You forgot to tell VBA that you wanted to deal with a Range. So
your code is comparing the string "Data_Entry_Sheet!B75" with an
empty string (""), and the previous will always be greater than the
latter.

Instead, you could use:

If Len(Sheets("Data_Entry_Sheet").Range("B75").Value) Then

This will force a printout only if cell B75 on Data_Entry_Sheet has
a value.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi, everybody -

BOY O' BOY --- I continue to improve our agency's Income Calculation
Tool with the help of numerous people here. I have a slight problem
in one of my VB codings. My particular problem is related to the
portion of the code that is seperated and in brackets. I have
created a specific userform that includes a number of textboxes
where the user enter data that feeds over to the spreadsheet's Data
Entry Sheet. The Data Entry Sheet then calculates the family's
income and rent whish then feeds over to all of the applicable
program letters and worksheets.

Within the userform, I have a data entry textbox with the Control
Source set to enter the data entered into the textbox to feed into
the field Data_Entry_Sheet!B75. What's supposed to happen is: if
Data_Entry_Sheet!b75 is populated, via the userform's textbox,
Worksheet 8 should be printed out - if the field is empty,
Worksheet 8 should not be printed. WHAT IS HAPPENING --- everytime
I run the code, even when Data_Entry_Sheet!b75 is empty, the
worksheet prints out!!!

Sorry for the extended query. I'm hoping one of the experts here can
give me a clue why this is happening and how I can fix the code.

CODE BELOW:

[[[If ("Data_Entry_Sheet!B75") >"" Then
Sheet8.PrintOut]]]

<snip>

 

[golf4]

Hi, Jake -

Thanks again for the assistance. I'll give your suggestion(s) a shot
when I get home.

Thanks again,

Golf

Hi Golf,

Excel gives you a way of protecting a worksheet's contents from direct user
input while leaving it open to programmatic change. Just set the
UserInterfaceOnly argument of the Protect method to True:

Sheets("Sheet1").Protect Password:="****", UserInterfaceOnly:=True

This will allow your UserForm to modify the cells (via the ControlSource
property) but will stop users from changing the values directly.

This statement only allows programmatic changes during the current session
of Excel. Once you close and reopen the workbook, it will be
fully-protected. So in order to have this work all the time, you'll have to
use that statement each time the workbook is opened. You can put that line
of code in the Workbook_Open event routine. Since you have to put the
password in your code, you should also protect your VBA Project with a
password (via Tools | VBAProject Properties in the VBE).

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

Hi, Jake -
Thanks so much for your response. Your code suggestion works like a
charm.

I was hoping, as long as I had you on the line, to ask a follow-up
related question. When I originally designed our Income Calculation
Tool, users would enter data directly into fields in the Data Entry
Sheet. Because the Data Entry Sheet contains 100s of formulas, command
buttons, etc., I have both the specific cells and the sheet password
protected. Since I now have users using userform textboxes to enter
data into the Data Entry Sheet, I'm finding that when I protect the
cells that are tied together with the specific textbox (Control
Source), I receive the error message "Exception Occured". I'm
wondering whether there is a way to resolve this while still
protecting the data entry method via userform textboxes?

Thanks again for the help,

Golf

Hi Golf,

[[[If ("Data_Entry_Sheet!B75") >"" Then
Sheet8.PrintOut]]]

You forgot to tell VBA that you wanted to deal with a Range. So
your code is comparing the string "Data_Entry_Sheet!B75" with an
empty string (""), and the previous will always be greater than the
latter.

Instead, you could use:

If Len(Sheets("Data_Entry_Sheet").Range("B75").Value) Then

This will force a printout only if cell B75 on Data_Entry_Sheet has
a value.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


golf4 wrote:
Hi, everybody -

BOY O' BOY --- I continue to improve our agency's Income Calculation
Tool with the help of numerous people here. I have a slight problem
in one of my VB codings. My particular problem is related to the
portion of the code that is seperated and in brackets. I have
created a specific userform that includes a number of textboxes
where the user enter data that feeds over to the spreadsheet's Data
Entry Sheet. The Data Entry Sheet then calculates the family's
income and rent whish then feeds over to all of the applicable
program letters and worksheets.

Within the userform, I have a data entry textbox with the Control
Source set to enter the data entered into the textbox to feed into
the field Data_Entry_Sheet!B75. What's supposed to happen is: if
Data_Entry_Sheet!b75 is populated, via the userform's textbox,
Worksheet 8 should be printed out - if the field is empty,
Worksheet 8 should not be printed. WHAT IS HAPPENING --- everytime
I run the code, even when Data_Entry_Sheet!b75 is empty, the
worksheet prints out!!!

Sorry for the extended query. I'm hoping one of the experts here can
give me a clue why this is happening and how I can fix the code.

CODE BELOW:

[[[If ("Data_Entry_Sheet!B75") >"" Then
Sheet8.PrintOut]]]

<snip>