Hide And Unhide Worksheets Using VBA

Sometimes we need to hide or unhide sheets in Excel. If you develop an advance application with lots of excel databases and VBA forms, then you may need to hide the database from the users. So then they can only alter data with userforms. In this post first I will explain you how to hide or unhide worksheets manually. Then I will teach you how to do it using VBA.

Assume we have an Excel workbook like this. So we have 3 sheets in our file.

If we want to hide one of the sheets manually we can do it as follows. First right click on the worksheet name you want to hide. Then click on “Hide”.

As I click on the “Sheet 1”, it will hide the “Sheet 1”

And if you need, you can hide several sheets at once. What you need to do is select all the sheet names you want to hide while holding down Ctrl key in your keyboard. (However you can’t select all the worksheets in the workbook to hide. Because there should be atleast one visible sheet in the workbook.) Then right click on one of the worksheet name you want hide and click on “Hide”.

This will hide all selected sheets.

Next let’s learn how to Unhide sheets manually. You can do it as follows. First right click on one of visible worksheets. Then click on Unhide.

Then it will show list of hidden sheets like this.

Select the worksheet you want to unhide and then click OK. Selected worksheet will become visible.

Now let's look at how to hide a worksheet using VBA. Assume you have worksheet call Sheet1. And you should have at least one more sheet in the workbook in addition to Sheet1. You can hide Sheet1 like this.

Sub HideSheet() Worksheets("Sheet1").Visible = False End Sub

And if you want to unhide that sheet using VBA then you can do it as follows.

Sub UnhideSheet() Worksheets("Sheet1").Visible = True End Sub

However if you use above method to hide sheets, then users have ability to unhide them manually if they want. But there is another method you can hide sheets which doesn't allow manual unhide. If you use that method then worksheets can be unhidden only using a above unhide code. Here is the code.

Sub VeryHidden() Worksheets("Sheet1").Visible = xlVeryHidden End Sub

So assume we have 3 sheets like this.

And we run above code.

As you can see the Sheet1 is now in hidden state. But you can’t unhide it manually as it doesn’t show hidden sheet.

So you need to use above Unhide code to make it visible.

How to Read, Write, Delete and Move excel comments

In this post let’s look at how to automate Excel cell comments using VBA. If you want to manually enter a cell comment, then you can do it as follows. First right-click inside a cell. Then click on “Insert Comment”.

Then Excel will create a box like this.

Now you can enter your comment.

So if you have a spreadsheet with lots of data, how do you identify the cells which have comments? It is easy. Cells with comments will have red color triangle shape in top right hand corner of the cell. In this example there is a comment in D3 cell.

If you want to read the comment you can take your cursor on top of that cell. Then it will show the comment.

Now let’s look at how to read this comment using VBA. If the comment is in cell D3 then you can read it as follows.

Sub ReadComment() Dim StrComment As String StrComment = Range("D3").Comment.Text Debug.Print StrComment End Sub

If you run above macro, at the beginning comment will be assigned to string variable call “StrComment”. Then it will be printed in immediate window.

Next let’s learn how to write a comment to a specific cell. Below is the code to write a comment to a cell. This will add comment “This is sample comment” to cell G5.

Sub WriteComment() Range("G5").AddComment "This is sample comment" End Sub

Also note that you can replace “Range” keyword with “Cells”. So instead of Range(“D3”) you can write Cells(3,4) and instead of Range(“G5”) you can write Cells(5,7)

So if you want to move comment from B2 cell to C2 cell then you can do it as follows.

Sub MoveComment() Dim StrComment As String StrComment = Cells(2, 2).Comment.Text Cells(2, 3).AddComment StrComment End Sub

Also if you are developing a dynamic application, it is important to check whether there is a comment in the cell before try to read the comment. Because otherwise excel will produce runtime error if program try to read a comment from a cell where there is no comment. You can check whether there is a comment in cell A1 using following code.

Sub CheckForComment() If Cells(1, 1).Comment Is Nothing Then      MsgBox "No comment" Else      MsgBox Cells(1, 1).Comment.Text End If End Sub

If there is no comment it will give you “No comment” message. If there is a comment then macro will show the comment in a message box.

Create new workbooks from each and every worksheet in your workbook | Excel VBA

