How to get windows login user name using Excel VBA ?

There might be a requirement, you have built a VBA macro or excel vba tool and it will be used by a group of people or different teams. While, once you have delivered your macro or tool, you need to track the usability. In order to track it, we will need how many users are using your macro or tool, how frequently they are using your macro or tool, in this case we will need date time stamps too.

We will first learn how to get the login user name, whoever is the current logged in user in the system. We will try this first in excel sheet and once we know, we will write a macro which will create a text file and write the usage details on every execution or opening of the file.

There is inbuilt function in Excel vba to get user name which is : Environ(“UserName”)

Below is the vba code, which we will use to get logged in user id:

–Code start

Sub UserName()

‘declare GetName as string variable, we will use this to assign the login id
Dim UserName As String

‘assign the user id to our variable GetName
UserName = Environ(“username”)

‘write a message box code to display a pop-up message to show the line mentioned below, with the logged in user name
MsgBox “Logged in User Name is : ” & UserName

End Sub

–Code End

Now we can test and run the code, we will receive a msgbox screenshot as shown below.


Now that we have tested the above code. We now know how to get the logged in or current user id. We will now write a macro which will create a log using the above code.

To create a log we will have to create a macro, which will create a text file and keep writing lines, whenever the macro is used. For this we will create a sub procedure, which will run along with above code.

–Code start

Sub UserName()

Dim UserName As String

UserName = Environ(“username”)

MsgBox “Logged in User Name is : ” & UserName

‘I have removed all green comments from above code, rest is same. The only change is below line, to run as sub procedure.
Logtracker UserName & “|UserNameMacro|” & Date & ” ” & Time

‘Below are details for above line:
‘Logtracker = sub procedure name
‘UserName = the variable with log in user id
‘ UserNameMacro = the macro name, to track
‘Date = will provide current date
‘ Time = will provide current time
‘ we are using & to concatenate and | as separator

End Sub

–Code end

Below is the sub procedure code to create text file and keep appending lines, whenever we run the above macro.

–Code start

Sub Logtracker(LogMessage As String)

‘declare the path to save file and create log
Const LogFileName As String = “C:\Users\xyz\Desktop\tracker_log.txt”

‘declare FileNum as integer, which will be used check Log File
Dim FileNum As Integer

‘ assign next file number, on every run
FileNum = FreeFile

‘Below code creates the file if it doesn’t exist. If exist then opens the file to write the log
Open LogFileName For Append As #FileNum

‘Below code will write our log message and append at the end in text file
Print #FileNum, LogMessage

‘Below code will close the file
Close #FileNum ‘ close the file

End Sub

–Code end

Now try to run the macro UserName() and test the working of above codes. There will be a text file created and saved on the path we have mentioned, in above example it is on desktop. Below is the screenshot of the text file tracker_log.txt, for reference.

prassann.daphal|UserNameMacro|8/22/2018 9:30:44 AM
prassann.daphal|UserNameMacro|8/22/2018 9:30:47 AM
prassann.daphal|UserNameMacro|8/22/2018 9:34:41 AM
prassann.daphal|UserNameMacro|8/22/2018 9:34:44 AM
prassann.daphal|UserNameMacro|8/22/2018 9:34:55 AM
prassann.daphal|UserNameMacro|8/22/2018 9:37:11 AM
prassann.daphal|UserNameMacro|8/22/2018 9:37:25 AM

Thank you for reading ! Happy Learning !

Please write feedback comments if you like my posts and also let me know if you want me to write any specific articles which want to learn or need help on.

How to deactivate Alerts in your Macro code ?

This is for people who write the automation macros in VBA code to execute multiple tasks. We will talk here about two examples where you will need to deactivate the alerts.

Example 1: We have created a web crawler macro, which extracts or downloads data from website. Here every time you refresh or land on the website i will show some pop-up messages or alerts or shows different screens through which the macro is executing the tasks.

