Hi All,
I have a file that records defects on a fleet of vehicles with a macro in it
which produces a list of repeat defects in the last 28 days on demand when
the vehicle comes up for servicing..
This macro names the worksheet as the name of the vehicle, for arguments
sake, call it 'Truck25'.
Sometimes a user will invoke this macro when a worksheet called 'Truck25'
already exists.
I've used the error handler facility within the macro code to pop up a
message box to inform the user that the sheet name already exists, delete
the new sheet and exit the sub.
I need to give some kind of warning that the macro wont work because the
people that use it are not computer buffs, just exiting generates a lot of
phone calls!
My question is, is there any way to determine whether a sheet named
'Truck25' exists already? If so I can modify the code to delete it before
another one is created and thus avoid the error and the need for a message
box,
TIA,
Regards,
Alan.

I don't follow the question completely but here is a function that will tell
you if a sheet exists in a given workbook...

Public Function SheetExists(SName As String, _
Optional ByVal Wb As Workbook) As Boolean
'Chip Pearson
On Error Resume Next
If Wb Is Nothing Then Set Wb = ThisWorkbook
SheetExists = CBool(Len(Wb.Sheets(SName).Name))
End Function
--
HTH...

Jim Thomlinson


"Alan" wrote:

Hi All,
I have a file that records defects on a fleet of vehicles with a macro in it
which produces a list of repeat defects in the last 28 days on demand when
the vehicle comes up for servicing..
This macro names the worksheet as the name of the vehicle, for arguments
sake, call it 'Truck25'.
Sometimes a user will invoke this macro when a worksheet called 'Truck25'
already exists.
I've used the error handler facility within the macro code to pop up a
message box to inform the user that the sheet name already exists, delete
the new sheet and exit the sub.
I need to give some kind of warning that the macro wont work because the
people that use it are not computer buffs, just exiting generates a lot of
phone calls!
My question is, is there any way to determine whether a sheet named
'Truck25' exists already? If so I can modify the code to delete it before
another one is created and thus avoid the error and the need for a message
box,
TIA,
Regards,
Alan.