Trying to create toggle button to hide a column

Posted by Justin on January 17, 2002 1:23 PM

i am making a proposal template and need to hide a certain column when printing and make it reappear when i am not printing.... i have made both a macro to hide and another to unhide the column but cant figure out how to make the toggle button control these to functions.... can anyone help?

thanks,
Justin

Posted by dan on January 17, 2002 2:01 PM

You can assign macros to buttons by going in to Design mode and right-clicking on the button and choosing View Code. That should take you to the Click event where you can call your macros. In your case, you will probably want an If statement to check to see if the column is hidden, if so then run the macro that unhides it, else run the macro the hides it.

I'm off now, so I won't be able to respond to any followups til tomorrow. Good luck.

Posted by Jacob on January 17, 2002 2:05 PM

Try this

In the workbook module put this

Workbook_beforeprint()
Call YourHideMacro
Application.OnTime (Now + TimeSerial(0, 0, 5)), "YourUnhideMacro"
end sub

This will hide the columns before printing and then unhide them 5 seconds later which should give it enough time to print first

HTH

Jacob

Posted by Joe Was on January 17, 2002 2:28 PM

This will do it in one macro

You can change the coded column in the code below or you can concatenate more than one column. You can change the column code to "Selection" and it will work on the selected column.

If you have a problem coding, just block copy the If Block below ans many times as you have columns that need to be toggled. JSW

Sub TogHide()
'Hide or UnHide Column "C"
'By Joe Was.

If Worksheets("Sheet1").Columns("C").Hidden = True Then _
Worksheets("Sheet1").Columns("C").Hidden = False Else _
Worksheets("Sheet1").Columns("C").Hidden = True
'Add another block here for an additional column,
'Just change the column letter in the code.
End Sub