Example 2: we have a macro which is running on excel application. which opens different worksheets to process the data. During the process we can see multiple sheets activated and the programmed tasks running on screen.

Let’s say you don’t want to see all what is happening on screen or in case you are creating the macro for some end user / customer and that user doesn’t want to know what is happening at the background, he just needs output. In this case we can just use 2 lines of code, which will help use to disable the Alerts and any Screen updates, till the macro completes all it’s tasks.

I have written below sample code, which can be used to check the working of the disable alerts and disable screen updating functions. I am using the example to 2 from above (running macro in Excel Workbook) for ease of understanding. This code starts writing 1 to 2000 by activating each sheet in the workbook using the FOR LOOP code.

Below are the only two lines of code, which work to disable Display Alerts and Screen updating.

Application.DisplayAlerts = False
Application.ScreenUpdating = False

To test the below code follow below steps:

  1. Open new excel workbook
  2. Pres Alt + F11, to open the VBA window
  3. Copy and past the below code from start to end
  4. Run the code. You will observe the screen changing from Sheet 1 activation to Sheet 3 and the numbers written from 1 to 2000 on each screen
  5. Now to test the display alerts and screen updating remove the below commented code and run again.
    code with comments
    ‘Application.DisplayAlerts = False
    ‘Application.ScreenUpdating = FalseCode without comments
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
  6. Now you will not see any movements on the screen. Instead you will just see the final message box saying Your macro is completed !

–Code start

Sub deactivateAlerts()

‘ remove the ‘ (comment) to activate below code
‘Application.DisplayAlerts = False

‘Application.ScreenUpdating = False

For i = 1 To 2000
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
Next i

For i = 1 To 2000
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
Next i

For i = 1 To 2000
ActiveCell.Value = i
ActiveCell.Offset(1, 0).Select
Next i

‘Please add the below code before ending the macro. Else it will keep the alerts and screen updating disabled for all other codes too.
Application.DisplayAlerts = True
Application.ScreenUpdating = True

MsgBox “Your macro is completed !”

End Sub

–Code end

deactivate alerts

Thank you for reading. Happy learning !

Please write comments, if you like

Please write comments if you like my posts and also write if you want me to post solutions on any problems which you face on excel.

How to resize charts using Excel VBA ?

There are lot of charts in excel in different width and length sizes. I need to resize them to same size, in order to use them in specific document, presentation or on website. To resize charts, i will have to select each chart and if there are more than 6 charts, it will become difficult to select charts by scrolling or keeping the Ctrl key pressed during selection.

Also, if i have charts in multiple sheets, i will have follow the below steps for each sheet, which will be really a time consuming task.

  1. Select all charts on sheet, by first selecting one chart and then select other using mouse click by keeping Ctrl key pressed
  2. Go to chart tools or Drawing tools(in case multiple charts are selected) menu
  3. Click format
  4. Check on the size menu on the right end of menu bar
  5. Change the shape height and shape width to the required size
  6. Done

resize charts

Follow above steps on each sheet independently.

Instead of spending time on each sheet, we can write a VBA macro which can be used to resize all charts at once, on all sheets.

–Code start

Sub ResizeCharts()

‘ Define the chart variable as chartobject, which will identify the charts in the activesheet
Dim chart As ChartObject

‘Write FOR NEXT loop to select each chart and rename in active sheet
For Each chart In ActiveSheet.ChartObjects

‘Using With and End with method to execute the required code, till the FOR NEXT loop is completed
With chart.Parent

‘ change the numbers in the below brackets (2) to change the size of the chart. The number reflect the chart size in inches
chart.Height = Application.InchesToPoints(2)
chart.Width = Application.InchesToPoints(4)

End With


MsgBox “All charts in the active sheet are resized !”

End Sub

–Code end

Using the above code we can resize all charts in active sheet to same sizes. If you observe above code, there are two lines which decide the size of charts and we only need to change the numbers between brackets (x). In above code, we have option to resize the size in inches. If you are not comfortable with inches, we can tweak the above code by using centimeters (cm). Observe below two lines of codes to tweak.

