Print

User Rating: 4 / 5

Star ActiveStar ActiveStar ActiveStar ActiveStar Inactive
 

 

Excel VBA - Message and Input Boxes in Excel, MsgBox Function, InputBox Function, InputBox Method

 

------------------------------------------------------------------------------------------------------------------------------

Contents:

Using Message Box in vba code

MsgBox Function

Example: Message Box Buttons options

Example: Set Default Button for Message Box

Example: Format MsgBox

Example: Using MsgBox return values with Select_Case statemnts

Example: Using MsgBox return values with If_Then statements

Using Input Box in vba code

InputBox Function

Example: Input Box display options

Example: Number format with InputBox

Example: Date format with InputBox

Example: InputBox Function Check for Cancel

Example: InputBox Function Check for Cancel - using StrPtr

Example: Using Input Box function - loop to allow input box be re-shown on invalid data entry

Application.InputBox Method

Example: InputBox Method Check for Cancel

Example: Accepting only numbers in InputBox

Accepting formulas in InputBox

Example: Accept formulas in InputBox

Example: Accept formulas in InputBox - using ConvertFormula method

Accepting a cell reference as a Range object in InputBox

Example: Accept a cell reference as a Range object in InputBox

Example: Using the FormulaLocal property to assign formula to a Range object with InputBox

------------------------------------------------------------------------------------------------------------------------------  

 

 

The Message Box is a means of interacting with the user, and is often used in vba code to display a message in a dialog box, wherein user response is required by clicking an appropriate button. A message box is also commonly used as a debugging tool. Input Box is used in Excel to get data from the user. An InputBox displays a dialog box wherein the user enters information which is used in vba code. You can create an input box by using either the InputBox Function (as described above) or using the InputBox Method.

 

 

Using Message Box in vba code

 

A MsgBox Function is often used in vba code to display a message in a dialog box, wherein user response is required by clicking an appropriate button (viz. Ok, Cancel, Yes, No, Retry, Ignore or Abort). The function returns an Integer indicating which button has been clicked by the user, basis which you proceed to execute an appropriate statement or code.

 

Using a message box in vba code: The message box is a means of interacting with the user viz. to display a value returned by executing a statement or code; or if you want the code to confirm from the user before performing an action like deleting or saving or deciding the execution flow; or if you want to let the user know that the macro has finished executing or exiting before finishing; and so on. A message box is also commonly used as a debugging tool, to validate or check code for any errors. The simplest code for displaying a message box is with the vba statement - MsgBox "Hello" - when your code is run, a dialog box will appear displaying the "Hello" message with an "Ok" button, clicking which the message box will be dismissed and code execution will continue. The message you wish to display in the message box should be typed between the double-quotes viz. "Hello" in this example.

 

MsgBox Function Syntax: MsgBox(prompt,buttons,title,helpfile,context). It is necessary to specify the prompt argument, while all others are optional. The prompt argument is a string expression that is displayed in the dialog box as the message. It can contain upto 1024 characters roughly, the precise number being determined by the width of characters used. The displayed message can be split into multiple lines by using the carriage return character - Chr(13), or the line feed character - Chr(10), or a combination of both. The buttons argument is a numeric expression representing the sum of values, which specify: number and type of buttons displayed, the icon style, the default button, and modality of the message box. Omitting this argument will default to value 0, which displays the OK button only. Use the title argument to specify a string expression to display in the dialog box's title bar. Omitting this argument will display your application's name in the title bar viz. for Excel application, "Microsoft Excel" will be displayed in the title bar. The helpfile argument is a string expression which defines or specifies the help file to be used for the dialog box. The context argument is a numerical value that specifies the number assigned to the appropriate Help topic. Both the arguments of helpfile and context will need to be specified together or none of them - if helpfile is specified, context must also be specified and if context is specified, helpfile must also be specified. When both the arguments of helpfile and context are specified, press F1 (Windows) or HELP (Macintosh) to view the help topic which corresponds to the context number. Specify the vbMsgBoxHelpButton built-in constant to add a Help button to the dialog box created by the MsgBox function, and when both the arguments of helpfile and context are specified you can view the appropriate Help topic corresponding to the context number by clicking this button.

 

 

Using multiple arguments: You will need to use MsgBox in an expression if you want to specify additional arguments in addition to the necessary argument of prompt. While specifying multiple arguments you can omit any positional argument by inserting a comma delimiter viz. code to display a message box with a title: MsgBox "Hello!", , "Greetings"

 

 

It is mentioned above that the MsgBox function returns an Integer indicating which button has been clicked by the user - these return values are:

 

Return Values
Value Constant Description
1 vbOK OK
2 vbCancel Cancel
3 vbAbort Abort
4 vbRetry Retry
5 vbIgnore Ignore
6 vbYes Yes
7 vbNo No

 

 

Settings for the buttons argument - choose only one value or constant from each group as below:

 