In an earlier post I explained how to create a workbook from a particular sheet in Excel VBA. Today I will teach you how to create new workbooks from each and every worksheet of your workbook and how to save them to desired location. So here is the workbook I have.

Name of this workbook is “Original file.xlsm”. As you can see this workbook has three worksheets. So I’m going to create 3 separate workbooks from them and save them in folder where this “Original file.xlsm” is in. This is how the folder look like before run the macro. It has only one file.

So below is the full code to separate all sheet to different workbooks and save them.

Sub CreateWorkbooks() Dim WB As Workbook Dim WS As Worksheet For Each WS In Worksheets    WS.Copy    Set WB = ActiveWorkbook    WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52    WB.Close Next WS End Sub

Here is the result after running above macro. As you can see three files have created with original sheet names.

Next I will explain about this code in detail. First we define our variables

Dim WB As Workbook Dim WS As Worksheet

Next we need to loop through all the sheets of our workbook.

For Each WS In Worksheets Next WS

As you can see there are few other lines between above two lines. So those commands will be executed for each and every worksheet. Following commands will create a new workbook from each sheet.

WS.Copy Set WB = ActiveWorkbook

Now let's take a closer look at the following line.

WB.SaveAs ThisWorkbook.Path & "\" & WS.Name, FileFormat:=52

What this line does is, it save each workbook in .xlsx format in the folder where we have our original file. ThisWorkbook.Path gives the location of the folder. And WS.Name set the name of the workbook. So here each file is saved with it’s original sheet name. And if you want to save the files in a different location you can replace the ThisWorkbook.Path with desired folder path. Here is an example.

WB.SaveAs "C:\Users\EVS\Documents\" & WS.Name, FileFormat:=52

Also FileFormat number specifies the format when saving the file. Here is the list of excel file formats.

XlFileFormat Enumeration (Excel)

And following line is used to close each newly created workbook.

WB.Close

How to Create a New Workbook from Existing Excel Sheet in Excel VBA

In this post I will share a quick tip with you. Sometimes we need to create a new workbook from existing worksheet in Excel VBA. This existing worksheet can be active worksheet or any other worksheet. Assume we have a workbook like this.

This workbook contains 3 sheets. Think we need to create a new workbook from “Sheet3”. We can do that using following code.

Sub CreateNewWorkbook() Dim WB As Workbook Dim WS As Worksheet Set WS = Worksheets("Sheet3") WS.Copy Set WB = ActiveWorkbook End Sub

If you run above macro you will notice a new workbook created from Sheet3 like this.

Now we can see two workbooks in taskbar. Our original workbook and the newly created one.

And if you want to create a new workbook from active sheet you can use below code.

Sub CreateNewWorkbookFromActiveSheet() Dim WB As Workbook Dim WS As Worksheet Set WS = ActiveSheet WS.Copy Set WB = ActiveWorkbook End Sub

If you want to learn how to create workbooks from each and every sheet of the workbook and save them to a desired folder, then check this post.

Create new workbooks from each and every worksheet in your workbook

How to Sum Values in Excel

There are many ways to sum values in Excel. If you want you can directly enter the values you want to sum like this.

Also you can select a cell and then type in the formula bar too. Because if the cell is small then you can’t see all the values you entered to the cell. But formula bar has lot more space. So you don’t need to resize cells when enter long formulas.

And if you want you can enter the cell addresses of the values you want to sum instead of directly entering the values. Using this method you can change the values in the cells and get the sum instantly without doing any change to the formula.

Actually you don’t need to type the cell addresses manually. You can select the cells using mouse while pressing + key from the keyboard.

Also you can use inbuilt function in Excel to sum values in ranges. If this is a continuous range you can use something like this.