‘Inches (InchesToPoints)
chart.Height = Application.InchesToPoints(2)
chart.Width = Application.InchesToPoints(4)

‘Centimeters (CentimetersToPoints)
chart.Height = Application.CentimetersToPoints(6)
chart.Width = Application.CentimetersToPoints(12)

So, now we will be able to use the above code to resize all charts on sheet. If we want to use the above code to resize all charts in all worksheets within a workbook, we can further write a FOR NEXT loop to run for each worksheet and execute the above code for each worksheet. Check this link on how to write FOR NEXT Loop.

Thank you for reading. Happy learning !

Please write comments if you like my posts and also write if you want me to post solutions on any problems which you face on excel.

How to remove hyperlinks in excel sheet using VBA code ?

You must have come across this challenge, when you copy paste some data from websites, from emails or any documents in excel. e.g. if you copy paste the text in excel file, it will be copied as hyperlink, as it is website address. Even if you type email id in excel cell and press enter, the text will turn into hyperlink.

In our example, we are using a sample data to check if our code will remove the hyperlinks.

First Name Last Name email id
Little John
Big Tom
Little Mary
Big Gary

Copy paste the above table in excel file, we can see that email id column has hyperlink, and whenever you will try to select any of the email id column cells, the click will open a new email compose item (if you are using any default connected email application for emails). Below is the screenshot from Microsoft outlook:

new email

To avoid the above action, we will need to remove the hyperlinks and to do it manually, we will need to select all cells, right click each cell and select remove hyperlinks.

remove hyperlinks

In our sample data there are only 4 records and we have structured data. But when we have large data and we need to search and remove hyperlinks, it will become difficult to remove all hyperlinks. Also, it will be a very time consuming task.

To avoid all this, we can just create a small macro, which can be used to removed all hyperlinks within the sheet or entire workbook with just one click of mouse :-).

Check out below vba codes and try them in your excel.

–Code start

Sub removeallhyperlinks()

‘the below code will remove all hyperlinks from the active excel sheet on screen

End Sub

–Code end

That’s it, a single line code to remove all hyperlinks in the worksheet :-).

Thank you for reading. Happy Learning !

How to copy and paste file using Excel VBA ?

This code can help you to achieve different tasks to copy and paste files using the automated process. Below are few examples where this code can be used:

  1. Copy and paste files from one location to other as a backup
  2. Copy and paste processed files from local desktop or drive to Customer location or FTP location
  3. Schedule a copy paste macro, for trigger as per specific requirement of job
  4. Copy paste file, in order to process the copied file and keep original as backup

…and you can write as many, as per your job requirements.

You can directly copy paste the below code in your VBA module and use it by changing few variables. Check out the comments in Green, wherever you would need to tweak the code to get the desired output.

—Code start

Sub Copy_File()

‘ Declare variables we are going to use. All variables are declared as string, as we are going to assign text values to them
Dim filename As String
Dim Source As String
Dim Destination As String

‘Dim means dimension, which we are using to declare the variables

‘Next step will be to set values to our declared variables
filename = “Desert_backup”
Source = “C:\Test\Desert.jpg”
Destination = “C:\Test\Backup\” & filename & “.jpg”

‘We have now set values or assigned values to our variables. In the example above, we are trying to copy a file named Desert.jpg, which is saved in Test folder in my C drive to folder named Backup under Test folder in my C drive. Refer the above path in the code i.e. Source and Destination.

‘Also while doing so, we are renaming the back up file from Desert.jpg to Desert_backup.jpg.

‘Below is the final code, which will copy the file from source to destination, with new name
FileCopy Source, Destination

‘ We have inbuilt FileCopy VBA function to complete the copy paste process. The FileCopy function’s standard script is FileCopy(Source, Destination) which means, this function copies file from one folder or directory to other

