What is the syntax to add to a listbox?

<listbox name>.AddItem <value to be added>

'example - userform called formHome exists
formHome.lstGuests.AddItem "Tristan Smith"

 

What is the syntax to load an image?

<image control name>.Picture = LoadPicture(<file path in string form>)

'examples
profileImg.Picture = LoadPicture("C:\Users\john\Documents\johnsface.jpg")

strPath = "C:\Users\bob\Pictures\Landscapes\picoftrees.jpg"
image1.Picture = LoadPicture(strPath)

 

Helpful Tips

• To add an Image Control, directly drag an Image from the Toolbox menu onto your UserForm.
• To change the name of an Image Control, go to the Properties window in the UserForm Editor and change the name under field “(Name)”.

Toolbox Window, Image Control highlighted

Properties Window, Name field pops up first

• If you do not see your Toolbox or Properties window, go to the top under View and toggle your visibility settings
• To find the string filepath for your image, you may find this article helpful: How do I find the filepath for a folder?

What is the syntax to add a calendar to a userform?

VBA IDE > View > Toolbox > Right-click on Toolbox window > Additional Controls > check Microsoft MonthView Control 6.0 (SP6) > In Toolbox, select MonthView control > drag calendar onto your userform

What is the syntax to create a folder?

Dim FSo
Dim sFolder As String
sFolder = “<filepath for the folder you want to create>”
Set FSO = CreateObject(“Scripting.FileSystemObject”)

If Not FSO.FolderExists(sFolder)Then
   FSO.CreateFolder(sFolder)
   MsgBox(“Folder Created”)
Else
   MsgBox(“Folder already Exists”)
End If

To learn how to find a folder’s filepath, click here.

What is the syntax to add a hyperlink to cell?

Sheets(“<sheet name>”).Hyperlinks.Add Range(“<cell reference>”), “<http://www.yourwebsite.com>”

'examples
Sheets(“resourceSheet”).Hyperlinks.Add Range(“B2”), “http://www.mysyntaxvba.wordpress.com”
ActiveSheet.Hyperlinks.Add Range(“B2”), “http://www.mysyntaxvba.wordpress.com”

Default formatting for hyperlinks on Excel is a blue text with a blue underline.

What is the syntax to create a new workbook?

Workbooks.Add
ActiveWorkbook.SaveAs “<C:\insertYourFilePathHere\<name for your new workbook>.xls>”

‘example that creates a new workbook called myWorkbook in the Downloads Folder
Workbooks.Add
ActiveWorkbook.SaveAs "C:\Users\glestrade\Downloads\myWorkbook.xls"

To find a file path for a file, click here.

What is the syntax to insert a row?

Range(“<row reference>”).EntireRow.Insert

‘example
Range(“3:6”).EntireRow.Insert ‘inserts 4 rows from row 3

What is the syntax to add/delete comments?

‘add comments
Range(“<cell reference>”).AddComment
Range(“<cell reference>”).Comment.Text (“<your text here>”)

'deletes comments
Range(“<cell reference>”).ClearComments ‘clear comments from a specific range
Cells.ClearComments ‘clear comments from a worksheet

 

 

What is the syntax for adding a worksheet?

'to add a sheet at the very beginning of the Sheets tab
Worksheets.Add Before:=Worksheets(1)

'to add a sheet at the very beginning of the Sheets tab
Dim shtLast As Worksheet
Set shtLast = .Worksheets(.Worksheets.Count)
Set shtNew = Worksheets.Add(After:=shtLast)

'to add multiple sheets before the active sheet
ThisWorkbook.Worksheets.Add Count:=<number of sheets you want to add>

'to add a new sheet before the current active sheet
Dim sht as Worksheet
Set sht = ThisWorkbook.Worksheets.Add
sht.Name = "<name of new worksheet>"