Number and type of buttons displayed in the dialog box           Determine modality of the message box
Value Constant Buttons Displayed   Value Constant Buttons Displayed
0 vbOKOnly OK button only 0 vbApplicationModal User response to the message box is required before work can be continued in the current application
1 vbOKCancel OK and Cancel buttons 4096 vbSystemModal Until user respondse to the message box, all applications are suspended
2 vbAbortRetryIgnore Abort, Retry and Ignore buttons 16384 vbMsgBoxHelpButton Help button gets added to the message box
3 vbYesNoCancel Yes, No and Cancel buttons 65536 VbMsgBoxSetForeground The message box window is specified as the foreground window
4 vbYesNo Yes and No buttons 524288 vbMsgBoxRight Right aligns text
5 vbRetryCancel Retry and Cancel buttons 1048576 vbMsgBoxRtlReading On Hebrew and Arabic systems, text will appear as right-to-left reading

 

Note: Pressing the ESC key will have the same effect as clicking the Cancel button, where present.

 

Describe the icon style           Determine which button is the default
Value Constant Icons Displayed   Value Constant Default Button
16 vbCritical Critical Message icon 0 vbDefaultButton1 First button
32 vbQuestion Warning Query icon 256 vbDefaultButton2 Second button
48 vbExclamation Warning Message icon 512 vbDefaultButton3 Third button
64 vbInformation Information Message icon 768 vbDefaultButton4 Fourth button

 

 

 

 

Example: Message Box Buttons options - refer Images 1 to 7:

 

Sub MsgBox_Buttons()
'Message Box Buttons

 

 

'basic message box, returns a message. Omitting the 'Buttons' argument will display the OK button only, Omitting the 'Title' argument displays "Microsoft Excel" in the title bar - refer Image 1:

MsgBox "Hello!"

 

 

 

 

'message box with title & OK button - refer Image 2:

MsgBox "Hello!", , "Greetings"

 

'message box with Information Message icon, Ok & Cancel buttons, display of "Microsoft Excel" in title bar:

MsgBox "Run program to return the Optimal Product Mix!", vbOKCancel + vbInformation

 

 

 

 

'Information message - refer Image 3:

MsgBox "Code executed, closing workbook!", vbOKOnly + vbInformation

 

 

 

 

'Critical message - refer Image 4:

MsgBox "Error - enter a valid value!", vbOKCancel + vbCritical, "Error"

 

 

 

 

'Code fails to connect - select options of abort, retry or ignore - refer Image 5:

MsgBox "Failed to establish connection - select from below options!", vbAbortRetryIgnore + vbCritical, "Connection failed"

 

 

 

 

'Question for user - refer Image 6:

MsgBox "Part 1 completed." & vbNewLine & "Continue to Part 2?", vbYesNo + vbQuestion, "Application Form"

 

 

 

 

'Exclamation message, display Warning Message icon - refer Image 7:

MsgBox "Invalid entry. Enter an Integer value!", vbRetryCancel + vbExclamation


End Sub

 

 

 

Example: Set Default Button for Message Box

 

Sub MsgBox_DefaultButton()
'set the default button

 

Dim response As Integer

 

'set the default button to No if you dont want the user to accidently press the Enter button to accept the default Yes and accidently delete data

'the default button is Yes in below code:

'response = MsgBox("Continue?", vbYesNo + vbQuestion, "Confirm")

'define your default button - set the focus from Yes to No:

response = MsgBox("Continue?", vbYesNo + vbQuestion + vbDefaultButton2, "Confirm")

 

'returns the value 7 for vbNo (6 for vbYes):

MsgBox response

'pressing enter will click No and display the message "Column NOT deleted!"

 

If response = vbYes Then

'delete active column

ActiveCell.EntireColumn.Delete

MsgBox "Column deleted!", vbInformation

Else

MsgBox "Column NOT deleted!", vbInformation

Exit Sub

End If


End Sub

 

 

 

Example: Format MsgBox - refer Images 8 & 9

 

              

 

 

Sub MsgBox_FormatText()
'format MsgBox - refer Images 8 & 9

 

'------------------------------------

'Multiple lines message box

 

'Multiple lines message box - using the Chr() function and vba constants for new lines or to insert a blank line - refer Image 8

'using the carriage return character - Chr(13); the line feed/new line character - Chr(10)

'vbCRLF constant represents a Carriage Return/Line Feed
'vbNewLine, vbCRLF, Chr(13) & Chr(10) give similar results in Windows

MsgBox "Multiple Lines:" & vbCrLf & vbCrLf & "A" & vbNewLine & "BB" & Chr(13) & "CCC" & Chr(10) & "DDDD" & Chr(10) & Chr(10) & "EEEEE"

 

'------------------------------------

'MsgBox with 3 parameters

 

Dim strMsg1 As String, strMsg2 As String, strMsg3 As String, strTitle As String

Dim response As Integer, iButtons As Integer

