How to connect these two codes into one code.

[Zahhhaaaa]

Here's the thing;

One sheet is called "Default", and it has data validation lists, textboxes, comboboxes etc, we don't change anything on this sheet.

Then I use this code to add new sheet;

Sub NewSheet()
Sheets("Default").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(Date, "dd.mm.yyyy")
End Sub

And when this new sheet appears, named after specific date, everything's restored, ok?? I can do changes, write text etc everything I want, and when I select "add new sheet", it gives me an new "fresh" sheet, named after specific date and it looks like "default-sheet".

But what I still need, is that user CANNOT/IS UNABLE TO add more than ONE sheet.

Like this;

Today is 7.21.2011, I select "add new sheet", it gives me an error and messagebox that says "you can add new sheet tomorrow".

When I'm using code ABOVE, it adds more and more sheets everytime I press "add new sheet", but in code BELOW, it gives me a message and I'm not allowed to add more sheets until next day.

Sub NewSheet()
Dim CurrentDay As Integer, NewName As String
If IsNumeric(Right(ActiveSheet.Name, 2)) Then
   CurrentDay = Right(ActiveSheet.Name, 2)
ElseIf IsNumeric(Right(ActiveSheet.Name, 1)) Then
   CurrentDay = Right(ActiveSheet.Name, 1)
Else
   Exit Sub
End If
CurrentDay = CurrentDay + 1
NewName = Format(Date, "dd.mm.yyyy")
Dim checkWs As Worksheet
On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
   Worksheets(ActiveSheet.Name).Copy After:=Worksheets(ActiveSheet.Index)
  Dim oleObj As OLEObject
With ActiveSheet
   .Name = NewName
   .Range("D2").ClearContents
   .Range("D6").ClearContents
   .Range("A31").ClearContents
   .Range("B31").ClearContents
   .Range("C31").ClearContents
   .Range("D31").ClearContents
   .Range("A34:B37").ClearContents
   .Range("C34:D37").ClearContents
   .Range("A40:B43").ClearContents
   .Range("C40:D43").ClearContents
   .Range("A46").ClearContents
   .Range("B46").ClearContents
   .Range("C46").ClearContents
   .Range("D46").ClearContents
   For Each oleObj In ActiveSheet.OLEObjects
      If oleObj.progID = "Forms.TextBox.1" Then oleObj.Object.Value = ""
   Next oleObj
   Dim Shp As Shape
 For Each Shp In ActiveSheet.Shapes
   If Shp.Type = msoTextBox Then
     Shp.TextFrame.Characters.Text = ""
   End If
 Next Shp
End With
 
Else
   Set checkWs = Nothing
   MsgBox "You add new sheet tomorrow."
End If
End Sub

So could you tell me how to connect these two codes.

And also, how do I protect this "default-sheet" with macro-code so that no one can't change values etc. I've tried to hidden it, but then macros don't work. It does add a new sheet, but it comes a hidden sheet and is named like "Default(2)" or something.

[royUK]

I think this

Sub NewSheet()
Dim CurrentDay As Integer
Dim NewName As String
Dim checkWs As Worksheet
Dim oleObj As OLEObject
Dim shp As Shape


If IsNumeric(Right(ActiveSheet.Name, 2)) Then
   CurrentDay = Right(ActiveSheet.Name, 2)
ElseIf IsNumeric(Right(ActiveSheet.Name, 1)) Then
   CurrentDay = Right(ActiveSheet.Name, 1)
Else
   Exit Sub
End If
CurrentDay = CurrentDay + 1
NewName = Format(Date, "dd.mm.yyyy")
Sheets("Default").Copy after:=Worksheets(Worksheets.Count)

On Error Resume Next
Set checkWs = Worksheets(NewName)
If checkWs Is Nothing Then
   Worksheets(ActiveSheet.Name).Copy after:=Worksheets(ActiveSheet.Index)
With ActiveSheet
   .Name = NewName
   .Range("D2").ClearContents
   .Range("D6").ClearContents
   .Range("A31").ClearContents
   .Range("B31").ClearContents
   .Range("C31").ClearContents
   .Range("D31").ClearContents
   .Range("A34:B37").ClearContents
   .Range("C34:D37").ClearContents
   .Range("A40:B43").ClearContents
   .Range("C40:D43").ClearContents
   .Range("A46").ClearContents
   .Range("B46").ClearContents
   .Range("C46").ClearContents
   .Range("D46").ClearContents
   For Each oleObj In ActiveSheet.OLEObjects
      If oleObj.progID = "Forms.TextBox.1" Then oleObj.Object.Value = ""
   Next oleObj
 For Each shp In ActiveSheet.Shapes
   If shp.Type = msoTextBox Then
     shp.TextFrame.Characters.Text = ""
   End If
 Next shp
End With
 
Else
   Set checkWs = Nothing
   MsgBox "You add new sheet tomorrow."
End If
End Sub

Note: group your variable declarations at the top of the code for simplicity

[Zahhhaaaa]

It works, but it adds another sheet too called "Default(2)"

And also, when I select "add new sheet", thing is that User isn't allowed to add more sheets than one per day. It still gives adds sheet, they're all named "Default".

[romperstomper]

Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this, and then please add links to any and all cross-posts in other forums to your first post here.