Solved: checking worksheet name in macro

[Patrick]

Hello,

I am fairly new at VBA and I would like some help with the following:
I have a workbook that includes a macro to add a worksheet by clicking a button to activate that macro. The macro generate a calendar and the date selected by the user will be pasted in the new worksheet and this date is used to name the newly added worksheet. As long as there is no sheet with that same date, all is fine. How can I make the macro check if a sheet with that name already exists and if it does to go back to the input calender and if not to go ahead a create that new sheet?

I have tried the sheetexits function but I can't get it to work.

I would greatly appreciate the help on this!

Here is what I have so far:

Sub adsheet()
Application.ScreenUpdating = False
Sheet16.Visible = True
Sheets("Dag").Select
ActiveSheet.Unprotect Password:="plccbe0510docs"
Sheets("Dag").Copy Before:=Sheets("Totaal")
Sheets("Dag").Protect Password:="plccbe0510docs"
Sheet16.Visible = xlSheetVeryHidden
Sheets("Dag (2)").Select
Range("k4").Select
frmKalender.Show
Set Target = Range("k4")
ActiveSheet.Name = Left(Target, 31)
Range("k6").Select
Application.ScreenUpdating = True
End Sub

For the calender here is what I have:

Private Sub Kalender_Click()
ActiveCell.Value = Kalender.Value
Unload Me

End Sub

Private Sub UserForm_Initialize()
If IsDate(ActiveCell.Value) Then
Kalender.Value = DateValue(ActiveCell.Value)
Else
Kalender.Value = Date
End If
End Sub

[Jacob Hilderbrand]

Ok, so this is the name we need to check.

ActiveSheet.Name = Left(Target, 31)

Try something like this:


Dim ws As Worksheet
Dim wsName As String

wsName= Left(Target, 31)
On Error Resume Next
Set ws = Sheets(wsName)
On Error Goto 0

If ws Is Nothing Then
'Sheet does not exist
Else
'Sheet exists
End If

[Bob Phillips]

Try this function


'-----------------------------------------------------------------
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

[Patrick]

DRJ,
Yes, ActiveSheet.Name = Left(Target, 31) is what we need to check.
Could you be a bit more specific on where I should insert your code in what I already have? Thanx.

[Patrick]

XLD,
I found some similar bits of code on the web but I couldn't figure out how and where to apply the sheetexists function in my specific case. Could you elaborate a bit on this? Thanx!

[Bob Phillips]

XLD,
I found some similar bits of code on the web but I couldn't figure out how and where to apply the sheetexists function in my specific case. Could you elaborate a bit on this? Thanx!

Before you copy the worksheet, check if it exists. Something like this, although you will have to test and verify as the code is unfamiliar to me


Sub adsheet()
Sheets("Dag (2)").Select
Range("k4").Select
frmKalender.Show
Set Target = Range("k4")
If SheetExists(Left(Target, 31)) Then
MsgBox "Sheet already exists"
Else
Application.ScreenUpdating = False
Sheet16.Visible = True
Sheets("Dag").Select
ActiveSheet.Unprotect Password:="plccbe0510docs"
Sheets("Dag").Copy Before:=Sheets("Totaal")
Sheets("Dag").Protect Password:="plccbe0510docs"
Sheet16.Visible = xlSheetVeryHidden
ActiveSheet.Name = Left(Target, 31)
Range("k6").Select
Application.ScreenUpdating = True
End If
End Sub

[Patrick]

DRJ,
Yes, ActiveSheet.Name = Left(Target, 31) is what we need to check.
Could you be a bit more specific on where I should insert your code in what I already have? Thanx.

[Patrick]

XLD,
I'll give it a try, thanx. I'll let you know.

[Patrick]

Hi, thanx for the input so far but I am still stuck.

Code included below, but here is a bit more info:
What the macro does is unlock and unhide a worksheet that is used as template for the actual worksheet for the user to work on. Once unhidden it is a copy is pasted before worksheet "totaal", the original template is locked and hidden again. Before the user gets to see the new worksheet a calender pops up to input a date (why this happens before the new worksheet is shown I don't know but it doesn't bother me). That date is used as input for a cell (K4) on the new worksheet, and this cell value is used to replace the default name (Dag (2)) of the new worksheet. When a worksheet with the same name does NOT already exist the code works fine. But when a worksheet with the same name already DOES exist the code gives an error on the line "ActiveSheet.Name = Left(Target, 31)".
What am I doing wrong, or am I forgetting? Please help!:banghead:


Sub adsheet()

Dim ws As Worksheet

Application.ScreenUpdating = False
Sheet16.Visible = True
Sheets("Dag").Select
ActiveSheet.Unprotect Password:="plccbe0510docs"
Sheets("Dag").Copy Before:=Sheets("Totaal")
Sheets("Dag").Protect Password:="plccbe0510docs"
Sheet16.Visible = xlSheetVeryHidden

Sheets("Dag (2)").Select
Range("k4").Select
calendarinput:
frmKalender.Show
Set Target = Range("k4")

On Error Resume Next
Set ws = Sheets(Target)
On Error GoTo 0

If ws Is Nothing Then
'Sheet does not exist
ActiveSheet.Name = Left(Target, 31)
ActiveSheet.Protect Password:="plccbe0510docs"
Range("k6").Select
Application.ScreenUpdating = True

Else
'Sheet exists
GoTo calendarinput
End If

End Sub

[Patrick]

Here is what it ended up working:


Sub voegDAGRAPPORTtoe()
Dim ws As Worksheet
Dim stSht As String
CreateNewSheet:
Application.ScreenUpdating = False
Sheet16.Visible = True
Sheets("Dag").Select
ActiveSheet.Unprotect Password:=""
Sheets("Dag").Copy Before:=Sheets("Totaal")
Sheets("Dag").Protect Password:=""
Sheet16.Visible = xlSheetVeryHidden

Sheets("Dag (2)").Select
Range("k4").Select
frmKalender.Show

If Range("k4") = "" Then
' close button is used or escape is pressed
Application.DisplayAlerts = False
Worksheets("Dag (2)").Delete
Application.DisplayAlerts = True

Else
stSht = Range("k4")

If Not bWorksheetExists(stSht) Then
'Sheet does not exist
'do something
Set Target = Range("k4")
ActiveSheet.Name = Left(Target, 31)
ActiveSheet.Protect Password:=""
Range("k6").Select
Application.ScreenUpdating = True

Else
'Sheet exists
MsgBox "Een werkblad met deze datum bestaat al"
Application.DisplayAlerts = False
Worksheets("Dag (2)").Delete
Application.DisplayAlerts = True
GoTo CreateNewSheet


End If
End If

End Sub


Problem solved,
Thanx for the help

[Patrick]

Thanx everyone,
Problem is solved.

[mvidas]

Hi Patrick,

Since you're new here, you can mark the thread solved yourself by going to 'Thread Tools' at the top of the thread, and choosing "Mark Thread Solved".

Welcome to vbax!
Matt

[Patrick]

Thanx,
Just what I needed, I was looking for that info on how to do that!

Cheers,
Patrick