InputBox

Syntax of InputBox in VBA:

InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context] )
‘prompt’ refers to the message that is displayed to the user.

‘title’ is an optional argument. It refers to the heading on the input dialog window. If it is omitted then a default title “Microsoft Excel” is shown.


Using the Title Fname = InputBox("Enter file name to save", "Save as PDF")

‘default’ it is an optional argument. It refers to the value that will appear in the textbox when the InputBox is initially displayed. If this argument is omitted, the textbox is left empty.

‘xpos’ is an optional argument. It refers to the positional coordinate of the input dialog window on X-axis.

‘ypos’ is also an optional argument. It refers to the positional coordinate of the input dialog window on Y-axis.

‘helpfile’ it is the location of help file that should be used with the InputBox. This is an optional parameter but it becomes a mandatory argument when ‘context’ argument is passed.
‘context’ represents the HelpContextId in the referenced ‘helpfile’. It is an optional paramete

Example:

Sub addproductnameandprice()
Dim pro As Variant ' Notice pro is a "Variant" so that we can use a decimal rather than whole numbers only.
Dim cst As Integer
Dim r As Integer
pro = InputBox("What is the product Descripiton/Name?")
cst = InputBox("Enter Cost of item.")
r = Worksheets("List").Cells(2, 4)

Cells(r, 2).Select
ActiveCell = pro
ActiveCell.Offset(0, 1) = cst
ActiveCell.Offset(0, 6).Select
Selection.FillDown

End Sub

Control box

Reset a Control Box using a macro.

Worksheets("Sheet1").Shapes("Drop Down 1").OLEFormat.Object.Value = 1

Example:

Sub chooseinventorychart()

If Worksheets("List").Range("Y6") = "2" Then Call OpenInventoryChart
If Worksheets("List").Range("Y6") = "3" Then Call CloseInventoryChart
Worksheets("Dash").Shapes("Drop Down 6").OLEFormat.Object.Value = 1

End Sub


Open and Close the chart from above example?

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes.Range(Array("Chart 1")).Visible = msoFalse ' This will Close Chart 1

ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes.Range(Array("Chart 1")).Visible = msoTrue ' This will Open Chart 1

Check box with "If Statement" Checked = 1 Unchecked = xlOff

Sub ticlow()


If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = 1 Then
Range("J1") = "Tic Low"
End If

If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = xlOff Then
Range("J1").ClearContents
End If
End Sub

Combo Box calls multiple List Boxes

Sub ComboBoxCalls_MultipleListsBoxs()
Dim a As String, b As String, c As String, d As String, x As String
a = "=Fruits"
b = "=Woods"
c = "=Sandwiches"
d = "=Knifes"

If [B1] = 2 Then x = a
If [B1] = 3 Then x = b
If [B1] = 4 Then x = c
If [B1] = 5 Then x = d

With Range("J1").Validation

.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=x

End With

End Sub

What we see here is that in cell J1 there is a drop down validation list of Sandwiches called by combo box in H1
Choose Fruits in H1combo box would cause the validation list in J1 to change to validation list Fruits.

Checkboxes, Check and unCheck 1 or all checkboxes

ActiveSheet.CheckBoxes.Value = False ' This will clear all checkboxes ("True" would check all boxes)

ActiveSheet.Checkboxes("Check Box 1").Value = True ' This will Check a particular checkbox

ActiveSheet.Checkboxes("Check Box 1").Value = False ' This will Clear a particular checkbox

Reset a ListBox using a macro.

Worksheets("Sheet1").Shapes("Drop Down 1").OLEFormat.Object.Value = 1

Searchable Dropdown ListBox using ActiveX combobox

See example file: "ActiveSearchDropdownList.xlsm
Instruction on the web at: Click Here. What the video.

Msgbox - - - - MsgBox(prompt[,buttons][,title][,helpfile,context])

MsgBox "Any messsage you want to display here"   ' this will have the standard Microsoft title

MsgBox "Welcome " & Application.UserName & "!", 1, "Title"

MsgBox "Sorry no room in play list!", , "Play Limit Reached"
GoTo Line1



MsgBox Link to tutorpoint



Msgbox with Yes NO

Sub msg()
Dim answer As Integer
answer = MsgBox("Would you like to place a one in H1", vbYesNo)

If answer = vbYes Then
[H1] = 1
Else
[H1] = 2
End If
End Sub



Msgbox with Yes NO

Msgbox("Prompt",Button style,"Title") ... This is the basic format. Above and Below are examples.

Sub Macro name()
Dim answer As Integer
answer = MsgBox("Part is Coded as Juniper?",4, "Coded as Juniper or Non juniper")
If answer = vbYes Then
Call Juniper_All
End If
If answer = vbNo Then
Call chooznonjuniper
End If

End Sub

Here are the button styles
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.