Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Conditional Macros
Assume you have a listbox on worksheet1 that contains the month names. the
listbox has the multiselect property set to fmMultiSelectMulti Assume the first entry in the listbox is at column 10 on each sheet and the months are in sequential columns. Further assume that all sheets in the workbook have the month columns on them except the sheet with the listbox. Either of the below should do what you want. Private Sub CommandButton2_Click() For Each sh In ThisWorkbook.Worksheets If sh.Name < Me.Name Then sh.Columns.Hidden = False End If Next For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then For Each sh In ThisWorkbook.Worksheets If sh.Name < Me.Name Then sh.Columns(i + 10).Hidden = True End If Next End If Next End Sub If you wanted to write more code, you could make a list of the columns and avoid multiple loops through the sheets. Private Sub CommandButton1_Click() Dim rng As Range For i = 0 To ListBox1.ListCount - 1 If ListBox1.Selected(i) Then If rng Is Nothing Then Set rng = Columns(i + 10) Else Set rng = Union(rng, Columns(i + 10)) End If End If Next If Not rng Is Nothing Then For Each sh In ThisWorkbook.Worksheets If sh.Name < Me.Name Then sh.Columns.Hidden = False sh.Range(rng.Address).EntireColumn.Hidden = True End If Next End If End Sub -- Regards, Tom Ogilvy "Kevin" wrote in message ... This seems like a simple thing to do, but I can't figure it out on my own. Basically, I have multiple worksheets, with successive months across the columns, and corresponding data beneath. I want the user to be able to quickly and automatically hide certain months in all of the worksheets. I was thinking of making seperate macros that hide individual months, then having check boxes on a new worksheet so that a user could just check the one he wants to include/hide, then press a button, and it will run the appropriate macros. I can make the macros, I just don't know how to do some sort of "if" that will determine which ones to run. Ideally, I was hoping to just have a list box and have the user select whatever months to inclue in this, but I couldn't even get started on this, so I started the other way. If you can help me with either of these methods, or a different one, I will greatly appreciate it. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional Formating and Macros | Excel Discussion (Misc queries) | |||
Conditional Replace without macros | Excel Worksheet Functions | |||
Conditional Formatting and Macros in Excel | New Users to Excel | |||
Conditional formatting based on decision to enable/disable macros? | Excel Discussion (Misc queries) | |||
Conditional Macros | Excel Programming |