‘ Remember if the file you are trying to copy is open, then the macro will give a run-time error. i.e. in order to copy the file, the file needs to be closed.

End Sub

—Code end

Just in case if you are planning to use the code to take the server back up daily or weekly once, so instead of renaming file to _backup, you can just add a timestamp at the end of file name, in order to save the file with the specific date and time stamp of the back up day.

Check below code in which we are using date time stamp to rename and save the file. The only different from above code is the tweak done in the Destination string.

—Code start

Sub Copy_File_dt ()

Declare variables
Dim filename As String
Dim Source As String
Dim Destination As String

‘Set values to variables
filename = “Desert_”
Source = “C:\Test\Desert.jpg”
Destination = “C:\Test\Backup\” & filename & Format(Now(), “mm_dd_yyyy hh mm AMPM”) & “.jpg”

‘Here we have used 2 inbuilt VBA functions Now() and Format. Now() function will get you the current value of date and time. While the Format function helps you to format the date time, in whatever format we need as output

‘Final code for file copy
FileCopy Source, Destination

End Sub

—Code end

In order to run the above code for multiple files copy paste, you will need to tweak the above code, to assign dynamic values to variables and add a Loop code to run till all the files are copied.

You can also get a list of files that you want to copy and also track how many files actually got copied, in order to verify or validate the output. But right now i want to keep this post simple, just to help you understand the simple code, on how to copy paste the file.

Thank you for reading. Happy Learning !

How to use If Else and Case functions in excel VBA ?

To understand how to use If else and case statements, i have create a small program code, which checks your personality based on your birth date numbers. So have fun while learning :-).


Sub checkpersonality()

‘Define all variables we are going to use in below code
Dim bdate As Integer, bmonth As Integer, byear As Integer, total As Integer
Dim calca As Integer, calcb As Integer, calcc As Integer, calcd As Integer, calctotal As Integer
Dim calce As Integer, calcf As Integer, yourbnum As Integer

‘On error, e.g. if incorrect dates are entered the below code will take you to the error message: in the code and exit the execution
On Error GoTo

‘Use InputBox function to collect the day, month and year of the user
bdate = InputBox(“Enter your Day of birth (DD):”)
bmonth = InputBox(“Enter your Month of birth (MM):”)
byear = InputBox(“Enter your Year of birth (YYYY):”)

‘Calculate total from your birth date, learnt this from astrology website 🙂
total = bdate + bmonth + byear

‘below are few more calculations on the number from above total
calca = Left(total, 1)
calcb = Right(Left(total, 2), 1)
calcc = Right(Left(total, 3), 1)
calcd = Right(total, 1)
calctotal = calca + calcb + calcc + calcd

‘below is use of If Else statement to get a final number from all calculations
calctotal >= 10 Then
calce = Left(calctotal, 1)
calcf = Right(calctotal, 1)
yourbnum = calce + calcf
Else: yourbnum = calctotal
End If

‘After all the calculations above, we will get a final number as output. This number will decide what is your personality trait and based on that a message box will pop-up from below 1 to 9 cases.
Select Case yourbnum

Case 1
MsgBox “Your Birth number is ” & yourbnum & ” and You are The originator” & vbCrLf & vbCrLf & “1s are originals. Coming up with new ideas and executing them is natural. Having things their own way is another trait that gets them as being stubborn and arrogant. 1s are extremely honest and do well to learn some diplomacy skills. They like to take the initiative and are often leaders or bosses, as they like to be the best. Being self-employed is definitely helpful for them.” & vbCrLf & vbCrLf & “Lessons to learn: Others ideas might be just as good or better and to stay open minded.” & vbCrLf & vbCrLf & _
“Famous 1s: Tom Hanks, Robert Redford, Hulk Hogan, Carol Burnett, Wynona Judd, Nancy Reagan, Raquel Welch, Prassann Daphal”, vbInformation