Dim iScoreHigh As Integer, iScoreLow As Integer, iTotalMarks As Integer

 

iScoreHigh = 84

iScoreLow = 67

iTotalMarks = 90

 

'set message: text indent - vbTab constant indicates TAB character.

strMsg1 = "High & Low Scores:"

strMsg2 = "Highest Score" & vbTab & Format(iScoreHigh / iTotalMarks, "0.00%")

strMsg3 = "Lowest Score" & vbTab & Format(iScoreLow / iTotalMarks, "0.00%")

 

'set buttons

iButtons = vbOKCancel + vbInformation + vbDefaultButton2

 

'set title

strTitle = "Exam Result"

 

'refer Image 9

MsgBox Prompt:=strMsg1 & vbCrLf & vbCrLf & strMsg2 & vbCrLf & strMsg3, Buttons:=iButtons, title:=strTitle


End Sub

 

 

 

Example: Using MsgBox return values with Select_Case statemnts - MsgBox Help Button, Help File & Context Id

 

Sub MsgBox_SelectCase_Help()
'Using MsgBox return values with Select_Case statemnts - MsgBox Help Button, Help File & Context Id

 

Dim response As Integer

 

'Use the vbMsgBoxHelpButton constant to add a Help Button to the Dialog Box.

'combining the Help Button with a 3-button option will yield 4 buttons, and you can therein use vbDefaultButton4 to make Help the default button.

'the Help button does nothing if the Help file is not specified

'If no help file has been given then the Help button does not do anything.

'display a custom Help topic - opens the Help file SampleHelp.chm in the current folder of this Excel workbook and displays the topic mapped to context ID 12

response = MsgBox(Prompt:="Deleting Active Sheet, Add New Sheet after Deletion, or Cancel?", Buttons:=vbYesNoCancel + vbQuestion + vbMsgBoxHelpButton + vbDefaultButton4, HelpFile:=ThisWorkbook.Path & "\SampleHelp.chm", Context:=12)

 

Select Case response

'delete & add (Yes = 6):

Case 6

'disable prompts or alerts

Application.DisplayAlerts = False

ActiveSheet.Delete

Worksheets.Add After:=Worksheets(Sheets.Count)

MsgBox "New Sheet Added after deleting Active Sheet."

 

'delete and do not add (No = 7):

Case 7

'disable prompts or alerts

Application.DisplayAlerts = False

ActiveSheet.Delete

MsgBox "Active Sheet Deleted."

 

'cancel - no deletion or addition (Cancel = 2):

Case 2

MsgBox "You chose to Cancel - No deletion or addition."

Exit Sub

End Select

        

End Sub

 

 

 

Example: Using MsgBox return values with If_Then statements - add new worksheet per user options / confirmations

 

Sub MsgBox_IfThen_AddSheet()
'Using MsgBox return values with If_Then statements - add new worksheet per user options / confirmations

 

Dim response1 As Integer, response2 As Integer, response3 As Integer

 

response1 = MsgBox("Do you want to add a worksheet?", vbYesNo + vbQuestion)

'IF YES TO ADD WORKSHEET

If response1 = vbYes Then

response2 = MsgBox("Add worksheet at the end? Click No to add before the active sheet.", vbYesNo + vbQuestion)

'if Yes to add at the end

If response2 = vbYes Then

Worksheets.Add After:=Worksheets(Sheets.Count)

'if No to add at the end, adds a new worksheet before the Active Worksheet

Else

Worksheets.Add

End If

 

response3 = MsgBox("Name new sheet with current time?", vbYesNo + vbQuestion)

'if YES TO NAME with current time

If response3 = vbYes Then

ActiveSheet.Name = Format(Now, "mm-dd-yyyy hh.mm.ss")

 

'confirmation message if added at the end

If response2 = vbYes Then

MsgBox "Worksheet Added at the end & Named", vbInformation

'confirmation message if Not added at the end

Else

MsgBox "Worksheet Added before the active sheet & Named", vbInformation

End If

'if NO TO NAME with current time, ie. if response3 = vbNo

Else

'confirmation message if added at the end

If response2 = vbYes Then

MsgBox "Worksheet Added at the end & Not Named", vbInformation

'confirmation message if Not added at the end

Else

MsgBox "Worksheet Added before the active sheet & Not Named", vbInformation

End If

End If

'IF NO TO ADD WORKSHEET, ie. if response1 = vbNo

Else

MsgBox "Worksheet Not Added", vbInformation

End If

 

End Sub

 

 

 

Using Input Box in vba code

 

InputBox Function

 

InputBox Function displays a dialog box which prompts the user to enter text, and returns the user-entered value as a String on clicking a button. An InputBox is used to display a dialog box in which the user enters information which will be used in a macro.

 

