How to unhide all sheets in an excel workbook / Show all sheets in one click / Unhide veryhidden sheets

Sometimes when we create larger reports in Excel, we will hide few sheets where we entered raw data or controls or calculations.

It's a tedious task to right click and unhide all the sheets one by one.

Sometimes, we will have sheets which are hidden from VBA IDE also which cannot be unhidden from Excel GUI.

You can unhide all the sheets in an excel workbook with a small piece of code below.


Sub UnHideSheets()
Dim Sht As Worksheet
For Each Sht In ThisWorkbook.Sheets
    Sht.Visible = xlSheetVisible
Next Sht
End Sub

View & work with same Excel file in more that 2 places at a time / Two windows at a time

Sometimes when we work on Excel, it's required to do some comparisons, copy paste, analysis on the same workbook at same time

You can do this easily following below steps

1. Goto View Ribbon 

2. Find Windows section

3. Click on New window

You can see two instances of same workbook is open now.

Note : For more easy usage, Make workbook window in "Restore Mode" and reduce the size of windows to arrange in your desktop as you wish to work, so you can see and use both instances at same time.

Find the total number of sheets in an Excel Workbook

VBA Code to Get the count of all sheets in an Excel Workbook

Sometime I come across with Excel reports with numerous worksheets / tabs.

Sometimes you can't find the starting or ending of the workbook.

Whenever I see those kind of Excel reports, I always wonder how many sheets will be there in that workbook !

Of Course, You can count the Excel sheets one by one. Select one sheet, then press CTRL + Tab to goto next sheet, keep on counting in your head. :) Very good time pass.

Excel is not providing a way to find how many worksheets are existing in a workbook.

If you want to know the count of all sheets in an Excel workbook, we have an easy way.

Steps:

1. Goto VBE, by pressing ALT + F11 or Click on VisualBasic Icon from the Develepor Tab.
2. Activate Immediate window by Pressing CTRL + G
3. ?activeworkbook.Sheets.Count   Copy paste the highlighted VBA code to immediate window and press Enter
4. You can see the count of the sheets in the active Excel workbook has printed just below the code which you have entered.
   
   msgbox activeworkbook.Sheets.Count If you use this code, a message box will be shown with count of sheets in the workbook.

This code becomes very important when you write VBA routines and loops.

Removing Borders in Excel

Adding borders to cells is a very common thing in Excel. You may add them to help draw attention to number, or to divide numbers from column headings in a table.

Here is a quick way you can remove any borders applied to a cell or group of cells: simply press Ctrl+_ Ie, Ctrl + Shift + _ . (That is the underscore, which means you must hold down the Shift key as well.) Excel leaves the other formatting of the cell set, but removes any borders.

Remove Gridlines From excel sheet

Fed up with watching gridlines in you excel sheet ?

Steps

1. Open your worksheet , Go to Tools Menu > Options 
2. In View Tab , You can see Window Options 
3. By Defult Gridlines are ticked, Untick it , Click ok Button

Then You have it


Note: If you want to change color of gridlines, there is an option under it, change gridlines color in it & Tick Gridlines.

Give Superb look for your excel sheet

1. Remove Annoying Gridlines from your sheet Click here

2.Remove Page Breaks

Steps
1. Open your worksheet , Go to Tools Menu > Options 
2. In View Tab , You can see Window Options 
3. By Defult Page Breaks are ticked, Untick it , Click ok Button

3. Remove Row & Coloumn Headers 

 Steps
1. Open your worksheet , Go to Tools Menu > Options 
2. In View Tab , You can see Window Options 
3. By Defult Row & Coloumn Headers  are ticked, Untick it , Click ok Button

4. Remove Scroll bars

Steps
1. Open your worksheet , Go to Tools Menu > Options 
2. In View Tab , You can see Window Options 
3. By Defult Horizondal Scroll bars & Vertical Scroll bars  are ticked, Untick both  , Click ok Button

5. Remove sheet Tabs
Note: Only Use this option if you have only 1 worksheet / you have hyperlinks to other sheets.
Steps
1. Open your worksheet , Go to Tools Menu > Options 
2. In View Tab , You can see Window Options 
3. By Defult sheet Tabs  are ticked, Untick it  , Click ok Button

Excel Worksheet Shortcuts - Very useful

Action	Key
Moving Forward to next worksheet	Ctrl + pagedown
Moving Backward to next worksheet	Ctrl+ Pageup
Selecting Multiple worksheets- Forward	Ctrl + Shift + pagedown
Selecting Multiple worksheets- Backward	Ctrl + Shift + pageup

Excel Shortcuts: Selecting These shortcuts help you to quickly select items in your Excel file in various ways. Shift+Spacebar: Select the current Row Ctrl+Spacebar: Select the current column Ctrl+A: Select All, will select everything on the current worksheet. This is most often used in conjunction with the copy shortcut.

