InputBox, MsgBox and TextBox

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"
  End
  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.

PopUp or Warning Box, short duration (#popup)

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)

Button parameter for MsgBox (#msgbox)

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:

Constant	Value	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

TextBox

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,
http://support.microsoft.com/?kbid=152379   ( 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"

Related   (#related)

• Getting Started with Macros for help in installing and running a macro.
• Cell Comment boxes
• Event Macros, more information on Event macros and how to install them.  They are installed differently than regular macros.
• Font size in comboboxes, depends on where you created combobox from.
• NameBox, increase the size of the name box, based on ...
• Proper, and other Text changes -- Use of Special Cells
• Shapes, a textbox is a shape, also see collections.

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

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