Syntax highlighting, what a fancy WP plugin! I’ll remember that next time I post a chunk of VB. Actually, since I haven’t touched VBA (or any coding) in a year and a half (since starting grad school), I’m glad some of my old stuff might be still useful somewhere.

I know my old company is still using a metric ton of VBA I wrote for the finance guys, the management, the travel coordinators, and the administrative assistants, but I hope the coders in the company have replaced it with apps more suited to the tasks.

Anyhow, thanks for the VBA posts, they take me back to a darker time… ;)

@jambarama: No worries – I fixed it. The code tags do not get the special formating on here. See that little “Code” button above the edit box? If you click it it will insert

into your comment. You can change the lang parameter to a specific language (in this case "vb") and the WP-Syntax plugin will pick it up and apply appropriate highlighting. If your language is not supported by WP-Script it will not apply any color highlighting, but it will maintain indentation and escape < and >. Once you are done just close the pre tag and you are all set. That's how I do all my code snippets here.

I have yet to figure out whether or not I can force WP-Syntax to work with the code tags.

Oh, and you are a hero for making this. I love it! :)

This whole macro security bullshit is annoying. Everyone wants to have macros in their spreadsheets! But every time we send out a memo showing people how to lower the macro security so that they can run them, someone gets on a soapbox and cries that his brothers' second cousin's former room mate allegedly said your macro security must be set to High at all times, no matter what and that we must be bunch of talentless hacks and clueless retards for recommending otherwise.

Can't win. :P

Sorry to post so much, but it looks like my code brackets didn’t include everything within, so the formatting & such is fugly. My apologies.

Right, I forgot to paste in the trigger subs. These had a lot of other stuff in them that I had to remove (it was particular to the data) but this is the gist:

Private Sub Workbook_Open()
    With Application
        Call UnhideSheets
    End With
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        Call HideSheets
    End With
End Sub

Oh man I hear you. At one of my jobs, once people found out I wasn’t a tard with programming, they asked me to write all kinds of excel macros. One particular annoyance I had was with people with high security settings who wouldn’t turn them down (even with explicit directions on how to do it) then complain that it isn’t working. I wrote this beauty to combat that problem. Basically if you set it to autorun, when the user closes the xls file thsi macro hides every worksheet except one with a warning telling them to enable macros. Then when the next person opens it, if macros can run – this unhides everything, if macros can’t run, all the user will see is the warning telling them how to enable macros.

Option Explicit
 
Private Sub UnhideSheets()
    Dim Sheet As Object
    .ScreenUpdating = False
    For Each Sheet In Sheets
        If Not Sheet.Name = "InfoSheet" Then
            Sheet.Visible = xlSheetVisible
        End If
    Next
    Sheets("InfoSheet").Visible = xlSheetVeryHidden
    Set Sheet = Nothing
    ActiveWorkbook.Saved = True
    .ScreenUpdating = True
End Sub
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    With Application
        .ScreenUpdating = False
        Call HideSheets
        .ScreenUpdating = True
    End With
End Sub
 
Private Sub HideSheets()
    Dim Sheet As Object
    With Sheets("InfoSheet")
         If ThisWorkbook.Saved = True Then .[Q200] = "Saved"
        .Visible = xlSheetVisible
        For Each Sheet In Sheets
            If Not Sheet.Name = "InfoSheet" Then
                Sheet.Visible = xlSheetVeryHidden
            End If
        Next
        If .[Q200] = "Saved" Then
            .[Q200].ClearContents
            ThisWorkbook.Save
        End If
        Set Sheet = Nothing
    End With
End Sub