Pyro's Excel Goodies

Pyrotex · 07-08-2008

Quote:

Originally Posted by alexander

...Can you write a macro for excel that will collapse certain fields to be invisible, and then next time toggle them back, or at least one macro to collapse the fields, and another to uncollapse them?....

YES!! And here is how it is done.

The solution involves doing three things:
1. Define range names to store the widths of your hide-able columns.
2. Create HIDE and REVEAL macros.
3. Create HIDE and REVEAL buttons.

1. I am assuming you want to hide one or more entire columns. Let us assume you want to hide columns B, C and D. Somewhere on that same page of your spreadsheet, out of the way of all your data, build a small table with the following Range-Names:
ColBwid
ColCwid
ColDwid
Our little table will (in this case) be three rows long, and exactly two columns wide. The cells to the right of our three names should be blank and unused.

Select all cells containing our Range-Names --AND-- the blank cells to their right. In my case, I am selecting 6 cells.

Pull down menu:Insert:Name:Create, and make sure the box "Left" is selected. Click OKAY. You have now created 3 Range-Names in your spreadsheet. This is where the HIDE macro will store the current sizes of your columns.

2. Pull down menu:Tools:Macro:Visual Basic Editor. The Project Window should be on the left. In there you will see the name of your spreadsheet. Under that is a folder for "Objects" and a folder for "Modules". Open the latter. If there is no Module, then pull down menu:Insert:Module. The blank module will appear on the right. In the module type (or copy and paste) your two Macros:

Code:

Sub Hider()
    Range("B:B").Select
    Range("ColBwid").Value = Selection.ColumnWidth
    Range("C:C").Select
    Range("ColCwid").Value = Selection.ColumnWidth
    Range("D:D").Select
    Range("ColDwid").Value = Selection.ColumnWidth
    Columns("B:D").Select
    Selection.ColumnWidth = 0  'Hide the columns!
End Sub

Sub Revealer()
    'Restore the columns!
    Range("B:B").Select
    Selection.ColumnWidth = Range("ColBwid").Value
    Range("C:C").Select
    Selection.ColumnWidth = Range("ColCwid").Value
    Range("D:D").Select
    Selection.ColumnWidth = Range("ColDwid").Value
End Sub

3. Pull down menu:Tools:Customize, select the tab "Toolbars". Click the box "Forms". Click CLOSE. The Forms toolbar should now be visible. We want only one thing off that toolbar, the rectangle icon, which creates buttons. Pick a place to put two buttons. I suggest immediately above or below your Range-Names. Allocate a 4-cell block (2 by 2) for each button.

Click the button icon on the Forms toolbar. With your "cross" cursor draw your first button in a 4-cell block. Repeat for your second button.

If you Right_Click either button, it will allow you to change it, resize it, move it, select and retype its name, assign its macro. Change the names to HIDE and REVEAL. Click some cell away from the buttons so they're not selected.

Again, Right_Click each button in turn and select "Assign Macro". Pick the corresponding macro for each button. Click some cell away from the buttons so they're not selected.

That's All, Folks! Make sure you click the HIDE button first so the Range-Names will be initialized. The REVEAL button will re-expand the columns to the same width they were before. I thought you would like that.

alexander · 07-08-2008

I do, all props to the king of macros

All hail Pyro

Pyrotex · 07-09-2008

You are very welcome, Alexander.
Thanks for asking.
It would not bother me at all if I were to become a "resource" for all Hypographites who need spreadsheet expertise of any kind.

Pyro, King of the Macros

Pyrotex · 07-09-2008

BTW, Alexander, did you TRY the solution? Did it work?

alexander · 07-10-2008

Well, ok, we actually came up with a solution a while ago, i'm now trying to do conditional hides, and for the time being it's not working

here's the macro code we use now for hiding the fields (courtesy of the person in the AV division, who's name i don't think makes any difference here):
(btw no separate table required to restore the width

)

Code:

Sub company_format_review()
    Columns("D:N").Select
    Selection.EntireColumn.Hidden = False
    Range("A8").Select
    With ActiveSheet.PageSetup
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
    End With
End Sub

Sub Customer_review()
    Range("E:E,G:G,H:H,I:I,J:J,K:K").Select
    Range("K1").Activate
    Selection.EntireColumn.Hidden = True
    Range("A8").Select
    With ActiveSheet.PageSetup
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .Orientation = xlPortrait
    End With