InputBox Function Syntax: InputBox(prompt, title, default, xpos, ypos, helpfile, context). It is necessary to specify the prompt argument, while all others are optional. The prompt argument is a string expression that is displayed in the dialog box as the message. It can contain upto 1024 characters roughly, the precise number being determined by the width of characters used. The displayed message can be split into multiple lines by using the carriage return character - Chr(13), or the line feed character - Chr(10), or a combination of both. Use the title argument to specify a string expression to display in the dialog box's title bar. Omitting this argument will display your application's name in the title bar viz. for Excel application, "Microsoft Excel" will be displayed in the title bar. Use the default argument to display a default string expression if no text is entered by the user. Omitting this argument will display an empty text box. The xpos argument is a numerical value, in twips, specifying the horizontal distance of the dialog box's left edge from the left edge of the screen, omitting which will horizontally center the dialog box. The ypos argument is a numerical value, in twips, specifying the vertical distance of the dialog box's top edge from the top edge of the screen, omitting which will position the dialog box about vertically one-third downwards the screen. The helpfile argument is a string expression which defines or specifies the help file to be used for the dialog box. The context argument is a numerical value that specifies the number assigned to the appropriate Help topic. Both the arguments of helpfile and context will need to be specified together or none of them - if helpfile is specified, context must also be specified and if context is specified, helpfile must also be specified. When both the arguments of helpfile and context are specified, press F1 (Windows) or HELP (Macintosh) to view the help topic which corresponds to the context number. The Excel application automatically adds a Help button to the dialog box where both arguments of helpfile and context are specified.

 

Using multiple arguments: You will need to use InputBox in an expression if you want to specify additional arguments in addition to the necessary argument of prompt. While specifying multiple arguments you can omit any positional argument by inserting a comma delimiter viz. code: InputBox "Enter your marks!", "Exam Results", , 50, 50

 

 

Example: Input Box display options - refer Images 1a & 1b

 

 

 

Sub InputBox_Display1()
'Input Box display options - refer Images 1a & 1b

 

'basic input box - refer Image 1a:

InputBox "Enter no. of rows to add!"

 

'input box with title - refer Image 1b:

InputBox "Enter your marks!", "Exam Results"

 

'input box at specified position - will display dialog box at top left side corner on screen

InputBox "Enter your marks!", "Exam Results", , 50, 50


End Sub

 

 

 

Example: Input Box display options - refer Images 2a & 2b

 

 

 

 Sub InputBox_Display2()
'Input Box display options - refer Images 2a & 2b

 

Dim strMsg1 As String, strMsg2 As String, strMsg3 As String, strTitle As String, strDefault As String

Dim strGrade As String

 

'set message: text indent - vbTab constant indicates TAB character.

strMsg1 = "Enter your Grade Here!"

strMsg2 = "Grade A for" & vbTab & "over 80%"

strMsg3 = "Grade B for" & vbTab & "under 80%"

 

'set title

strTitle = "Exam Result"

 

'set default text

strDefault = "Grade B"

 

'multiple lines display message - refer Image 2a

strGrade = InputBox(strMsg1 & vbCrLf & vbCrLf & strMsg2 & vbCrLf & strMsg3, , strDefault, 5000, 250)

 

'using Helpfile and Context arguments - automatically adds the Help button - refer Image 2b

strGrade = InputBox(prompt:=strMsg1, title:=strTitle, HelpFile:=ThisWorkbook.Path & "\SampleHelp.chm", Context:=10)

 

End Sub

 

 

 

Example: Number format with InputBox - refer Images 3a to 3f

 

 

 

 

 

 

 

 Sub InputBoxNumberFormat()
'number format with InputBox - refer Images 3a to 3f

 

On Error Resume Next

 

Dim strName As String

Dim sngMarks As Single, sngTotalMarks As Single

Dim result

 

Set rng = ActiveSheet.Cells(1, 1)

 

'refer Image 3a

strName = InputBox("Enter Student Name", "Student Name")

'refer Image 3b

sngMarks = InputBox("Enter Marks", "Student Marks")

'refer Image 3c

sngTotalMarks = InputBox("Enter Total Marks", "Total Marks")

 

'all 3 codes below display the same result:

 

'refer Image 3d

result = Format(sngMarks / sngTotalMarks * 100, "0.00")

MsgBox strName & " has got " & result & "%", , "Exam Result_1"

 

'use the percent number format - refer Image 3e

result = Format(sngMarks / sngTotalMarks, "0.00%")

MsgBox strName & " has got " & result, , "Exam Result_2"

 

'use the percent number format - refer Image 3f

result = Format(sngMarks / sngTotalMarks, "Percent")

MsgBox strName & " has got " & result, , "Exam Result_3"

 

End Sub

 

 

 

Example: Date format with InputBox

 

Sub InputBoxDateFormat()
'date format with InputBox

 

Dim strDate As String

 

strDate = InputBox("Enter date")

 

'Use the IsDate function to determine if a value can be converted to a date and time.

If IsDate(strDate) Then

