How to build dialogue boxes into macros

[Guest]

How do I get XL/VB to return a dialogue box with an specific error-message
(defined by me) when the macro stops for a specific reason.
Example:

If the macro halts on

Sheets("Sheet1").Name = Sheets("Sheet1").Range("c4")

I want a dialouge box to appear with the text: " This name is already in
the database"

I am not an experienced programmer, so I would appreciate an explanation
"for dummies"

Thanks

 

[Guest]

You need an error trap. Your should be able to adapt this:

Sub xx()
Erin = "xx"
On Error GoTo errTrap
a = 10
b = a / 0
Exit Sub
errTrap:
MsgBox "Error in " & Erin & " " & Err.Number & " " & Err.Description,
vbOKOnly + vbCritical
End Sub

 

[Bob Phillips]

If you want that, you have to test the condition, and manage it

sName = Sheets("Sheet1").Range("c4")
If SheetsExists(sName) Then
Msgbox "This sheet name is already used"
...
End If

....
....

'-----------------------------------------------------------------
Function SheetExists(Sh As String, _
Optional wb As Workbook) As Boolean
'-----------------------------------------------------------------
Dim oWs As Worksheet
If wb Is Nothing Then Set wb = ActiveWorkbook
On Error Resume Next
SheetExists = CBool(Not wb.Worksheets(Sh) Is Nothing)
On Error GoTo 0
End Function


--

HTH

RP
(remove nothere from the email address if mailing direct)