Help with Bob Phillps' UDF SheetExists

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" <Casey.1x9f2f_1129910710.2125@excelforum-nospam.com> wrote in
message news:Casey.1x9f2f_1129910710.2125@excelforum-nospam.com...
>
> 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
>

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" <Casey.1x9kmc_1129917913.0721@excelforum-nospam.com> wrote in
message news:Casey.1x9kmc_1129917913.0721@excelforum-nospam.com...
>
> 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
>