InputBox, MsgBox and TextBox

Interaction with user via InputBox, MsgBox

Home page:
[View without Frames]

Example using both InputBox and MsgBox

Option Explicit
Sub test01()
   Dim a As Long, response As Long
   a = Application.InputBox( _
      Prompt:="Enter the Rownumber", _
      Title:="Delete rownumber:", Type:=1)
   If a <> False Then
     response = MsgBox("Are you sure.", vbYesNo)
     If response = vbYes Then Rows(a).Delete
   End If
End Sub
Perhaps a little more convention is Ok or Cancel on the MsgBox
  dim response as long
  response = msgbox(prompt:="ok or cancel",buttons:=vbokcancel)
  if response = vbok then ...
MsgBox(prompt[, buttons] [, title] [, helpfile, context])

Simple MsgBox Example(s)

worksheets(1) — for first sheet
worksheets("sheet1") – for named sheet
vbYesCancel – might be used instead
Sub stay_OR_Sheet1()
  MsgBox "Do you want to go sheet1", vbYesNo
  On Error Resume Next
  If vbYes Then Worksheets("sheet4").Activate
  If Err.Number = 9 Then
    MsgBox "Sorry but sheet4 no longer exits, so staying here anyway"
  On Error GoTo 0
  Rem   rest of code
End Sub

More Explicit Example for InputBox (#explicit)

InputBox allows you to set a default, and to place a title on the dialog, as well as the prompt. 
I prefer to specify each of those things.  See HELP for specific parameter information:

expression.InputBox(Prompt, Title, Default, Left, Top, HelpFile, HelpContextId, Type)
Warning the following example defaults to deleting all rows in the initial selection range,
if only one cell is selected that would the row of the active cel.

Sub test01()
   Dim a As Variant, response As Variant
   a = Selection(1).row & ":" & Selection(1).SpecialCells(xlLastCell).row
   a = Application.InputBox( _
      "Enter the Rownumber range to be deleted", _
      "Delete rownumber:", a, , , , 8)   '8 for a range
   'MsgBox VarType(a) '8=string,  11=Boolean
   If a <> False Then      ' use False if  a is defined as a number
     response = MsgBox("Are you sure, you want do delete row " & a, vbYesNo)
     If response = vbYes Then Rows(a).Delete
   End If
End Sub

Multiple Range Selection in InputBox (#multi)

Sub InputBox_MultiRange()
 Dim varRange As Range, subArea As Range, AreasStr As String
    '-- initial selection area(s) will be used as suggestion
    On Error Resume Next
    Set varRange = _
        Application.InputBox("Select single or multiple ranges:", _
           "MultiRange test", Selection.Address(0, 0), Type:=8)
    On Error GoTo 0
    If varRange Is Nothing Then Exit Sub
    For Each subRange In varRange.Areas
        AreasStr = AreasStr & Chr(10) & subRange.Rows.Count _
           & " rows in " & subRange.Address(0, 0)
    Next subRange
    MsgBox "Areas: " & varRange.Areas.Count & ", " & _
        "rows: " & varRange.Rows.Count & AreasStr
End Sub
John Walkenbach has an example, Excel Developer Tip: Copying a Multiple Selection (Tip 36), that obtains a multiple range without the inputbox, with a msgbox asking you to select range(s), and then does a multiple range paste that is not available in Excel.
Jim Rech posted code (2000-09-20) to put a warning box up for two seconds and then disappear. 
Thanks to Dave Peterson for mentioning this in newsgroups
Sub SelfClosingMsgBox()
    CreateObject("WScript.Shell").Popup "Hello", _
        2, "This closes itself in 2 seconds"
End Sub

Parameters for InputBox and then for MsgBox (#parameters)

Type parameter for InputBox (#type)

        expression.InputBox(prompt[, title] [, default] [, xPos] [, yPos] [, helpfile, type])

xPos     optional parameter from Left in points
yPos     optional parameter from Top in points

Value  Meaning for Type
0  A formula
1  A number
2  Text (a string) -- default
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
The type information from HELP

Type Optional Variant.  Specifies the return data type.  If this argument is omitted, the dialog box returns text.  Can be one or a sum of the type values.

You can use the sum of the allowable values for Type.  For example, for an input box that can accept both text and numbers, set Type to 1 + 2.

Button parameter for MsgBox (#msgbox)

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

Constant for ButtonsValue  Button Description(s)
 vbOKOnly0  Display OK button only.
 vbOKCancel1  Display OK and Cancel buttons.
 vbAbortRetryIgnore2  Display Abort, Retry, and Ignore buttons.
 vbYesNoCancel3  Display Yes, No, and Cancel buttons.
 vbYesNo4  Display Yes and No buttons.
 vbRetryCancel5  Display Retry and Cancel buttons.
 vbCritical16  Display Critical Message icon.
 vbQuestion32  Display Warning Query icon.
 vbExclamation48  Display Warning Message icon.
 vbInformation64  Display Information Message icon.
 vbDefaultButton10  First button is default.
 vbDefaultButton2256  Second button is default.
 vbDefaultButton3512  Third button is default.
 vbDefaultButton4768  Fourth button is default.
 vbApplicationModal0  Application modal; the user must respond to the message box before continuing work in the current application.
 vbSystemModal4096  System modal; all applications are suspended until the user responds to the message box.
 vbMsgBoxHelpButton16384  Adds Help button to the message box
 VbMsgBoxSetForeground65536  Specifies the message box window as the foreground window
 vbMsgBoxRight524288  Text is right aligned
 vbMsgBoxRtlReading1048576  Specifies text should appear as right-to-left reading on Hebrew and Arabic systems

VarType Constants (#vartype)

dim a as variant a = "abc"
msgbox vartype(a) -- would display 8 for string

The following constants can be used anywhere in your code in place of the actual values:

ConstantValue Description
 vbEmpty 0   Uninitialized (default)
 vbNull 1   Contains no valid data
 vbInteger 2  Integer
 vbLong 3  Long integer
 vbSingle 4   Single-precision floating-point number
 vbDouble 5   Double-precision floating-point number
 vbCurrency 6  Currency
 vbDate 7  Date
 vbString 8  String
 vbObject 9  Object
 vbError 10  Error
 vbBoolean 11  Boolean
 vbVariant 12   Variant (used only for arrays of variants)
 vbDataObject 13   Data access object
 vbDecimal 14  Decimal
 vbByte 17  Byte
 vbUserDefinedType  36   Variants that contain user-defined types 
 vbArray 8192  Array


An Event macro that will change all text boxes on the worksheet to caps upon selection of the worksheet.  (install with right on sheet tab, view code, ...)
Private Sub Worksheet_Activate()
' change all textboxes on the page to caps
' upon worksheet activation.  DMcR 2004-07-15
' also see kbid= 
  Dim tbox As TextBox
  For Each tbox In ActiveSheet.TextBoxes
     tbox.Text = UCase(tbox.Text)
  Next tbox
End Sub
kbid=152379 - How to Copy the Text Within a Text Box to a Cell,   ( you can Google kbid=152379)

Speech, Voice message alternative (#speech)

In the VBE, Tools, References check -- Microsoft Direct Speech Synthesis, then
code:  Application.Speech.Speak "I am done"

This page was introduced on May 11, 2002. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP

Please send your comments concerning this web page to: David McRitchie send email comments

Copyright © 1997 - 2004,  F. David McRitchie,  All Rights Reserved