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;
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.
So could you tell me how to connect these two codes.
AND ALSO, HOW DO I PROTECT THIS "DEFAULT-SHEET" SO THAT NO ONE CAN'T CHANGE ANYTHING, 'CUZ IF I HIDE IT, MACROS DON'T WORK SOMEHOW :P IT DOES ADD NEW SHEET, BUT IT'S NAMED LIKE "DEFAULT(2) OR SOMETHING AND IT'S ALSO HIDDEN.
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;
Code:
Sub NewSheet()
Sheets("Default").Copy after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Format(Date, "dd.mm.yyyy")
End SubAnd 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.
Code:
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 "Uusi taulukko voidaan lisätä huomenna."
End If
End SubSo could you tell me how to connect these two codes.
AND ALSO, HOW DO I PROTECT THIS "DEFAULT-SHEET" SO THAT NO ONE CAN'T CHANGE ANYTHING, 'CUZ IF I HIDE IT, MACROS DON'T WORK SOMEHOW :P IT DOES ADD NEW SHEET, BUT IT'S NAMED LIKE "DEFAULT(2) OR SOMETHING AND IT'S ALSO HIDDEN.
