Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on Control Source in Userform Textbox
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on Control Source in Userform Textbox
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: <snip [[[If ("Data_Entry_Sheet!B75") "" Then Sheet8.PrintOut]]] <snip |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on Control Source in Userform Textbox
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 "Jake Marx" wrote in message ... 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: <snip [[[If ("Data_Entry_Sheet!B75") "" Then Sheet8.PrintOut]]] <snip |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on Control Source in Userform Textbox
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] golf4 wrote: 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 "Jake Marx" wrote in message ... 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: <snip [[[If ("Data_Entry_Sheet!B75") "" Then Sheet8.PrintOut]]] <snip |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query on Control Source in Userform Textbox
Hi, Jake -
Thanks again for the assistance. I'll give your suggestion(s) a shot when I get home. Thanks again, Golf "Jake Marx" wrote in message ... 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] golf4 wrote: 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 "Jake Marx" wrote in message ... 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: <snip [[[If ("Data_Entry_Sheet!B75") "" Then Sheet8.PrintOut]]] <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Syntax for control source in combo box control | Excel Discussion (Misc queries) | |||
userform textbox | Excel Worksheet Functions | |||
Autofill userform textbox | Excel Programming | |||
hide a textbox on a userform? | Excel Programming | |||
UserForm TextBox to ActiveSheet TextBox over 256 characters | Excel Programming |