Case 2
MsgBox “Your Birth number is ” & yourbnum & ” and You are The peacemaker” & vbCrLf & vbCrLf & “2s are the born diplomats. They are aware of others needs and moods and often think of others before themselves. Naturally analytical and very intuitive they don’t like to be alone. Friendship and companionship is very important and can lead them to be successful in life, but on the other hand they’d rather be alone than be in an uncomfortable relationship. Being naturally shy they should learn to boost their self-esteem and express themselves freely and seize the moment and not put things off.” & vbCrLf & vbCrLf & _
“Famous 2s: President Bill Clinton, Madonna, Whoopi Goldberg, Thomas Edison, Wolfgang Amadeus Mozart”, vbInformation

Case 3
MsgBox “Your Birth number is ” & yourbnum & ” and You are The life of the party” & vbCrLf & vbCrLf & “3s are idealists. They are very creative, social, charming, romantic, and easygoing. They start many things, but don’t always see them through. They like others to be happy and go to great lengths to achieve it. They are very popular and idealistic. They should learn to see the world from a more realistic point of view.” & vbCrLf & vbCrLf & _
“Famous 3s: Alan Alda, Ann Landers, Bill Cosby, Melanie Griffith, Salvador Dali, Jodi Foster”, vbInformation

Case 4
MsgBox “Your Birth number is ” & yourbnum & ” and You are The conservative” & vbCrLf & vbCrLf & “4s are sensible and traditional. They like order and routine. They only act when they fully understand what they are expected to do. They like getting their hands dirty and working hard. They are attracted to the outdoors and feel an affinity with nature. They are prepared to wait and can be stubborn and persistent. They should learn to be more flexible and to be nice to themselves.” & vbCrLf & vbCrLf & _
“Famous 4s: Neil Diamond, Margaret Thatcher, Arnold Schwarzenegger, Tina Turner, Paul Hogan, Oprah Winfrey”, vbInformation

Case 5
MsgBox “Your Birth number is ” & yourbnum & ” and You are The non-conformist” & vbCrLf & vbCrLf & “5s are the explorers. Their natural curiosity, risk taking, and enthusiasm often land them in hot water. They need diversity, and don’t like to be stuck in a rut. The whole world is their school and they see a learning possibility in every situation. The questions never stop. They are well advised to look before they take action and make sure they have all the facts before jumping to conclusions.” & vbCrLf & vbCrLf & _
“Famous 5s: Abraham Lincoln, Charlotte Bronte, Jessica Walter, Vincent Van Gogh, Bette Midler, Helen Keller, Mark Hamil.”, vbInformation

Case 6
MsgBox “Your Birth number is ” & yourbnum & ” and You are The romantic” & vbCrLf & vbCrLf & “6s are idealistic and need to feel useful to be happy. A strong family connection is important to them. Their actions influence their decisions. They have a strong urge to take care of others and to help. They are very loyal and make great teachers. They like art or music. They make loyal friends who take the friendship seriously. 6s should learn to differentiate between what they can change and what they cannot.” & vbCrLf & vbCrLf & _
“Famous 6s: Albert Einstein, Jane Seymour, John Denver, Meryl Streep, Christopher Columbus, Goldie Hawn”, vbInformation

Case 7
MsgBox “Your Birth number is ” & yourbnum & ” and You are The intellectual” & vbCrLf & vbCrLf & “7s are the searchers. Always probing for hidden information they find it difficult to accept things at face value. Emotions don’t sway their decisions. Questioning everything in life, they don’t like to be questioned themselves. They’re never off to a fast start, and their motto is slow and steady wins the race. They come across as philosophers and being very knowledgeable, and sometimes as loners. They are technically inclined and make great researchers uncovering information. They like secrets. They live in their own world and should learn what is acceptable and what not in the world at large.” & vbCrLf & vbCrLf & _
“Famous 7s: William Shakespeare, Lucille Ball, Michael Jackson, Joan Baez, Princess Diana”, vbInformation

