Macros

• Macro is a mini program created in excel.
• We use macro in order to perform previous values.

Setting the Developers in Macro:

• In order to get macro settings, we have to enable them manually.
• Below are steps to enable the macro.

• Click on FILE.
• Move on to the Options menu.
• In next tab, open Customize Ribbon.
• Check Developer options. 
• Developer appears in menu bar.

Macro Enable:

• Below are the steps to set macro

• Open Developers
• Open Record Macro.
• Give a name in  Macro Name.
• If needed assign a shortcut key.
• In Store Macro In, select Personal Macro Workbook.
• Then Click OK, Stop Recording displays in the place of Record Macro. 

• Recording is on.
• I want to store below table in record.
• After entering total,  Press stop recording.

• In second sheet I want to create same information, just adding Designation.
• I added, designation manually
• Then I opened Macros. 
• Then select Previously Created Macro Name and click Run.
• What ever data in the previous sheet is displayed. 

Adding Button.

• Button can be added in two methods.
• Selecting Existing Macro.
• Creating New Macro with commands.

• Selecting existing Macro.

• Open Insert in Developers.
• In form controls, select button.
• In Assign Macro Pop Up, Select Desired Macro.
• Then Click OK, Button Appears.
• Button work same as, macro run. 

• New Macro with Commands :

• Create New Macro.
• Stop Recording it.
• Open in Macro and Click Edit Macro. 

• Enter desired code in command sheet.
• The Function which, macro as to be performed.
• Close command Prompt.

• In Below sheet I created macro for switching to next sheet.

• In below, by pressing sheet 2 button it moves to next sheet.

• Using Vlookup in the macros.
• In below sheet I used Vlookup to retrieve data from EmployeData and Sheet 2.
• Pressing Get Contacts, The information in EmployeData is Retrieved.
• Pressing Get Names, The name of the person retrieved form EmployeData,
• Pressing Get Designations, Designation of persons retrieved.

ActiveX Controls:

•  Open Developers > Insert > ActiveXcontrols

Text Box:

• Drag and drop Text box from ActiveX controls.
• Right click on it and select the place to display the value.

Option Button :

• Select Option Button and Place it on sheet.
• Click on Button to write the code for desired functionality.
• I used option button, to select the gender and display it in form.

Combo Box :

• Select Combo Box and Place it on sheet.
• Right click on Combo Box.
• Select Range reference cells.(Linked Range)
• Select Value to be display Page.
• I used combo box to select the date of birth.

Spin :

• Right click on Spin.
• Select Min value and Max value.
• Desired cell to display the value.

Check Box:

• Place check box in desired place.
• Open command prompt
• I used check box to select Marital Status.
• If Unmarried selected,
•  Hide Spouse Cell
• Writes Unmarried in the form.
• N/A in Martial Status Place in form.
• If Married selected,
• Unhide  Spouse Cell.
• Writes Married in the form.
• Removes check mark to the Unmarried.

Listed Box:

• Place list Box in Desired Place.
• Right click on list box and open properties.
• Select Reference range to cell(Link Range).
• Select value display cell(Linked Cell).
• I used the listed box to open select the place.

Label:

• Place label on the sheet.
• Change the background(BackColor).
• Change Label Name in Caption.
• I used label, to alert age and written command to view age limit place.

Image:

• Select Picture control.
• Open the select image from computer, by right click on image controls.
• Used image controls to display company name.\

Command Button :

• Select Button in ActiveX Controls and Drop it on Excel
• Click on Button, To write command to button Functionality.
• In Below sheet I used the button to open hidden sheet and review form.

Example:

• Below created the Form Fill page.
• By submitting it shows the review page, hiding the form fill.