VBA-Excel: Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet

  Download Link: Multi Sheets Objective: This tool is very useful. Many times we encounter a situation where we need to maintain a report on the daily basis (For weekdays). We manually create a worksheet for each day and copy the template from previous sheets and edit it. This tool will automate all the manual … Read more VBA-Excel: Add Worksheets For All The Given Dates Except Weekends and Copy The Common Template In Each Worksheet

VBA-Excel: Create or Add Worksheets at the Run time.

Objective: This article will teach you “how to add worksheets in excel workbook at the run time”. Important Command: Workbook. Worksheets.Add().Name = “NameYourSheet” Complete Code: Sub Sumit() Dim mainWorkBook As Workbook Set mainWorkBook = ActiveWorkbook mainWorkBook.Worksheets.Add().Name = “Sumit” End Sub Output:   Also Read: Create worksheets with Names in Specific Format/Pattern. Add Worksheets For All … Read more VBA-Excel: Create or Add Worksheets at the Run time.

Excel-VBA : Open a Excel File using Another Excel File using Browse Option.

This tutorial will teach you how to Open a Excel File using Another Excel File using Browse Option. We will be using Application.GetOpenFile() function to accomplish this.

NOTE: You can define any type file you want to , just set the proper filter.

FileFilter:=”Excel Files *.xls* (*.xls*) – to open Excel files

FileFilter:=”Excel Files *.txt* (*.txt*) – To open Text File

NOTE :Do not provide any filter if you want to select any type of file as attachment

Complete Code:

Read moreExcel-VBA : Open a Excel File using Another Excel File using Browse Option.

Excel-VBA : Insert Multiple Images from a Folder to Excel Cells

Say you have many images in a folder and you want to insert all these images in your excel work book, one image in one cell. You can do it manually, insert and resize the image and drag it to the particular cell, but think of a scenario where you have more than 100 images or may be more. I am sure you don’t want to do it manually. IF you are looking for solutions for problems like this, this is the tutorial for you.

This tutorial will teach you about how to insert multiple images from a folder into your excel.

Steps:

Excel-VBA : Range – Lock/Unlock Specific Cells

There are two different cases you might deal with,

  • Lock only few specific cells and rest of the cells in the work sheet are not locked(means you can edit them)
  • Lock the Enitre worksheet except few cells

Lock only few specific cells and rest of the cells in the work sheet are not locked

Steps:

  • Lock the selected cells
  • Provide the password

Note: If you wont provide the password then your cells will not be locked.

Read moreExcel-VBA : Range – Lock/Unlock Specific Cells

VBA-Excel: User Forms

User Forms are one of the very important features in Excel Macro. It provides and an interface to the user for interaction with the workbook. It has many controls which makes user’s life easy for entering the data from excel in a specific manner or reading the particular data from the excel.

Getting Started : Create a Blank User Form

  1. To open or create a new user form, follow the steps below
  2. Open an Excel file
  3. Press “Alt+F11” to open the Microsoft Visual Basic Editor.
  4. Make Project Explorer and Property Window visible from “view menu” (If not already visible)
  5. Select the Project from the Project Explorer.
  6. Form “Insert” menu, select “User Form
  7. Read moreVBA-Excel: User Forms

VBA-Excel: Putting Text In The Windows Clipboard

For working with Windows Clipboard you need DataObject, the object in MSForms library. It provides support for text-string.
For that you must add the reference “Microsoft Forms 2.0 Object Library”
How to add “Microsoft Forms 2.0 Object Library”
Now, For putting text in the Clipboard, Follow the below steps.
Steps:
•    Initialize the Data Object, the type of MSForms.DataObject
•    Create a String
•    Set the String into Data Object using SetText() method.
•    Put the data in ClipBoard using PutInClipboard

Read moreVBA-Excel: Putting Text In The Windows Clipboard

VBA-Excel: Reference Libraries in Excel WorkBook.

There are set of built in libraries available in excel, few of them are default to your code and rest are optional to be added. These libraries contain various built in methods, objects and properties for these methods and objects. These libraries act same as namespaces in .Net, packages in Java

Below are the steps for adding reference libraries to y

our Excel workbook

  1. Open Excel workbook and press Alt + F11 to get Visual Basic Editor (VBE)
  2. Go to the Tools menu and select References from the drop down menu.
  3. References- VBAProject” dialog will appear.
Reference Libraries
Reference Libraries

Read moreVBA-Excel: Reference Libraries in Excel WorkBook.

WorkBooks and WorkSheets

Workbook->WorkSheet->Column->Row
WorkBook stays at the top of the hierarchy. If we want to retrieve the value from a cell then we have to follow the hierarchy to reach to that particular cell. The Entire Excel file is known as WorkBook and one WorkBook can contain many sheets, or to be more precise, many WorkSheets.
Note: While saving the Excel File , Save it as “Macro Enabled WorkBook” (Select Excel Macro Enabled WorkBook from the Save as type drop down in Save As Dialog box  ( FileName.xlsm))

Excel-VBA-MACRO-Saving in .xlsm
Excel-VBA-MACRO-Saving in .xlsm

Read moreWorkBooks and WorkSheets

Password Protection to Your Macro Code

In earlier sessions we saw that how we can write code, generate code and customize it but at the same time it’s very essential to protect it. Excel provides an option to make our Code password protected.

In Next few steps we will see how to make our macro secure.

Step 1:

 In VB Editor, right click on the “VBAProject(Book1)” in Project window and select “VBA Project Properties..”

Excel-VBA-MACRO - Password Protection To Macro - 1
Excel-VBA-MACRO – Password Protection To Macro – 1

“Project Properties” window will be displayed.

Read morePassword Protection to Your Macro Code

Add Tools to Quick Access Tool Bar(Excel Top Bar)

You can also add the “Commands” or “Tools” to “Quick Access Tool Bar” (top nav bar) in your excel sheet and it will be placed next to “Save” or “undo” icon in the excel sheet and their fore it will be easy for you to use it, it will be like shortcut.
Below are the following steps you need to follow to add commands to your excel “Quick Access Tool Bar”
Main Menu -> Excel Options

Excel-VBA-MACRO-Add Tool or command to excel - 1
Excel-VBA-MACRO-Add Tool or command to excel – 1

Read moreAdd Tools to Quick Access Tool Bar(Excel Top Bar)

Excel Macro – Visual Basic Editor

The first question will be where do we write the code , Open the Excel and then press “Alt+F11” and you will get the another window named as “Microsoft Visual Basic” and press “Alt+F11” and you will get back to Excel window.

VBA Editor has 3 main windows:

  1. Project Window
  2. Code Window
  3. Properties Window

Project window and Properties window generally appears on the left side of the Editor. You can close them any time you want and to get them back all you need to do is

  • For Project Window : View-> Project Explorer ( Shortcut : Ctrl+R)
  • For Properties Window : View->Properties Window ( Shortcut : F4)

 

Read moreExcel Macro – Visual Basic Editor

VBA Codes – Record Macro

 

There are two most common ways to write VBA codes,  they are-

  • Record the MACRO.
  • Write it in VBA Editor from scratch.

Obviously it’s easy to record the Macro and use it but there are certain disadvantages when you record the Macro like size of the script goes huge which makes it bit difficult to customize it if needed, When we have to deal with the dynamic contents like getting the search results from Google, in such case its recommended to write the VBA.

1. Record a macro:

Recording a macro is one of the powerful feature in Excel, It will record every action done by the user and it will automatically generate the code for the user and user gets the option to create a shortcut for the Macro so the next time when user wants to

Read moreVBA Codes – Record Macro