VBA example - Excel 2007: automate work


VBA code to automate work

In this VBA example, we would like to write VBA code to automate work in Excel. When the Excel sheet is active, the current font name and font size will be changed to Khmer OS and 20 automatically. To automate work in Excel, the SendKeys command is used. The SendKeys command has the following syntax:
    Application.SendKeys "Key",[Wait] or just SendKeys "key",[Wait]
    -The Key is a key or combination of keys to be sent. For example, if you want to send key "F" to Excel Application, you can write: SendKeys "F".
    -In case that you want to combine the key with Shift, Ctrl, or Alt, you need to use +(Shift), ^(Ctrl), and %(Alt). For example, to send key Ctrl+O, you can write: SendKeys "^o".
   -The Wait parameter is optional. Its value is True or False. If you specify True, the program will wait before sending your key.
   -For special keys such as ENTER, BACKSPACE, or Arrow keys, you need to place it in braces. For example, to send the ENTER key, you would write:
SendKeys "{ENTER}

To run this VBA example code,  you will need to copy and paste the VBA code(below) to the  block of Worksheet_Activate() procedure.

VBA for Excel: VBA example code to change font name and size automatically

VBA code for this example:


Private Sub Worksheet_Activate()
  AutomateKey

End Sub
Sub AutomateKey()

  SendKeys "%h" 'select Home tab
  SendKeys "ff" 'select font box
  SendKeys "{DOWN}" 'go down
  SendKeys "Khmer OS" 'select font name
  SendKeys "{ENTER}" 'enter
  SendKeys "%h" 'select Home tab
  SendKeys "fs" 'select font size box
  SendKeys "20" 'type 20
  SendKeys "{ENTER}" 'enter


End Sub




Comments

Comments

Name:
Comment:
Enter the text:
CAPTCHA image




This website intents to provide free and high quality tutorials, examples, exercises and solutions, questions and answers of programming and scripting languages:
C, C++, C#, Java, VB.NET, Python, VBA,PHP & Mysql, SQL, JSP, ASP.NET,HTML, CSS, JQuery, JavaScript and other applications such as MS Excel, MS Access, and MS Word. However, we don't guarantee all things of the web are accurate. If you find any error, please report it then we will take actions to correct it as soon as possible.