What is the syntax to open a userform?

<userform name>.Show
'example
frmHome.Show

 

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

 

What is the syntax to load multiple images at once?

Dim cntrl as Control
For Each cntrl In <userformname>.Controls 'goes through all controls in form
   If TypeOf cntrl Is Image Then 'if control is image
      cntrl.Picture = LoadPicture(<picture's filepath here>)
   End If
Next 'moves onto next control
'example - updates the tiles (which are images) in a connect-3 game
'there are 90 image controls, all with naming convention: r<row>c<column>
'(ex) r01c01, r01c02, r01c03...r09c10
'there are 7 random colours a tile can be, the random number is stored in a sheet

Private Sub updateDisplay() 'call this procedure whenver display wanted to update
Dim cntrl As Control
Dim rr, cc As Integer 'for rows and columns
Dim strPath(7) As String

strPath(0) = "C:\Users\Documents\connect3\images\Yellow.jpg"
strPath(1) = "C:\Users\Documents\connect3\images\Green.jpg"
strPath(2) = "C:\Users\Documents\connect3\images\Pink.jpg"
strPath(3) = "C:\Users\Documents\connect3\images\White.jpg"
strPath(4) = "C:\Users\Documents\connect3\images\Red.jpg"
strPath(5) = "C:\Users\Documents\connect3\images\Cyan.jpg"
strPath(6) = "C:\Users\Documents\connect3\images\Orange.jpg"

For Each cntrl In frmConnect.Controls
   If TypeOf cntrl Is Image Then
      'cntrl.Name is in form r01c01, r01c02...r09c10
      rr = Int(Mid(cntrl.Name, 2, 2)) 'returns 3 if r03c04
      cc = Int(Mid(cntrl.Name, 5, 2)) 'returns 4 if r03c04
      cntrl.Picture = LoadPicture(strPath(Cells(rr, cc).Value))
      rr = rr + 1
      cc = cc + 1
   End If
Next
End Sub

retro