'CDate function converts a value to a date. CDate recognizes date formats basis your system's locale setting. Ensure that you provide the day, month, and year in the correct sequence or order per your locale setting, or the date might not be correctly interpreted. A long date format containing a string value specifying a weekday like 'Tuesday' will not be recognized.

strDate = Format(CDate(strDate), "dd/mm/yyyy")

'displays date in the format of day-month-year

MsgBox strDate

Else

MsgBox "Invalid date"

End If

 

End Sub

 

 

 

Example: InputBox Function Check for Cancel

 

Sub InputBoxFunction_CheckForCancel_1()
'InputBox Function Check for Cancel - Cancel button & a zero-length string ("") may give similar response

 

Dim response As Variant

response = InputBox("Enter a value")

'if (i) input box field is blank & Ok is pressed, or (ii) the Cancel button is pressed 

If response = "" Then

'indicates either Cancel is pressed or a a zero-length string ("") has been entered

MsgBox "Either Cancel has been pressed or a zero-length string ("") has been entered"

Else

MsgBox response

End If

 

End Sub

 

 

 

Example: InputBox Function Check for Cancel - using StrPtr

 

Sub InputBoxFunction_CheckForCancel_2()
'InputBox Function Check for Cancel - using StrPtr differentiates Cancel button & zero-length string ("")

 

Dim response As Variant

response = InputBox("Enter a value")

 

'if cancel button is clicked

'StrPtr - String Pointer - is an undocumented function which can be used to determine if Cancel button has been pressed. StrPtr function allows you to get the address in memory of variables. Because this function is unsupported it may not be really advisable to be used because it could be excluded from any future VBA update.

If StrPtr(response) = 0 Then

MsgBox "Cancel has been pressed"

ElseIf response = "" Then

'alternatively:

'ElseIf response = vbNullString Then

MsgBox "A zero-length string ("") has been entered"

Else

MsgBox response

End If

 

End Sub

 

 

 

Example: Using Input Box function - loop to allow input box be re-shown on invalid data entry

 

Sub InputBox_ParamtersForHexagon()
'Using Input Box function - loop to allow input box be re-shown on invalid data entry - validate the entered data - check if Cancel button is pressed

 

'form a Rhombus (4 equal sides) or a Hexagon (6-sides) of consecutive odd numbers, using Offset & Resize properties of the Range object.

'this procedure will enter odd numbers consecutively (from start/lower number to last/upper number) in each successive row forming a pattern, where the number of times each number appears corresponds to its value - first row will contain the start number, incrementing in succeeding rows till the upper number and then taper or decrement back to the start number.

'if the start number is 1, the pattern will be in the shape of a Rhombus, and for any other start number the pattern will be in the shape of a Hexagon.

'this code enables INPUT of DYNAMIC VALUES for the start number, last number, first row position & first column position, for the Rhombus/Hexagon by using an INPUTBOX.

'ensure that both the start number & upper number are positive odd numbers.

 

Dim vStartNumber As Variant, vLastNumber As Variant, vRow As Variant, vCol As Variant

Dim i As Long, r As Long, c As Long, count1 As Long, count2 As Long, colTopRow As Long

Dim rng As Range

Dim ws As Worksheet

Dim InBxloop As Boolean

    

'set worksheet:

Set ws = ThisWorkbook.Worksheets("Sheet6")

'clear all data and formatting of entire worksheet:

ws.Cells.Clear

'restore default width for all worksheet columns:

ws.Columns.ColumnWidth = ws.StandardWidth

'restore default height for all worksheet rows:

ws.Rows.RowHeight = ws.StandardHeight

 

'----------------------

'INPUT BOX to capture start/lower number, last number, first row number, & first column number:

    

Do

'Set the InBxloop variable to True - InBxloop variable has been used to keep the input box displayed, to loop till a valid value is entered:

InBxloop = True

'enter an odd value for start/lower number:

vStartNumber = InputBox("Enter start number - should be an odd number!")

'if cancel button is clicked (StrPtr - String Pointer - is an undocumented function which can be used to determine if Cancel button has been pressed)

If StrPtr(vStartNumber) = 0 Then

'then exit procedure:

Exit Sub

'if non-numeric value is entered

ElseIf IsNumeric(vStartNumber) = False Then

MsgBox "Mandatory to enter a number!"

'break out of the loop

InBxloop = False

'if value entered is less than zero or an even number

ElseIf vStartNumber <= 0 Or vStartNumber Mod 2 = 0 Then

MsgBox "Must be a positive odd number!"

'break out of the loop

InBxloop = False

End If

'loop to allow input box be re-shown, if an error occurs, till all conditions are met

Loop Until InBxloop = True

 

Do

InBxloop = True

'enter an odd value for last/upper number:

vLastNumber = InputBox("Enter last number - should be an odd number!")

'if cancel button is clicked, then exit procedure:

If StrPtr(vLastNumber) = 0 Then

Exit Sub

