Thursday, 31 March 2011

April Fools Reverse The Excel Menu with VBA

The code below will, when run, reverse all of the menus and their options within Excel.  For example; File will become Elif and Tools will become Sloot.  It works on Excel 97 through 2003.  Simply run the code again to ‘reverse’ the name back to their original format.  It is worth noting that Excel remembers the settings, so you will need to be able to re-run the code.

Sub ReverseMenuText()
    Dim m1 As CommandBarControl
    Dim m2 As CommandBarControl
    Dim m3 As CommandBarControl

    On Error Resume Next
    For Each m1 In Application.CommandBars(1).Controls
        m1.Caption = Reverse(m1.Caption)
        For Each m2 In m1.Controls
            m2.Caption = Reverse(m2.Caption)
            For Each m3 In m2.Controls
                m3.Caption = Reverse(m3.Caption)
            Next m3
        Next m2
    Next m1
End Sub

Function Reverse(MenuText As String) As String
    Dim Temp As String, Temp2 As String
    Dim ItemLen As Integer, i As Integer
    Dim HotKey As String * 1
    Dim Found As Boolean

    ItemLen = Len(MenuText)
    Temp = ""
    For i = ItemLen To 1 Step -1
        If Mid(MenuText, i, 1) = "&" Then _
            HotKey = Mid(MenuText, i + 1, 1) _
        Else Temp = Temp & Mid(MenuText, i, 1)
    Next i
    Temp = Application.Proper(Temp)
    Found = False
    Temp2 = ""
    For i = 1 To ItemLen - 1
        If UCase(Mid(Temp, i, 1)) = UCase(HotKey) And Not Found Then
            Temp2 = Temp2 & "&"
            Found = True
        End If
        Temp2 = Temp2 & Mid(Temp, i, 1)
    Next i
    If Left(Temp2, 3) = "..." Then Temp2 = Right(Temp2, ItemLen - 3) & "..."
    Reverse = Temp2
End Function

This code was orinally found on

No comments:

Post a Comment

Please, no purely anonymous comments, they will be deleted; always use a name for ease of reference by other commenters.