Results 1 to 4 of 4

How to connect these two codes into one code.

Threaded View

  1. #1
    Registered User
    Join Date
    06-26-2011
    Location
    Kuopio
    MS-Off Ver
    Excel 2007
    Posts
    18

    How to connect these two codes into one code.

    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.
    Last edited by Zahhhaaaa; 07-25-2011 at 07:13 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1