Case 8
MsgBox “Your Birth number is ” & yourbnum & ” and You are The big shot” & vbCrLf & vbCrLf & “8s are the problem solvers. They are professional, blunt and to the point, have good judgment and are decisive. They have grand plans and like to live the good life. They take charge of people. They view people objectively. They let you know in no uncertain terms that they are the boss. They should learn to exude their decisions on their own needs rather than on what others want.” & vbCrLf & vbCrLf & _
“Famous 8s: Edgar Cayce, Barbra Streisand, George Harrison, Jane Fonda, Pablo Picasso, Aretha Franklin”, vbInformation

Case 9
MsgBox “Your Birth number is ” & yourbnum & ” and You are The performer” & vbCrLf & vbCrLf & “9s are natural entertainers. They are very caring and generous, giving away their last dollar to help. With their charm, they have no problem making friends and nobody is a stranger to them. They have so many different personalities that people around them have a hard time understanding them. They are like chameleons, ever changing and blending in. They have tremendous luck, but also can suffer from extremes in fortune and mood. To be successful, they need to build a loving foundation.” & vbCrLf & vbCrLf & _
“Famous 9s: Albert Schweitzer, Shirley McLaine, Harrison Ford, Jimmy Carter, Elvis Presley”, vbInformation

Case Else
MsgBox “I didn’t knew this, You are not HUMAN !”, vbCritical
End Select
Exit Sub

‘ you have an option to change the below message, anything that you want to display on error
MsgBox “You entered something wrong !” & vbCrLf & vbCrLf & “You need to enter your Birth Date ?”, vbCritical

End Sub

In above code, you have seen vbCrLf used between text messages. Here Cr means carriage return, while Lf means line feed, usign vbCrLf between text, it will create a new line for next text.

You can also use vbNewLine instead of vbCrLf, more easy to remember :-).

Below are the screen shots of the above program in action.

  1. Open new excel file
  2. Copy paste above code from sub to End sub in new VBA module
  3. Save the file as Personality Test
  4. Press Alt + F8 to run the code
  5. Select checkpersonality macro, if you have multiple codes in same sheet
  6. Run
  7. we will take example of my birth date. 23-01-1984. First input your date of birth in first input box in DD format. In my case it will be 23.
  8. In second input box, enter your birth month in MM format. In my case it will be 01.
  9. In third input box, enter your birth year in YYYY format. In my case it will be 1984.
  10. Based on your birth date, out program will calculate the personality and show the below output.
  11. If you don’t enter anything in the input box or click cancel on input box, then it will show the error message.


Thank you for reading. Happy Learning !

How to write a For Next Loop in Excel VBA ?

Here the term loop means, the line of written code will be repeated till the specific condition is met. In excel VBA there are different ways to run code in loops, in this article we will learn one of the method, which is For Next loop.

First we will write simple code, which will pop-up the message box for numbers 1 to 10.

1. Open new excel file and open the VBA window by pressing Alt + F11 (check for more details on How to use VBA editor and add references to Excel VBA ?)

2. Add the below lines of codes, in any of the vba module:

‘ ForLoop is the name of the macro which will appear when you press ALt + F8 to run macro
Sub ForLoop()

‘Define n variable as integer
Dim n As Integer

‘ Assign the values to variable n
For n = 1 To 10

‘MsgBox will give a pop up message with value assigned to variable n
MsgBox n

‘Next will loop back to start and add 1 to value of n, e.g. during first run of code value of n will be 1 and on execution of the Next, the value of n will become n = n + 1 i.e. n=2

‘once the n value will become 10, the code will exit the execution with End Sub
End Sub

3. Run the code using Alt + F8 and choose ForLoop from the list, if you have multiple macros

4. Below is the screenshot of the third Next statement executed in the above code:


Using the loop we can further write a code, which will help us to provide a message box pop-up to identify odd or even numbers.