ElseIf IsNumeric(vLastNumber) = False Then

MsgBox "Mandatory to enter a number!"

InBxloop = False

ElseIf vLastNumber <= 0 Or vLastNumber Mod 2 = 0 Then

MsgBox "Must be a positive odd number!"

InBxloop = False

ElseIf Val(vLastNumber) <= Val(vStartNumber) Then

MsgBox "Error - the last number should be greater than the start number!"

InBxloop = False

End If

Loop Until InBxloop = True

 

Do

InBxloop = True

'determine row number from where to start - this will be the top edge of the pattern:

vRow = InputBox("Enter first row number, from where to start!")

'if cancel button is clicked, then exit procedure:

If StrPtr(vRow) = 0 Then

Exit Sub

ElseIf IsNumeric(vRow) = False Then

MsgBox "Mandatory to enter a number!"

InBxloop = False

ElseIf vRow <= 0 Then

MsgBox "Must be a positive number!"

InBxloop = False

End If

Loop Until InBxloop = True

 

Do

InBxloop = True

'determine column number from where to start - this will be the left edge of the pattern:

vCol = InputBox("Enter first column number, from where to start!")

'if cancel button is clicked, then exit procedure:

If StrPtr(vCol) = 0 Then

Exit Sub

ElseIf IsNumeric(vCol) = False Then

MsgBox "Mandatory to enter a number!"

InBxloop = False

ElseIf vCol <= 0 Then

MsgBox "Must be a positive number!"

InBxloop = False

End If

Loop Until InBxloop = True

 

'----------------------

'calculate the column number of top row right starting from first column number (vCol):

colTopRow = Application.RoundUp(vLastNumber / 2, 0) + Application.RoundDown(vStartNumber / 2, 0)

    

'----------------------

'set range (top right) from where to offset, when numbers are incrementing:

Set rng = ws.Cells(vRow, colTopRow + vCol - 1)

count1 = vStartNumber

count2 = 1

'loop to enter odd numbers (start number to last number) in each row wherein the number of entries in a row corresponds to the value of the number - i - entered:

For i = vStartNumber To vLastNumber Step 2

'offset & resize each row per the correspponding value of the number:

Set rng = rng.Offset(count1 - i, count2 - i).Resize(, i)

rng.Value = i

rng.Interior.Color = vbYellow

count1 = i + 3

count2 = i + 1

Next

 

'----------------------

'set range from where to offset, when numbers are decreasing:

Set rng = ws.Cells(vRow, 1 + vCol)

count1 = colTopRow + 1

count2 = 1

r = vStartNumber

c = 1

'loop to enter odd numbers (decreasing to start number) in each row wherein the number of entries in a row corresponds to the value of the number - i - entered:

For i = vLastNumber - 2 To vStartNumber Step -2

'offset & resize each row per the correspponding value of the number - i:

Set rng = rng.Offset(count1 - r, count2 - c).Resize(, i)

rng.Value = i

rng.Interior.Color = vbYellow

count1 = r + 3

count2 = c + 3

c = c + 2

r = r + 2

Next

 

'----------------------

'autofit column width with numbers:

ws.UsedRange.Columns.AutoFit

 

End Sub

 

 

 

 

Application.InputBox Method

 

InputBox Method displays a dialog box for the user to enter information, and has an OK button and a Cancel button. Clicking the OK button in the dialog box will return the value entered by the user, and clicking the Cancel button will return False. It is similar to the InputBox Function wherein both create an InputBox to get data from the user, to be used in a macro.

 

InputBox Method vs InputBox Function: Input Box is used in Excel to get data from the user. You can create an input box by using either the InputBox Function (as described above) or using the InputBox Method. Preceding the InputBox Function with "Application" will make it an InputBox Method, the main difference between the two being that the InputBox Method allows checking the correctness of entered data. The InputBox Method adds an extra argument of Type, which specifies the type of data to be entered. Also, if you click the Cancel button, the InputBox method returns False whereas the InputBox function returns an empty text string (""). Use the InputBox with the object qualifier (Application.InputBox) to call the InputBox Method which enables it to be used with Excel objects, error values & formulas and wherein user input can also be checked.

 

InputBox Method Syntax: Application.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextID, Type). It is necessary to specify the Prompt argument, while all others are optional. The Prompt argument is displayed in the dialog box as the message - it can be a string, a number, a date, or a Boolean value, but is forcibly displayed as a String data type in the dialog box. The Title argument specifies the title for the input box and is displayed in the dialog box's title bar. Omitting this argument will display the default title of "Input". Use the Default argument to display a default string expression if no text is entered by the user. Omitting this argument will display an empty text box. A Range object can also be entered as a default value. The Left argument specifies, in points, the x-position for the dialog box in respect to the screens's upper-left corner. The Top argument specifies, in points, the y-position for the dialog box in respect to the screens's upper-left corner. The HelpFile argument specifies the help file to be used for the input box. The HelpContextID argument specifies the number assigned to the appropriate Help topic in the HelpFile. Both the arguments of HelpFile and HelpContextID will need to be specified together or none of them - a Help button is automatically added to the dialog box where both arguments are specified. The Type argument is used to specify the data type returned by dialog box, and if omitted, text is returned.

 

