Hi,
I wrote a routine to add a sheet named Answer Sheet and open the
InsertObject dialog box. Then I realized I needed to check for the
sheet, already existing, just in case, and found Bob Phillips UDF for
doing just that. But instead of creating a new sheet named "Answer
Sheet" and bringing up the dialog, it just inserts a generic sheet with
cell A1 active. The procedure worked prior to adding the needed check.
When I put a watch on SheetExists the value never changes from
<Expression not defined in context. What am I missing?

Here's the Code for the routine and the function

Private Sub cmdInsertFileObject_Click()
Dim Msg As Integer
Dim ans As Integer

Msg = MsgBox("This feature can be used to insert a file containing " _
& (Chr(13)) & " your answer into this workbook for e-mailing back to
the sender " _
& (Chr(13)) & "Select OK to insert File. Select Cancel to Exit", _
vbOKCancel + vbQuestion + vbDefaultButton1, "Insert File")

If Msg = 1 Then 'Click OK
If SheetExists("Answer Sheet") = True Then
'.....check if sheet exists using Bob Phillips UDF SheetExists
With Worksheets("Answer Sheet")
.Range("A1").Activate
Application.Dialogs(xlDialogInsertObject).Show
End With
ElseIf ActiveWorkbook.Worksheets.Add.Name = "Answer Sheet" Then
With Worksheets("Answer Sheet")
.Range("A1").Activate
Application.Dialogs(xlDialogInsertObject).Show
End With
End If
End If

If Msg = 2 Then 'Click cancel
Exit Sub
End If

End Sub

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


--
Casey


------------------------------------------------------------------------
Casey's Profile: http://www.excelforum.com/member.php...fo&userid=4545
View this thread: http://www.excelforum.com/showthread...hreadid=478263