Validating InputBox values

[iceberg27191]

I'm looking to be able to validate InputBox values that would generate an error message. I would like to not have just a MsgBox pop up, but rather the one with the red circle and X through it that Excel spits at you when you manually type in data that is invalid (assuming that cell is set up to be Data Validated).

I've tried the "Worksheet_Change (ByVal Target As Range)" method, but I cannot seem to write the code for that Excel error box as described above (has the options of 'Retry', 'Cancel', and 'Help').

Does anyone know how this can be done?

 

[villy]

Try...

Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A2").Text <> "Yes" Then MsgBox "Warning!", vbCritical, "Warning"
End Sub

 

[Jerry Sullivan]

I've tried the "Worksheet_Change (ByVal Target As Range)" method, but I cannot seem to write the code for that Excel error box as described above (has the options of 'Retry', 'Cancel', and 'Help').

In addition to what villy described, you can add more than one buttons' values together to combine buttons like Retry or Help on your msgbox.

Check out Excel's VB Help under MsgBox for an overview of all buttons.

Set myCell = Application.InputBox( _
        Prompt:="Select a cell", Type:=8)
If myCell Is Nothing Then Exit Sub
While myCell.Count > 1
    Response = MsgBox(Prompt:="Select only one cell", _
                Buttons:=vbRetryCancel + vbCritical + vbMsgBoxHelpButton, _
                Title:="Warning", _
                HelpFile:="DEMO.HLP", _
                Context:=1000)

I haven't worked with custom help files but my understanding is that you need to have an application that can write Windows .HLP files.

Here is a link if you want to investigate some options for creating custom help files.
http://www.excelguru.ca/node/73

 

[iceberg27191]

This is really helpful --

Now can I do a Select Case or something with actions I'd want occuring after each of these vbButtons are pressed? I can't get any of this code from recording macros...

 

[Jerry Sullivan]

This is really helpful --

Now can I do a Select Case or something with actions I'd want occuring after each of these vbButtons are pressed? I can't get any of this code from recording macros...

The VB Help in Excel also lists all the Return values.
You can use the number values or better yet the "vb*" constant enumerations
(which make it easier to follow the intent of your code).

Select Case Response
    Case vbRetry
        '....