What is the syntax to check the value of a textbox?

If <textbox name>.Value = "<value textbox should be>" Then
   <code to be run, provided correct textbox value was entered>
End If
'example
If txtName.Value = "" Then
     Msgbox ("No name was entered!")
End If
Advertisements

What is the syntax to find the minimum between two numbers?

<variable to hold minimum> = WorksheetFunction.Min(<num1>, <num2>)
#example 
minValue= WorksheetFunction.Min(100, Sheets("eg").Cells(1,1).value)

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 count how many times a substring occurs in a string?

<integer variable> = Len(<string>) - Len(Replace(<string>, "<substring>", "")) 
'example
sentence = "Hello, this is a sentence,"
countCommas = Len(sentence) - Len(Replace(sentence, ",", "")) 
msgbox(countCommas) 'output is 2

What is the syntax to exit a procedure?

Exit Sub
#example - exits procedure if something error description happens
Private Sub btnAddNew_Click()
errorStr = errorCheck 'checks for errors ie. missing gaps in textfields

If Not (errorStr = "") Then 'if something wrong with wat user entered
     Exit Sub
End If

End Sub

What is the syntax to read rows in to a list box?

<listbox name>.AddItem Sheets("<sheet name>").Cells(<row>, <column num>).Value & ...etc
#example - reads a list of food entries from a sheet
Private Sub loadListbox()
     lstFoods.Clear
     lastEntryRow = Sheets("Foods").Cells(Sheets("Foods").Rows.Count, "A").End(xlUp).Row 'how many foods are there?

     For i = 2 To lastEntryRow 'adds each food entry row into the listbox
        lineDisplay = Sheets("Foods").Cells(i, 1).Value & Chr(9) & Sheets("Foods").Cells(i, 2).Value
        lstFoods.AddItem lineDisplay 
     Next
End Sub

 

 

What is the syntax to hide Excel and only show userform?

#insert code in ThisWorkbook (under Microsoft Excel Objects in IDE)
Private Sub Workbook_Activate()
     <userform name>.Show
End Sub

#insert code in userform's code
Private Sub UserForm_Initialize()
     Application.Visible = False
End Sub
 
Private Sub UserForm_Terminate()
     Application.Visible = True
End Sub
#example

#in ThisWorkbook object
Private Sub Workbook_Activate()
 frmHome.Show
End Sub

#in userform, frmHome
Private Sub UserForm_Initialize()
 Application.Visible = False
End Sub
 
Private Sub UserForm_Terminate()
 Application.Visible = True
End Sub