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

 <button name>.Caption = "<new text>"
'example
btnPause.Caption = "Resume"

 

Advertisements

What is the syntax to open a userform in the top-right screen corner?

 Me.StartUpPosition = 0
 Me.Top = Application.Top + 25
 Me.Left = Application.Left + Application.Width - Me.Width - 25
'example - place in UserForm_Activate procedure
Private Sub UserForm_Activate() 'when form opens
   Me.StartUpPosition = 0
   Me.Top = Application.Top + 25
   Me.Left = Application.Left + Application.Width - Me.Width - 25
End Sub

 

What is the syntax to return the index of the a listbox’s selected value?

#'i' is an integer variable that holds the index
 For i = 0 To <list box name>.ListCount - 1
     If <list box name>.Selected(i) = True Then Exit For
 Next i
#example
 For i = 0 To lstGuestList.ListCount - 1
     If lstGuestList.Selected(i) = True Then Exit For
 Next i

 

What is the syntax to call procedures from another module?

Call <module name>.<sub procedure's name>
#example - project has a userform, and a module called Commands
Private Sub btnRefresh_Click() 'located in a userform
 Call Commands.output
End Sub


Sub output() 'located in Commands module
     MsgBox "This code is in a separate module"
End Sub

 

What is the syntax to run a procedure based on a timer?

Sub <procedure name>()
   'code to be run
   Application.OnTime Now + TimeValue("00:00:00"), "<procedure name>"
End Sub
'example
Sub runsEveryTenSeconds()
    If formClock.Visible = False Then Exit Sub

    Call updateClock
    Application.OnTime Now + TimeValue("00:00:10"), "runsEveryTenSeconds"
End Sub

 

What is the syntax to replace a value in a whole workbook?

Dim Sht As Worksheet
For Each Sht In Worksheets
   Sht.Cells.Replace What:=<old value>, Replacement:=<new value>, LookAt:=xlPart, MatchCase:=False
Next
'example - replaces all instances of person's name
'Old first/last name are in A1, B2 (respectively) in Sheet "Names"
Dim Sht As Worksheet
For Each Sht In Worksheets
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 1).Value, Replacement:=txtNameFirst.Value, LookAt:=xlPart, MatchCase:=False
    Sht.Cells.Replace What:=Sheets("MasterNames").Cells(1, 2).Value, Replacement:=txtNameLast.Value, LookAt:=xlPart, MatchCase:=False
Next