Copy paste the below code in the VBA module:

Sub EvenOdd()
For n = 1 To 10
If n Mod 2 = 0 Then
MsgBox n & ” is Even Number”
MsgBox n & ” is Odd Number”
End If
Next n
End Sub

When you will execute the above code, you will get message box pop-up saying n (value of n), if it is even number or odd number. Below is the screen shot references for both even and odd numbers.

odd number      even number

These message box pop-ups will continue till the n variable is set to 10 using For Next loop.

In above code, we have used the combination of For Next and If Else statements. e.g. If n is divisible by 2, then the number will be even, else odd. In this way, the loops can be used in different combinations in VBA code to achieve results.

Thank you for reading. Happy Learning !

How to use Autofilters in Excel VBA ?

If you are a excel vba programmer, you would definitely want to know, that how you can use the Auto filter option using code in macro. As you might be required to provide user with custom filtered data as final output.

We will first have a look at code which will check if the auto filter is already applied in the sheet. If the auto filter already applied then the code will exit (i.e. not execute), else it will apply the filter to the selected or available data within range.

Sub pdCheckAutoFilter()
If Not ActiveSheet.AutoFilterMode Then
End If
End Sub

When executed the above macro will check , if the filters are already applied if yes, then it will exit the code and if not applied then it will filter the available data in current sheet data range.

Now you know how to check and apply the AutoFilter using vba code. Next we will learn how to remove this applied VBA filter.

By just tweaking the above autofilter check and apply code, we can write a code to check and remove Auto Filter

Sub pdCheckRemoveAutoFilter()
If Not ActiveSheet.AutoFilterMode Then
Worksheets(“Sheet1”).AutoFilterMode = False
End If
End Sub

If there is no Autofilter applied to the data, then the above macro will just stop execution. While in case the Autofilter is applied then, the filters will be removed in Sheet1 data.

The above two macros can be used as a sub tasks within a program in order to achieve different outputs as per the requirements.

Thank you for reading. Happy Learning !

How to create a command button and assign macro in Excel VBA ?

  1. Click on the Developer tab View tab.
  2. Click on Insert and Click on Command Button in the ActiveX Controls section
    Developer Tab

This will create a command button within current sheet. Let’s now create a macro and assign it to this command button.

To write a macro within the command button, right click on command button and click view code

view code

3. The Visual Basic Editor will be opened. Place you cursor between

Private Sub CommandButton1_Click()

End Sub

4. For example, add the following code line:

Range(“A1”).Value = “My First Code !”

We just wrote one line code within the command button click event. So whenever you click this command button this code will be executed.

my first code

As per the written code, the command button will write a value in cell A1 as “My First Code !” as shown in the above screenshot.

You can use multiple command buttons to execute multiple repetitive tasks or create a complete form like interface within the sheet.

Thank you for reading. Happy Learning !

How to turn on the Developer Tab in Excel ?

1. Click on the File tab and choose Options. This will open the Excel Options dialog box

file menu    
2. Click Customize Ribbon on the left side of the dialog box

3. Under Choose commands from on the left side of the dialog box, select Popular Commands

4. Under Customize the ribbon on the right side of the dialog box, select Main tabs

5. Search for the Developer and click the check box

6. click Ok. Now you will be able to see the below developer tab activated in Excel Menu

There are many features the developer tab is useful for the excel macro developer user. Developer tab helps us with below listed features:

  1. View excel VBA editor on click of Visual Basic buttonvba screen
  2. View the available macros using Macros button|
    macro window
  3. Record a new macro, in case you are planning to automate a repetitive task
  4. Macro security, to secure your VBA macros and modules for unauthorized edit or view
  5. Add new Add-ins, in order to achieve specific tasks. You can read a separate post on this website on What are Add-ins in Excel ?
  6. Insert form controls or ActiveX controls within Excel sheets

insert devloper          properties

Thank you for reading. Happy Learning !