Help with Bob Phillps' UDF SheetExists

[Casey]

Hi,
I wrote a routine to add a sheet named Answer Sheet and open th
InsertObject dialog box. Then I realized I needed to check for th
sheet, already existing, just in case, and found Bob Phillips UDF fo
doing just that. But instead of creating a new sheet named "Answe
Sheet" and bringing up the dialog, it just inserts a generic sheet wit
cell A1 active. The procedure worked prior to adding the needed check.
When I put a watch on SheetExists the value never changes fro
<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 t
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 Functio

 

[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]

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]

OK. Thanks again for the help.