PDA

View Full Version : Need Help: Check Sheet Name Exists Before Creating New W/ Same Name



jmack81
04-06-2016, 05:50 PM
Hello - I've been searching for a while and found several things that may work but they all seem fairly bloated for my purposes. What I am creating is an attendance tracker for my employees. I have it creating a new sheet and populating the dates and other data when you click "start new month". Others use this as well. If one were to create a new sheet within the same month, there will be a run-time error due to the fact the sheet already exists.

I would like it to function as it does but in the event someone does want to create a new month(sheet) and that current months sheet exists already, I would like an input box so they can enter a name of their choosing.


Private Sub Cont_Click()
Nmth.Hide
Sheets("Empty").Copy Before:=Sheets(1)
ActiveSheet.Name = Format(Date, "MMMM-YY")
Cells(45, "A") = Format(Date, "MMMM")
Dim i As Integer
Dim counter As Integer
Dim days As Integer
days = Cells(44, "A")
counter = 0
i = 1
Cells(1, "D").Select
Do While counter < Cells(44, "A").Value
ActiveCell.Value = Cells(45, "A").Value & i
ActiveCell.Offset(0, 1).Select
counter = counter + 1
i = i + 1
Loop
End Sub

Thanks for any help. I'm out of practice!

Paul_Hossler
04-07-2016, 05:35 AM
Probably something like this



Option Explicit

Private Sub Cont_Click()
Dim i As Integer
Dim counter As Integer
Dim days As Integer
Dim sWS As String

Nmth.Hide

sWS = Format(Date, "MMMM-YY")
'check for already existing
Do While WorksheetExists(sWS)
sWS = InputBox(sWS & " exists. Enter another, or leave blank to exit", "Duplicate Sheet Name")
If Len(sWS) = 0 Then Exit Sub
Loop

Sheets("Empty").Copy Before:=Sheets(1)
ActiveSheet.Name = sWS
Cells(45, "A") = Format(Date, "MMMM")
days = Cells(44, "A")
counter = 0
i = 1
Cells(1, "D").Select
Do While counter < Cells(44, "A").Value
ActiveCell.Value = Cells(45, "A").Value & I
ActiveCell.Offset(0, 1).Select
counter = counter + 1
i = i + 1
Loop
End Sub
Function WorksheetExists(wsName As String) As Boolean
Dim i As Long
On Error Resume Next
i = -1
i = Worksheets(wsName).Index
On Error GoTo 0
WorksheetExists = Not (i = -1)
End Function

jmack81
04-09-2016, 10:05 AM
Probably something like this



Option Explicit

Private Sub Cont_Click()
Dim i As Integer
Dim counter As Integer
Dim days As Integer
Dim sWS As String

Nmth.Hide

sWS = Format(Date, "MMMM-YY")
'check for already existing
Do While WorksheetExists(sWS)
sWS = InputBox(sWS & " exists. Enter another, or leave blank to exit", "Duplicate Sheet Name")
If Len(sWS) = 0 Then Exit Sub
Loop

Sheets("Empty").Copy Before:=Sheets(1)
ActiveSheet.Name = sWS
Cells(45, "A") = Format(Date, "MMMM")
days = Cells(44, "A")
counter = 0
i = 1
Cells(1, "D").Select
Do While counter < Cells(44, "A").Value
ActiveCell.Value = Cells(45, "A").Value & I
ActiveCell.Offset(0, 1).Select
counter = counter + 1
i = i + 1
Loop
End Sub
Function WorksheetExists(wsName As String) As Boolean
Dim i As Long
On Error Resume Next
i = -1
i = Worksheets(wsName).Index
On Error GoTo 0
WorksheetExists = Not (i = -1)
End Function




Thanks. What I ended up writing was simple.



Private Sub Cont_Click()
Nmth.Hide
If ActiveSheet.Name = Format(Date, "MMMM-YY") Then
Rename.Show
Else
Sheets("Empty").Copy Before:=Sheets(1)
ActiveSheet.Name = Format(Date, "MMMM-YY")
Cells(45, "A") = Format(Date, "MMMM")
Dim i As Integer
Dim counter As Integer
Dim days As Integer
days = Cells(44, "A")
counter = 0
i = 1
Cells(1, "D").Select
Do While counter < Cells(44, "A").Value
ActiveCell.Value = Cells(45, "A").Value & i
ActiveCell.Offset(0, 1).Select
counter = counter + 1
i = i + 1
Loop
End If
End Sub


My code will still fail if someone is on a different sheet or tries to enter the same name twice. I think I'll integrate what you wrote when I have some extra time.

Thanks!

jmack81
04-09-2016, 11:23 AM
Changed to this. Solved the problem.



Private Sub Cont_Click()
Nmth.Hide


Dim CurSheet As String
CurSheet = Format(Date, "MMMM-YY")
Sheets(CurSheet).Select
If ActiveSheet.Name = Format(Date, "MMMM-YY") Then
Rename.Show
Else
Sheets("Empty").Copy Before:=Sheets(1)
ActiveSheet.Name = Format(Date, "MMMM-YY")
Cells(45, "A") = Format(Date, "MMMM")
Dim i As Integer
Dim counter As Integer
Dim days As Integer
days = Cells(44, "A")
counter = 0
i = 1
Cells(1, "D").Select
Do While counter < Cells(44, "A").Value
ActiveCell.Value = Cells(45, "A").Value & i
ActiveCell.Offset(0, 1).Select
counter = counter + 1
i = i + 1
Loop
End If
End Sub