Tag: value

What is the syntax to see if a checkbox is checked?

 <userform name>.<checkbox name>.Value
'example
If formHome.chkActiveOnly.Value = True Then
    MsgBox "User has checked the box"
Else
    MsgBox "User has not checked the box"
End If

 

Advertisements

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 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 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

 

 

What is the syntax to use/set data in another sheet?

'to retrieve data
Worksheets("<sheet name>").Range("<your range>").Value
Worksheets("<sheet name>").Cells(<row number>, <column number>).Value

'to set data
Worksheets("<sheet name>").Range("<your range>") = <your value/data>
Worksheets("<sheet name>").Cells(<row number>, <column number>).Value = <your value/data>
'examples
Worksheets("Example").Range("A1:A4") = "hello"
Worksheets("Example").Range("D1") = "world"
Worksheets("Example").Cells(2, 4).Value = "goodbye"

Capture.PNG

What is the syntax for a function to return a value?

Function <function name> (<parameter name> As <data type>)
     'parameters are optional
     <function name> = <your code/calculations here>
End Function
'examples
Function TotalPay (hrs As Integer)
     TotalPay = hrs*12
End Function

Public Function returnOne() As Integer
     returnOne = 1
End Function