 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / New Users / June 2007Showing/Hiding Columns by clicking a button
Hi, I would like to write some smarter code to - (a) initially hide columns C-I on Worksheets 1-3, then (b) show only the columns selected by check boxes on each of those Worksheets after pressing a button named 'SIB'. What I am doing at the moment for is the following: Private Sub SIB_Click() Worksheets("Sheet2").Columns("A:A").Hidden = False Worksheets("Sheet2").Columns("B:B").Hidden = False Worksheets("Sheet3").Columns("A:A").Hidden = False Worksheets("Sheet3").Columns("B:B").Hidden = False Worksheets("Sheet4").Columns("A:A").Hidden = False Worksheets("Sheet4").Columns("B:B").Hidden = False If CheckBox1.Value = True Then Worksheets("Sheet2").Columns("C:C").Hidden = False Worksheets("Sheet3").Columns("C:C").Hidden = False Worksheets("Sheet4").Columns("C:C").Hidden = False Else: Worksheets("Sheet2").Columns("C:C").Hidden = True Worksheets("Sheet3").Columns("C:C").Hidden = True Worksheets("Sheet4").Columns("C:C").Hidden = True End If If CheckBox2.Value = True Then Worksheets("Sheet2").Columns("D:D").Hidden = False Worksheets("Sheet3").Columns("D:D").Hidden = False Worksheets("Sheet4").Columns("D:D").Hidden = False Else: Worksheets("Sheet2").Columns("D:D").Hidden = True Worksheets("Sheet3").Columns("D:D").Hidden = True Worksheets("Sheet4").Columns("D:D").Hidden = True End If etc., etc. right through to CheckBox 7 & Column I. I am not sure how to write efficient code to hide all the columns, then write efficient code to hide all but the selected columns. If anyone can help me on this I would be very grateful. Best regards, Brian Private Sub SIB_Click() Worksheets("Sheet2").Columns("C:C").Hidden = CheckBox1.Value Worksheets("Sheet3").Columns("C:C").Hidden = CheckBox1.Value Worksheets("Sheet4").Columns("C:C").Hidden = CheckBox1.Value Worksheets("Sheet2").Columns("D:D").Hidden = CheckBox2.Value Worksheets("Sheet3").Columns("D:D").Hidden = CheckBox2.Value Worksheets("Sheet4").Columns("D:D").Hidden = CheckBox2.Value 'etc.  SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > Hi, > [quoted text clipped - 41 lines] > Best regards, > Brian > Private Sub SIB_Click() > [quoted text clipped - 7 lines] > > 'etc. That's fantastic and helps a lot, thank you very much Bob! Thanks for responding so quickly, too. Best regards, Brian. > Private Sub SIB_Click() > [quoted text clipped - 7 lines] > > 'etc. Hi again Bob, oops, I spoke a little too soon. Your code makes a checkbox hide a column rather than show a column. I am not sure how to reverse the situation. I tried using "Visible" instead of "Hidden", but that only gave an error message telling me there is no such property for this type of Object. Any ideas how I might get the checkboxes to show the columns? Best regards, Brian. I wasn't sure about the setting, your words seem to be against the code you gave <g> Just logically reverse it = Not CheckBox1.Value etc. SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >> Private Sub SIB_Click() >> [quoted text clipped - 18 lines] > Best regards, > Brian. >I wasn't sure about the setting, your words seem to be against the code you >gave <g> [quoted text clipped - 4 lines] > > etc. Thanks again, Bob. I apologise for the ambiguity in my original post. I am completely new to VBA, I have a lot of reading to do. I didn't even know one could place a NOT statement like that. I appreciate your help very much. Best regards, Brian. No need to apologise, I read it as ambiguity and took a punt ... wrong <vbg>. I should have trusted the code <ebg> SignatureHTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) >>I wasn't sure about the setting, your words seem to be against the code >>you gave <g> [quoted text clipped - 13 lines] > Best regards, > Brian. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.