What is the syntax to change a button’s display text?

 <button name>.Caption = "<new text>"

'example
btnPause.Caption = "Resume"

 

What is the syntax to automatically run code when multi-page page changes?

Private Sub <multipage name>_Change()
   If Me.<multipage name>.Value = <pg index under which code should run> Then
      <code to be run when page is activated>
   End If
End Sub

'example for multipage named "mtp"
Private Sub mtp_Change() 'called whenever active page in multipage changes
   If Me.mtp.Value = 1 Then 'if user opens the "Show Guest List" page
        Call loadGuestLists 'automatically runs this code
   End If
End Sub

What is the syntax to change the current directory?

ChDir (<directory path>)

'examples - changes current directory to Desktop folder
ChDir "C:\UserA\Desktop"

 

 

What is the syntax to run code every time a cell value changes?

'place code in a sheet under Microsoft Excel Objects
Private Sub Worksheet_Change(ByVal Target As Range)
     If target.Row = Range("<range of cell>").Row And target.Column = Range("<range of cells>").Column Then
        <code to be run here>
     End If
End Sub

'example
Private Sub Worksheet_Change(ByVal Target As Range)
     If target.Row = Range("A1").Row And target.Column = Range("A1").Column Then
        MsgBox("The value of A1 has changed")
     End If
End Sub

 

 

What is the syntax to cast to an integer?

<variable holding integer> = Int(<value that needs to be cast>)

'examples
MsgBox(Int("001")) 'produces 1
MsgBox(Int(12.1)) 'produces 12

 

 

What is the syntax to change tab colors?

Worksheets(“<sheet’s name>”).Tab.ColorIndex = <color index>

For a color index reference, click here.

 

What is the syntax to change column width?

Columns(“<column letter>”).ColumnWidth = <width in pixels>

Bear in mind that standard column width in Excel is 15.

‘examples
Columns(“B:D”).ColumnWidth = 32

 

What is the syntax to change font color?

'method 1
Cells(<row number>, <column number>).Font.ColorIndex = <color index integer>

'method 2
Range(“<cell reference>”).Font.ColorIndex = <color index integer>

‘examples
Cells(2,4).Font.ColorIndex = 5
Range(“D2”).Font.ColorIndex = 2

 

What is the syntax to change the font size of a row?

Rows(<row number>).Font.Size=<font pt size>

‘examples
Worksheets(“Sheet3”).Rows(5).Font.Size = 14

What is the syntax to change column width?

Columns(“<columns>”).ColumnWidth= <width in pixels>

‘examples
Columns(“D:G”).ColumnWidth = 4
Worksheets(“Sheet3”).Columns(“A”).ColumnWidth=233