Help with Bob Phillps' UDF SheetExists

[Casey]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Casey]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Casey]

OK. Thanks again for the help.