The following values can be passed in the Type argument. You can pass one or a sum of values viz. to allow a user to enter both Text and Number, set the Type parameter as Type:=1+2.

 

Value Meaning
0 A formula
1 A number
2 Text (a string)
4 A logical value (True or False)
8 A cell reference, as a Range object
16 An error value, such as #N/A
64 An array of values

 

 

 

Example: InputBox Method Check for Cancel

 

Sub InputBoxMethod_CheckForCancel_1()
'InputBox Method Check for Cancel - input box that can accept both Text And numbers

 

Dim response As Variant

 

ActiveSheet.Cells(1, 1).Clear

 

'input box that can accept both text and numbers

response = Application.InputBox("Enter a value", "Text & Numbers Only", , , , , , 1 + 2)

 

If response = "False" Then

'code distinguishes between "False" on clicking the Cancel button as compared to "False" entered in the input box as a string value which returns 0

MsgBox "Cancel has been pressed"

ElseIf response = "" Then

MsgBox "A zero-length string ("") has been entered"

Else

'reurns "0" (zero) both if either "0" is entered in input box or "False" is entered in input box

MsgBox response

End If

 

ActiveSheet.Cells(1, 1) = response

 

End Sub

 

 

 

Example: InputBox Method Check for Cancel

 

Sub InputBoxMethod_CheckForCancel_2()
'InputBox Method Check for Cancel - input box that can accept only numbers

 

Dim response As Variant

ActiveSheet.Cells(1, 1).Clear

'input box that can accept only numbers

'Note - InputBox will accept if True or False are entered (any other text is not accepted)

response = Application.InputBox("Enter a value", "Numbers Only", , , , , , 1)

If response = "False" Then

'code distinguishes between "False" on clicking the Cancel button as compared to "False" entered in the input box as a string value

MsgBox "Cancel has been pressed"

Else

'on pressing the Ok button, returns "0" (zero) both if either "0" is entered in input box or "False" is entered in input box & returns 1 if True is entered in InputBox

MsgBox response

End If

ActiveSheet.Cells(1, 1) = response

 

End Sub

 

 

 

Example: InputBox Method Check for Cancel

 

Sub InputBoxMethod_CheckForCancel_3()
'InputBox Method Check for Cancel - input box that can accept only numbers

 

Dim response As Variant

'or
'Dim response As Double

ActiveSheet.Cells(1, 1).Clear

'input box that can accept only numbers

'Note - InputBox will accept if True or False are entered (any other text is not accepted)

response = Application.InputBox("Enter a value", "Numbers Only", , , , , , 1)

If response = False Then

'on pressing the Ok button, returns below message if either "0" is entered in input box or "False" is entered in input box

'on pressing the Cancel button, returns below message

MsgBox "Either Cancel has been pressed, or 0 or False has been entered"

Else

'on pressing the Ok button, returns the number or returns 1 if True is entered in InputBox

MsgBox response

End If

ActiveSheet.Cells(1, 1) = response

 

End Sub

 

 

 

 

Example: Accepting only numbers in InputBox

 

Sub InputBoxMethod_AcceptNumbers()
'accepting only numbers in InputBox

 

Dim response As Double

ActiveSheet.Range("C2").Clear

'Input Box - set Type argument to 1 for accepting numbers only

'Note - InputBox will accept if "True" or "False" is entered (any other text is not accepted)

response = Application.InputBox("Enter a value", "Numbers Only", "Only numbers accepted", , , , , 1)

'check if Cancel button is pressed or not

If response <> False Then

'if Cancel button is pressed nothing is entered in cell C2

'if Ok is pressed: (i) enter the number in cell C2 of active sheet; (ii) enters 1 in cell C2 if True is input; (iii) does not enter anything in cell C2 if 0 or False is input

ActiveSheet.Range("C2") = response

End If

'if Ok button is pressed, returns the number or returns 1 if True is input or returns 0 if False is input

'if Cancel button is clicked, returns 0

MsgBox response

 

End Sub

 

 

 

Accepting formulas in InputBox

Set Type argument to 0 for accepting formulas and in this case the InputBox returns the formula in the form of text. Any references in the formula are returned as A1-style references in InputBox. See below examples of accepting formulas in InputBox.

 

 

Example: Accept formulas in InputBox - refer Images 4a & 4b

 

      

 

 Sub InputBoxMethod_AcceptFormulas1()
'accepting formulas in InputBox - refer Images 4a & 4b

'InputBox returns the formula in the form of text. Any references in the formula are returned as A1-style references in InputBox.

 

Dim strFormula As String, result As Variant

 

