Tuesday, September 16, 2014

Find alphabets in a cell

Sub Get_Alpha()
For I = 1 To Range("A1").End(xlDown).Row
    Alp = ""
    For J = 1 To Len(Range("A" & I).Value)
        StrV = Range("A" & I).Value
        If Asc(Mid(StrV, J, 1)) >= 65 And Asc(Mid(StrV, J, 1)) <= 90 Then
            Alp = Alp & Mid(StrV, J, 1)
        ElseIf Asc(Mid(StrV, J, 1)) >= 97 And Asc(Mid(StrV, J, 1)) <= 122 Then
            Alp = Alp & Mid(StrV, J, 1)
        End If
    Next J
    Range("B" & I).Value = Alp
Next I
End Sub


Friday, September 5, 2014

Macro to hide/unhide menu bars in Excel

The 2 macros below are what can be used to show your toolbar, remove all native toolbars and most importantly restore them back when done;


Sub RemoveToolbars()

    On Error Resume Next

        With Application

           .DisplayFullScreen = True

           .CommandBars("Full Screen").Visible = False

           .CommandBars("MyToolbar").Enabled = True

           .CommandBars("MyToolbar").Visible = True

           .CommandBars("Worksheet Menu Bar").Enabled = False

        End With

    On Error GoTo 0

End Sub

------------------------------------------------------------------------------

Sub RestoreToolbars()

    On Error Resume Next

        With Application

           .DisplayFullScreen = False

           .CommandBars("MyToolbar").Enabled = False

           .CommandBars("Worksheet Menu Bar").Enabled = True

        End With

    On Error GoTo 0

End Sub