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

 

What is the syntax to load an image?

<image control name>.Picture = LoadPicture(<file path in string form>)
'examples
profileImg.Picture = LoadPicture("C:\Users\john\Documents\johnsface.jpg")

strPath = "C:\Users\bob\Pictures\Landscapes\picoftrees.jpg"
image1.Picture = LoadPicture(strPath)

 

Helpful Tips

  • To add an Image Control, directly drag an Image from the Toolbox menu onto your UserForm.
  • To change the name of an Image Control, go to the Properties window in the UserForm Editor and change the name under field “(Name)”.
  • If you do not see your Toolbox or Properties window, go to the top under View and toggle your visibility settings
  • To find the string filepath for your image, you may find this article helpful: How do I find the filepath for a folder?

Tips for User Interface Design [updating]

  1. Use simple dialog understandable by your target user (ie) no jargon
  2. Rely little on user memory (ex) lesser steps for user to memorize/follow
  3. Output meaningful error messages (ex) when error happens, tell them using normal english vs something like “error code 1035”
  4. Avoid limitless choices (ex) Give them a limit of choices as often as possible, vs an open text field that could produce all sorts of data mismatch errors
  5. Always have an exit (ie) never put user in a place where they cannot go back or get out
  6. Exploit consistency (ie) familiarity equates speedy learning
  7. Provide continuous feedback (ex) instead of just a loading bar, have a label underneath that tells the user what step the computer is on
  8. Purpose (not beauty) dictates design (ex) look at how ugly but popular sites like Reddit, Craigslist are

What is the syntax to open a userform?

Put the code below in a separate module from that of the userform. A common choice for users is the proceduce of Workbook_Open() under module ThisWorkbook.

Private Sub Workbook_Open()
    <user form name>.Show
End Sub

 

Do not use the load method, as this just loads the form but does not make it visible. Besides, Show option automatically loads it anyway.

Load <user form name>