ActiveSheet.Range("A1").Clear

'Input Box - set Type argument to 0 for accepting formulas

'you may select cells to refer within formula

'InputBox returns the formula in the form of text - refer Image 4a

result = Application.InputBox("Enter a formula", "Accept Formulas", , , , , , 0)

ActiveSheet.Range("A1") = result

'returns formula in R1C1-style - refer Image 4b

MsgBox result

 

strFormula = "=" & Cells(1, 3).Address & "+" & Cells(1 + 1, 3).Address
'inputbox will display the default formula as: =$C$1 + $C$2
result = Application.InputBox(prompt:="Enter a formula", title:="Accept Formulas", Default:=strFormula, Type:=0)
'returns formula in R1C1-style: =SUM(R1C3:R2C3)
MsgBox result
    
strFormula = "=$C$1 + $C$2"
'inputbox will display the Default formula as: =$C$1 + $C$2
result = Application.InputBox(prompt:="Enter a formula", title:="Accept Formulas", Default:=strFormula, Type:=0)
'returns formula in R1C1-style: =SUM(R1C3:R2C3)
MsgBox result

 

End Sub

 

 

 

Example: Accept formulas in InputBox - using ConvertFormula method - refer Images 5a to 5c

 

 

       

 

 Sub InputBoxMethod_AcceptFormulas2()
'InputBox returns the formula in the form of text. Any references in the formula are returned as A1-style references in InputBox. You may use ConvertFormula to convert between reference styles - refer Images 5a to 5c

 

Dim result As Variant

Dim strFormula As String

 

ActiveSheet.Range("A1").Clear

'set formula for the Default argument value that will appear in the text box when the dialog box is initially displayed

strFormula = "=IF(($C$1*$C$2)>$C$3,1,0)"

 

'Input Box - set Type argument to 0 for accepting formulas

'InputBox returns the formula in the form of text - refer Image 5a

result = Application.InputBox(prompt:="Enter a formula", title:="Accept Formulas", Default:=strFormula, Type:=0)

'enter formula result in cell A1 

ActiveSheet.Range("A1") = result

 

'returns formula as text in R1C1-style in the message Box - refer Image 5b

MsgBox result

'using ConvertFormula method to convert between reference styles - display formula as A1-style references after converting from R1C1-style references - refer Image 5c

MsgBox Application.ConvertFormula(result, fromReferenceStyle:=xlR1C1, toReferenceStyle:=xlA1)

 

End Sub

 

 

 

Accepting a cell reference as a Range object in InputBox

Set Type argument to 8 for return value to be a cell reference (Range object). use the Set statement to assign the return value to a Range object, otherwise the variable is set to the Range value and not the Range object. See below examples. You will be required to use the FormulaLocal property to assign formula to a Range object when accepting formulas in InputBox. See below examples.

 

 

Example: Accept a cell reference as a Range object in InputBox - refer Images 6a & 6b

 

      

 

Sub InputBoxMethod_AcceptCellReference()
'accepting a cell reference as a Range object in InputBox - refer Images 6a & 6b

 

Dim rng As Range

Dim rngAddress As String

 

On Error Resume Next

 

'Input Box - set Type argument to 8 for return value to be a cell reference (Range object) - refer Image 6a

'use the Set statement to assign the return value to a Range object, otherwise the rng variable is set to the Range value and not the Range object

Set rng = Application.InputBox(prompt:="Select range to format", title:="Cell Reference", Type:=8)

 

'set interior color of selected Range to yellow

rng.Interior.Color = vbYellow

 

'set rng address

rngAddress = rng.Address

 

'refer Image 6b

MsgBox "Interior color of Range " & rngAddress & "," & vbNewLine & "consisting of " & rng.Cells.Count & " cells," & vbNewLine & "has been set to Yellow."

 

End Sub

 

 

 

Example: Using the FormulaLocal property to assign formula to a Range object with InputBox - refer Images 7a to 7c

 

 

       

 

Sub InputBoxMethod_AssignFormulaToRange()
'using the FormulaLocal property to assign formula to a Range object with InputBox - refer Formulas 7a to 7c

 

On Error Resume Next

 

Dim strAssignFormula As Variant

Dim strFormula As String

Dim rng As Range

 

strFormula = "=SUM($A$1:$A$3)"

 

'Input Box - set Type argument to 0 for accepting formulas - refer Image 7a

strAssignFormula = Application.InputBox(prompt:="Enter a formula", title:="Accept Formulas", Default:=strFormula, Type:=0)

 

'Input Box - set Type argument to 8 for return value to be a cell reference (Range object) - refer Image 7b

Set rng = Application.InputBox(prompt:="Select range to assign Formula", title:="Cell Reference", Type:=8)

 

rng.FormulaLocal = strAssignFormula

 

'returns the total of cells A1:A3 which is entered in the Range (rng variable) - refer Image 7c

MsgBox rng.Value

 

End Sub