Addin With Useful Macros & Easily Accessible From Custom Ribbon Tab

• Download CustomUIEditor.zip To Create and Show Ribbon - 69.6 KB
• Download Custom Ribbon TabAddin.zip - 63.6 KB

Introduction  

With due thanks to all whose code snippets and / or logic I've used in creating this add-in. 

I have developed this add-in which on installation will create a new custom tab called 'Macros' consisting of many useful macros which are needed for every excel user.  

The add-in on installation will create a new custom tab called 'Macros' in the Ribbon Bar.

The Macro tab consists of four groups. 

• Single Sheet Macros 
• Macros For All Sheets 
• Merge Sheets
• Send Emails Utility
• Manage Work Sheets 
• Convert Case Utility 

• Single Sheet Macros Group: 

The group consists of macros which will be executed only on the active sheet.

When the user clicks on the button 'Single Sheet Macros Menu; a dropdown consisting of macros is presented to the user. 

The dropdown consists of following Macros: 

1. Remove / Unprotect password protected Work sheet 
2. Unhide hidden rows & columns 
3. Remove Filter & Freeze Panes Mode  
4. Paste Special As Values 
5. Delete Blank Rows And Columns
6. Auto fit all rows & Columns 
7. Apply predefined formatting 
8. Swap Rows
9. Swap Columns 
10. Performing Most Common Tasks ( i.e running all macros mentioned above ) 

• Macros For All Sheets Group:  

When the user clicks on the button 'Macros For All Sheets' a dropdown consisting of macros is presented to the user. 

 The dropdown consists of following Macros: 

1. Remove / Unprotect password protected from all the sheets 
2. Unhide hidden rows & columns from all the sheets   
3. Remove Filter & Freeze Panes Mode from all the sheets 
4. Paste Special As Values on all the sheets   
5. Delete Blank Rows (Only active sheet - some tweaks required) 
6. Auto fit all rows & Columns of all sheets 
7. Apply predefined formatting in all sheets 
8. Performing Most Common Tasks ( i.e running all macros mentioned above )   

• Merge Sheets Group:  

When The user clicks on the button 'Merge Work Sheets ' all the worksheets in the active workbook are merged  in a new worksheet called Master. Before running this macro / clicking please make sure that the headings on all the sheets are same.

• Send Email Group: 

When The user clicks on the button 'E Mail Utility', a userform with varied options to mail is presented. the user can mail the active workbook or active sheet as an attachment. The user can also extract his contacts from Outlook Contacts List & select them as recipients to send mails. 

• Manage Work Sheets Group: 

When The user clicks on the button 'Manage Work Sheets Utility', a userform opens which lets the user to delete multiple worksheets in one go.  

• Convert Case Utility Group: 

When The user clicks on the button 'Case Converter', the user can toggle between UPPER CASE, lower case & Sentence Case. 

Using the code 

The File works only on MS Office Excel 2007 Version. Haven't checked on other Excel versions. 

Click on the file and install the add-in properly. 

Restart the excel app if the ribbon tab 'MACROS' is not visible. 

' The Following Code will Logically change the case from UPPER to lower And Sentence Case
Sub ChangeCase
Dim c As Range, rng As Range, cc As Integer
    If Selection.Count = 1 Then
        Set rng = ActiveCell
        Else
        Set rng = Selection.SpecialCells(xlCellTypeConstants).Cells
    End If
    Set c = rng(1)
    Select Case True
        Case c = LCase(c)
        cc = 1
        Case c = UCase(c)
        cc = 2
        Case Else
        cc = 3
    End Select
    Application.EnableCancelKey = xlErrorHandler
    On Error GoTo xit
    Application.EnableEvents = 0
    For Each c In rng
        With c
            .Formula = Choose(cc, UCase(.Formula), _
                       Application.Proper(.Formula), LCase(.Formula))
        End With
    Next c
xit:
    Application.EnableEvents = 1
End Sub

Points of Interest 

Learn to customize the ribbon using the following link: http://www.rondebruin.nl/ribbon.htm.

You can download the tool Custom UI Editor to learn how to create & change the ms office ribbon menu. http://openxmldeveloper.org/blog/b/openxmldeveloper/archive/2010/08/10/23248.aspx.

History 

I would like to roll out more useful macro with some tweaks. So let's wait and watch.