Here again, you don’t need to type the ranges manually. You can first type =Sum(
Then you can select the range using mouse. So the range will be inserted automatically to the formula. Finally you can close the bracket and press enter.

And if it is not a continuous range you can use a formula like this.

Paste Clipboard Content to Excel Sheet Using VBA

Have you ever needed to paste clipboard content to an excel sheet. Content in the clipboard may have copied from a web page, software, word file or text file etc. And content may be in any form.  It may be a text, table, image and so forth. So in this post I will teach you how to do this using VBA.

Method 1

In this method, first you need to add reference to Microsoft Forms 2.0 Object Library. To do that go to VBA editor and click Tools menu and then click on References.

Then put a tick to Microsoft Forms 2.0 Object Library. However you will notice that Excel application automatically add reference to that library if you add form to your project.

Then add this code to a module. In this code, clipboard content is assigned to the SText variable. So at the end you can paste it to the place where you need. In this example content is pasted to the B2 cell of the activesheet.

Sub PasteToExcelFromClipboard() Dim DataObj As MSForms.DataObject Set DataObj = New MSForms.DataObject DataObj.GetFromClipboard SText = DataObj.GetText(1) ActiveSheet.Range("B2").Value = SText End Sub

However this method will not work if you have an image in the clipboard. So if you want to deal with images then you can use this second method.

Method 2

Actually in this method we are using a very simple theory. We know that Ctrl + v is the shortcut keys to paste anything to the Excel sheet. So instead of doing this manually we can give that command through VBA like this.

Sub PasteToExcelFromClipboard_SendKeyMethod() activesheet. Range("B2").Select SendKeys "^v" End Sub

First we select the B2 cell of the active sheet. Then we give the paste command using SendKeys method.  Using this method you can even paste images, shapes and tables from the clipboard too.

How to Set the Location, Width and Height of an Inserted Image in Excel VBA

In this post I will explain how to set location, height and width of an inserted image. So this is the image I’m going to insert to the Excel sheet.

And this is the “Details” tab of the “Properties” window of that image. As you can see height and width of the image are 1000 and 1500 pixels respectively.

So if we need we can easily insert the image using following code.

Sub InsertPicture_Example1() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath) End Sub

Then picture will be inserted at active cell like this.

But sometimes we need to insert the image to a specific location of the sheet. And we may need to change the width and height to suit with the available space in the Excel sheet. So we can use following properties to change the location and size of the image to suit with our requirements.

LockAspectRatio
Height
Width
Top
Left

LockAspectRatio Controls the width: height ratio of the inserted image.  So if it is true, width: height ratio of the inserted image will be equals to the width: height ratio of the original image. If it is false, ratio of the inserted image will be different.  Height and Width defines the height and width of the inserted image respectively.  Top define vertical location of the top left corner of the image. And Left defines horizontal location of the top left corner of the image. We can give the location using row numbers and column numbers.

So following code will insert the image to D2 cell.  And height will be 200 pixels.  As LockAspectRatio set to true, excel will automatically calculate the width to comply with original image.

Sub InsertPicture_Example2() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoTrue     .Height = 200     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

Image will be inserted like this

And here is our next example.  In this example, image will be inserted to D2 cell of the active sheet.  But width: height ratio will not equal to the ratio of the original image because we have set LockAspectRatio to false. And we have given specific height and width.

Sub InsertPicture_Example3() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoFalse     .Height = 200     .Width = 450     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

So the image will be inserted like this

Here is another example.  Actually there's a mistake in this code. But I’m putting it here to show you how Excel application works if we use properties incorrectly. In this code we have set LockAspectRatio to true but after that we have given both height and width values.

Sub InsertPicture_Example4() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoTrue     .Height = 200     .Width = 450     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

So when the code is executed image size will be altered keeping the original width: height ratio. You will see how it works if you use debug -> step into method.

At the end image will be inserted with width of 450 pixels. And height will be altered to comply with original ratio. So the image will inserted like this.

In above examples we gave height and width in pixels.  But sometimes we need to assign the width and height in centimeters. We can use Application.CentimetersToPoints to do that. So the following code will insert the image to D2 cell of the active sheet. Height of the image will be 5cm. And width of the image will be 7.1cm

Sub InsertPicture_Example5() Dim AddresPath As String     AddresPath = "C:\Users\EVS\Desktop\Setting Picture Properties\Wooden Car.JPG"     Set myPicture = ActiveSheet.Pictures.Insert(AddresPath)     'Set the location, width and height With myPicture     .ShapeRange.LockAspectRatio = msoFalse     .Height = Application.CentimetersToPoints(5)     .Width = Application.CentimetersToPoints(7.1)     .Top = Rows(2).Top     .Left = Columns(4).Left End With End Sub

So if you print the Excel sheet you will notice that image will printed in given size.