Tag: user input

What is the syntax to clear a listbox?

<listbox name>.Clear

'if clearing a userform's listbox from outside the userform
<userform name>.<listbox name>.Clear
'example
formHome.lstOutput.Clear

 

What is the syntax to get the date selected for a calendar?

  1. Add a calendar to your userform (for instructions, click here)
  2. As shown in the screenshot below, select your calendar control (default name is always MonthView1)
  3. Select DateClick, as shown circled in red below
  4. A procedure will be auto-generated, containing a parameter called DateClicked
  5. User’s selected date is automatically contained in variable DateClicked

unnamed.png

What is the syntax to return part of an inputted date?

DatePart (interval, date1, firstdayofwk, firstdayofyr)
‘interval – can be one of the following listed in bullet points; mandatory
‘date1 – mandatory parameter
‘firstdayofweek – can be values 1-7 for Sunday-Saturday, or 0 for system’s version; optional parameter
‘firstdayofyear – really useless optional parameter, just ignore it

Interval values

  • d – day of the year.
  • m – month of the year
  • yyyy -Year
  • w -Weekday
  • ww -week
  • q -quarter
  • h -Hour
  • n -Minute
  • s –Second
‘examples
MsgBox (DatePart("yyyy", #3/22/2030#)) '2030
MsgBox (DatePart("d", #8/17/2060#)) '17
MsgBox (DatePart("q", "2057-01-15")) '1
MsgBox (DatePart("ww", "2031-12-27")) '52

 

 

What is the syntax to use input boxes?

InputBox(prompt, title, default, xpos, ypos, helpfile, context)
‘prompt – message of the input box; mandatory parameter with maximum of 1024 characters
‘default – automatic default text that populates the textbox shown to user; optional parameter
‘XPos – coordinate of where box is horizontally; default is centered
‘YPos – coordinate of where the box appears vertically; default is centered
'example
Dim name As String
name = InputBox("What is your name?", "Name Input", "John Doe")

unnamed (4).jpg

 

What is the syntax to use message boxes?

MsgBox(prompt, buttons, title, helpfile, context)
‘prompt – message displayed in msgbox; mandatory parameter with max length 2014 characters
‘buttons – integer telling VBA which button options to show; optional parameter with default value 0

 

Which button options do I have?

  • 0 vbOKOnly Displays OK button only.
  • 1 vbOKCancel Displays OK and Cancel buttons.
  • 2 vbAbortRetryIgnore Displays Abort, Retry, and Ignore buttons.
  • 3 vbYesNoCancel Displays Yes, No, and Cancel buttons.
  • 4 vbYesNo Displays Yes and No buttons.
  • 5 vbRetryCancel Displays Retry and Cancel buttons.
  • 16 vbCritical Displays Critical Message icon.
  • 32 vbQuestion Displays Warning Query icon.
  • 48 vbExclamation Displays Warning Message icon.
  • 64 vbInformation Displays Information Message icon.
  • 0 vbDefaultButton1 First button is default.
  • 256 vbDefaultButton2 Second button is default.
  • 512 vbDefaultButton3 Third button is default.
  • 768 vbDefaultButton4 Fourth button is default.
  • 0 vbApplicationModal Application modal. The current application will not work until the user responds to the message box.
  • 4096 vbSystemModal System modal. All applications will not work until the user responds to the message box.

 

What are the integer return values that identify which buttons were clicked?

  • 1 – vbOK – OK was clicked
  • 2 – vbCancel – Cancel was clicked
  • 3 – vbAbort – Abort was clicked
  • 4 – vbRetry – Retry was clicked
  • 5 – vbIgnore – Ignore was clicked
  • 6 – vbYes – Yes was clicked
  • 7 – vbNo – No was clicked

 

‘example
Dim userReturn As Integer

userReturn = MsgBox("Do you agree?", 4 + 64 + 256, "Choice")
If userReturn = 6 Then
    MsgBox ("You just agreed.")
Else
    MsgBox ("You do not agree.")
End If