MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Hiding Controls when printing an Excel Spreadsheet

Posted by Clinton Hayes on August 14, 2001 7:55 PM

I have created a quotation form in Excel utilising various controls to select product and price groups for a quotation. Allowing other users to simply select the category and have the quotation priced correctly was the aim.
However, as I've added controls to each line, when the quotation is finished and printed, all the unused lines show the controls. Al I really want the form to show is the information from the control and if none entered, then show nothing.
Would appreciate any help.
Clinton Hayes.

Posted by neotech on August 14, 2001 8:28 PM

the easiest way would be through a little VBA coding for the controls. if you're not looking for that, the second easiest way is to select the controls you don't want printed, right-click one of them and select 'format control'. on the 'properties' tabs unselect the Print Object checkbox.

with vba, do the steps above then right-click each control one at a time and select 'view code'. in the new window that pops up, you'll see your cursor between two lines of words. the code you'll need will be different from one type of control to the next. for example if your object was a command button, you'd type : 'commandbutton1.printobject = false' in between the sentences.
textbox :
textbox1.printobject = false
checkbox :
cehckbox1.printobject = false

i think you get the idea. if not just say so and i'll explain more


Posted by Clinton Hayes on August 16, 2001 12:34 AM

Thanks very much neo for taking the time. That solves my problem.

Posted by Clinton Hayes on August 16, 2001 12:34 AM

Thanks very much neo for taking the time. That solves my problem.
Cheers Clinton.

Posted by Clinton Hayes on August 16, 2001 12:44 AM

Sorry neo, one more thing if it's OK.
What I want is for the text selected in the combo to be printed under certain conditions only. That is, the first selection in the Combo box may be N/A, which means I don't want the contents printed and if the user selects anything else from the range, then I want that printed on the form.
Could I just put an "IF" statement into the vba code to set print to false only if N/A is shown? I don't know how to do the code so if you can help, it would be appreciated.

Posted by neo on August 16, 2001 10:24 AM

sorry i didn't see this until now... here's what you're looking for:

if combobox1.text = "#N/A" then
combobox1.pritobject = false
combobox1.printobject = true
end if


Posted by Clinton Hayes on August 22, 2001 3:37 PM

Sorry, I've been away. Thanks very much again for taking the trouble.