End Sub

now here is the new perplexion

have a field that points to another field that does calculations for say labor, what i wanna do is hide the whole row if the value is nothing, and if there is a value, i dont want to hide the row. And i'll need to do this for a dozen or so rows...

Pong back to you Pyro, help me Pyro-One-Kenobi, for you this is like fun, and perhaps not even anything challenging, and i don't like M$ Office

Pyrotex · 07-10-2008

Quote:

Originally Posted by alexander

Well, ok, we actually came up with a solution a while ago...

Okay, your friend's code will also work. Hiding a column produces the same effects as setting its width to zero. However, your friend's code is a tad sloppy. Instead of:

Code:

With ActiveSheet.PageSetup
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .Orientation = xlLandscape
    End With

you should have this, which does exactly the same thing without duplicate statements.

Code:

With ActiveSheet.PageSetup
    .PrintArea = ""
    .Orientation = xlLandscape
End With

Quote:

have a field that points to another field that does calculations for say labor, what i wanna do is hide the whole row if the value is nothing, and if there is a value, i dont want to hide the row. And i'll need to do this for a dozen or so rows...

Certainly a macro can do this, but it cannot be a "conditional format" kinda thing. In other words, Excel will not automatically set a row height to zero because of some formula in a cell. Gotta be a macro. So, use the same technique as your friend did. Example:

Code:

Sub Hide_Zeros()
    Const cFlagOffset = 3
    Range("LaborHeader").Select
    Do Until Selection.Offset(0, cFlagOffset).Value = "XXXX"
        Selection.Offset(1, 0).Select 'move down one row
        If Selection.Value = 0 Then
            Selection.EntireRow.Hidden = True
        Else
            Selection.EntireRow.Hidden = False
        End If
    Loop
    Range("LaborHeader").Select
End Sub

Now, this assumes you have a Range-Name defined for the "header" of the Labor column. Change it to whatever the header actually is.
And, this assumes that you have a "Flag" (XXXX, in this case) situated off to the side that marks the END of the Labor data. The offset of this Flag (3 columns to the right, in this case) is defined as a constant and its value is up to you. So, for example, if your Labor values are in column "H", then this macro looks for "XXXX" in column "K" in the the same row as your LAST Labor value (whether its zero or not).
You can execute the macro with a keyboard shortcut, but I recommend a button.
And you will definately want to show ALL rows from time to time, so you need this macro:

Code:

Sub Show_All()
    Const cFlagOffset = 3
    Range("LaborHeader").Select
    Do Until Selection.Offset(0, cFlagOffset).Value = "XXXX"
        Selection.Offset(1, 0).Select 'move down one row
        Selection.EntireRow.Hidden = False
    Loop
    Range("LaborHeader").Select
End Sub

Of course, if your Labor Table has a constant number of rows, you don't need to use a Flag to mark the end ("XXXX"). You can replace the "Do/Loop" statement with:

Code:

    For I = 1 to 12
    ...
    Next I

alexander · 07-10-2008

ok this is what i settled on doing:

to the right of the various fields i had to hide, i set a function with a value of, as an example:

Code:

=NOT(NOT(A1))

and then macro was straight forward (after 4 hours of tinkering with it)

why? this effectively casts any value in the field as boolean, any 0, false, or null will be cast as false, and anything else as true, which is easy to check afterwards...

Code:

Sub hide_empty_fields_bottom()
    For I = 1 To 10
        Range("B" + CStr(I)).Select
        Selection.EntireRow.Hidden = Selection.Value Xor True
    Next I
    Selection.EntireColumn.Hidden = True
End Sub

Sub unhide_empty_fields_bottom()
    For I = 1 To 10
        Range("B" + CStr(I)).Select
        Selection.EntireRow.Hidden = False
    Next I
    Selection.EntireColumn.Hidden = True
End Sub

though in all reality they should be combined into a single function, but i dont care enough to do that...

Problem i ran into was that the range function was returning a value that i couldn't even cast, it was so insanely weird, probalby due to what has to happen on the back end of the function or formatting or something.... so after hours of banging my head against the table, just rewrote it as such

(it works now)