Terminally Incoherent

Sub Worksheet_Deactivate()
    Me.Name = "The Name of the Worksheet"
End Sub

' Put this in it's own module or Workbook module
Sub DeleteButton(ByVal enable As Boolean)
  Dim CommBarTmp, Commbar As CommandBar
  For Each Commbar In Application.CommandBars
    Set CommBarTmp = Commbar.FindControl(ID:=847, _
       recursive:=True)
    If Not CommBarTmp Is Nothing Then _ 
      CommBarTmp.Enabled = enable
  Next
End Sub
 
' On each sheet put the following two subroutines:
 
' When browsing away, enable the Delete button
Private Sub Worksheet_Deactivate()
    DeleteButton True
End Sub
 
' When activated, disable the Delete button
Private Sub Worksheet_Activate()
    DeleteButton False
End Sub
 
' Add these two in the Workbook module:
 
' Enable the button before closing excel
Private Sub Workbook_BeforeClose(Cancel As Boolean)
    DeleteButton True
End Sub
 
' Enable the button when you switch to another workbook
Private Sub Workbook_Deactivate()
    DeleteButton True
End Sub

' Put all of these in ThisDocument module
Sub AutoOpen()
    WaitAndSave
End Sub
 
' Wait 5 minutes
Sub WaitAndSave()
    Application.OnTime Now + TimeValue("00:5:00"), "Saver"
End Sub
 
' Save the doc and wait again
Sub Saver()
    ThisDocument.Save
    WaitAndSave
End Sub

5 Responses to “VBA Hacks and Trickery Grab Bag”

1. jambarama Says:
   September 12th, 2008 at 4:46 pm (10135) [Quote]

   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

   Posted using K-Meleon 1.1.5 on Windows XP
2. jambarama Says:
   September 12th, 2008 at 4:53 pm (10136) [Quote]

   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

   Posted using K-Meleon 1.1.5 on Windows XP
3. jambarama Says:
   September 12th, 2008 at 4:54 pm (10137) [Quote]

   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.

   Posted using K-Meleon 1.1.5 on Windows XP
4. Luke Maciak Says:
   September 12th, 2008 at 5:12 pm (10138) [Quote]

   @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

   <pre lang="java">

   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.

   Posted using Mozilla Firefox 2.0.0.16 on Ubuntu Linux
5. jambarama Says:
   September 17th, 2008 at 5:34 pm (10204) [Quote]

   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…

   Posted using Epiphany 2.22 on Linux

Leave a Reply