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

You are using ElseIf where you should use Else when adding the sheet

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 = vbOK 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
Else
ActiveWorkbook.Worksheets.Add.Name = "Answer Sheet"
With Worksheets("Answer Sheet")
Range("A1").Activate
Application.Dialogs(xlDialogInsertObject).Show
End With
End If
End If

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

End Sub

--

HTH

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


"Casey" wrote in
message ...

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

Bob,
Thank you for another great response. I copied your code off the forum
and realized the "*.*"before Range, somehow got dropped, and I had to
add the ".Activate" lines so that the sheet would show. But it worked
perfectly. Followup question if I could; is there a way to have the
dialog box default to the "Create from File" tab instead of the "Create
New" or better yet show only the "Create from File" tab similar to way I
understand the individual tabs for format Cells get displayed
individually?

Here's the working Code Many thanks Bob.

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 = vbOK Then 'Click OK
If SheetExists("Answer Sheet") = True Then
'.....check if sheet exists using Bob Phillips UDF SheetExists
With Worksheets("Answer Sheet")
.Activate
.Range("A1").Activate
Application.Dialogs(xlDialogInsertObject).Show
End With
Else
ActiveWorkbook.Worksheets.Add.Name = "Answer Sheet"
With Worksheets("Answer Sheet")
.Activate
.Range("A1").Activate
Application.Dialogs(xlDialogInsertObject).Show
End With
End If
End If

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


End Sub


--
Casey


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

Hi Casey,

I don't think so. Looking at help, all the arguments for the
xlDialogInsertObject seem to pertain to the object, not the display.

--

HTH

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


"Casey" wrote in
message ...

Bob,
Thank you for another great response. I copied your code off the forum
and realized the "*.*"before Range, somehow got dropped, and I had to
add the ".Activate" lines so that the sheet would show. But it worked
perfectly. Followup question if I could; is there a way to have the
dialog box default to the "Create from File" tab instead of the "Create
New" or better yet show only the "Create from File" tab similar to way I
understand the individual tabs for format Cells get displayed
individually?

Here's the working Code Many thanks Bob.

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 = vbOK Then 'Click OK
If SheetExists("Answer Sheet") = True Then
'.....check if sheet exists using Bob Phillips UDF SheetExists
With Worksheets("Answer Sheet")
Activate
Range("A1").Activate
Application.Dialogs(xlDialogInsertObject).Show
End With
Else
ActiveWorkbook.Worksheets.Add.Name = "Answer Sheet"
With Worksheets("Answer Sheet")
Activate
Range("A1").Activate
Application.Dialogs(xlDialogInsertObject).Show
End With
End If
End If

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


End Sub


--
Casey


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

OK. Thanks again for the help.


--
Casey


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