how do I validate sheet names?

[jojotherider]

Hi,

I've got a macro that creates sheets based on a list of names in sheet1. If the user goes back to the macro a second time (i.e. there are already named sheets), how can prevent them from creating another sheet that already exists. Currently, when I create the new sheet and try to rename it, I get an error if there is already a sheet with that name.

eh, hope that makes sense.

-joel

[there_is_no_bob]

What do you want to do in the case of an already existing sheet?

Do you have a variable that points to the sheet you're trying to rename?

[jojotherider]

good question...

Part of me wants to just exit the sub and make the user remove it and start over. But then that will probably lead to more duplicates.

I think displaying a message box indicating so would be good enough and then continuing with the script.

-joel

[rylo]

Hi

You could build your own function along the lines of

Function SheetExists(sh) As Boolean
  SheetExists = False
  On Error Resume Next
  Set sht = Sheets(sh)
  On Error GoTo 0
  If Not IsEmpty(sht) Then SheetExists = True
End Function

and incorporate this in your code.

sub tester
  if not sheetexists("sheet1") then
   'process your code
  end if
end sub

hth

rylo

[jojotherider]

That works perfect. Thank You!

I've never really written my own functions like that where you have to pass a value to it. I just learned that you could do that the other day.

-joel