Vba Continue Displaying Inputbox if Invalid
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
Source: https://www.excelanytime.com/excel/index.php?Itemid=475&catid=79&id=271%3Aexcel-vba-message-and-input-boxes-in-excel-msgbox-function-inputbox-function-inputbox-method&option=com_content&view=article