How to add formula to a worksheet range using VBA Codes

How to add formula to a worksheet range using VBA


Adding formulas to ranges is common task when you workaround with Excel VBA.

If you are good in entering formulas in Excel, you can become an expert in adding VBA formulas too.

Eg :

We have some values in a range A1:A20 and We have some values in B1:B20 also. We need to get the difference of all values in B column with corresponding Cell in Column A, in the Column C.

In Excel we will enter a formula = B1-A1 in the cell C1 and do a fill down or a copy paste till C20. It's the simplest formula when we work in Excel.

But How to get the same formulas in C1:C20 using VBA codes ?

You can use two ways.

1. Enter Activesheet.Range("C1:C20").Value = "RC[-1] + RC[-2]" in your code.
2. Enter Activesheet.Range("C1:C20").FormulaR1C1= "RC[-1] + RC[-2]" in your code.

Both codes with do the same task. Note, VBA is using R1C1 reference in entering the formulas to Excel. It's better to Get a basic idea about how R1C1 reference style works, before you start working with formulas in VBA.

Solution: If you have any doubt in VBA, first try to record the task which you want to automate and see the codes. Same way, you record the task of entering the above formulas using macro recorder. See the codes.

How to get Office 365 username in a excel worksheet cell / Excel Function to get the username

You might require get application's username in order to user in your projects.

Since the arrival of Office 365, the office application's username became more valid where in older versions allowed user to edit the user name easily.


Add the below codes in a module of your project.

Public Function CurrentUser()
CurrentUser = Application.UserName
End Function

This function can be called from your worksheet or from a macro you have written.

But still there are lots of uses for this function in realtime. Like you can surprise the user by greeting him , etc.

How To disable scrolling in an Excel sheet / Make a Static Page in Excel

How To disable scrolling in an Excel sheet / Make a Static Excel Page:

Most of the Excel Geeks might have faced this problem.

How to make an Excel page static ?

How to lock the scrolling or disable the scrolling of the excel sheet along with mouse ?

How to make a static front page for your dashboard or excel report ?

How to prevent the end user from scrolling the sheets?

Even I have faced this issue.

I found a solution after long experiments.

 Click on this link to open the sample file for Reference. 
Click on SKIP ad to Download the file. (Note: The password is blank to unprotect the sheet)

Did you like the way it's built ? It's the billing file I had built for myself to note the time spent on projects and to send to clients for payments. You can see the file has made in a professional way. A Normal user will get wondered thinking what kind of file is this. So many clients had asked me that which software I had used to make this billing file.

It's a simple excel file. You can see the Column headers & Row headers are hidden, Scroll bars are not available, Sheet tabs are hidden. You can click or select only the cells which requires any changes. It's all basic Excel tricks.

But you can see that you can't scroll the mouse. You can't use PageUp Or PageDown. The page is completely fixed only the screen. I will explain how to do this.

Do the following steps:

1. First of all, hide the vertical or horizondal scroll bars or both as per your need.

    File > Excel Options > Advanced > Display options for this workbook >

   Untick the Show horizondal scroll Scroll bar and vertical scroll bar.

   Click OK, it will hide the bars. But it's not the solution. You can see still the scrolling is working.

2. Then Goto VBE ( Press Alt + F11)

3. Open Project Explorer > VBAproject > MS Excel objects > This workbook

4. In VBA code Window

                  From 'General' drop down , select 'Workbook'

                  Then, from the 'Declaration' drop down, select 'Open'

You will get a default code like this.

Private Sub Workbook_Open()

End Sub

5. Enter ThisWorkbook.Sheets("DashBoard").ScrollArea = "A1:E20"  into the above sub.

Final code will be like this :

Private Sub Workbook_Open()

ThisWorkbook.Sheets("DashBoard").ScrollArea = "A1:E20"

End Sub

This code will run when you open the workbook and the page will be static. User will not be able to scroll or select any cell out of the given range above.

Notes :

• You should change name of the sheet and the range in the above code as per your workbook. 
• If the current workbook is not macro enabled workbook, you have to save the file as macro enable workbook. 
• Make sure the macro is enabled on your excel
• You cannot unfreeze the scrolling once it's run the code while opening the file. If you want to unfreeze the scrolling, first comment the vba code and then save the file. So the code will not run while opening the file. Then Re-Open the file.

How to Comment the VBA code:

Add the comment operator (') before starting of the line as shown below. The Single Quote has placed before ThisWorkbook. You can see the commented line in a different color.

Private Sub Workbook_Open()

'ThisWorkbook.Sheets("DashBoard").ScrollArea = "A1:E20"

End Sub