+ Reply to Thread
Results 1 to 3 of 3

Worksheet Activate when user selects sheet NOT when sheet is created from a template sheet

  1. #1
    Registered User
    Join Date
    09-07-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Post Worksheet Activate when user selects sheet NOT when sheet is created from a template sheet

    Sorry to be a pain however i'm using a spreadsheet that has the following bit of code in it. to look at a range and create new sheets based on the entries in that range.

    PHP Code: 
    Sub Add_NameWS2()
    Dim myCell As Range
    Dim ValidName 
    As Boolean
        Application
    .ScreenUpdating False
    With Worksheets
    ("Maintain")
    For 
    Each myCell In .Range(("A1"), .Cells(.Rows.Count1).End(xlUp)).Cells
        ValidName 
    True
    Sheets
    ("Template").Visible xlSheetVisible
        Worksheets
    ("template").Copy after:=Worksheets(Worksheets.Count)
        
    On Error GoTo NameAlreadyUsed
        ActiveSheet
    .Name myCell.Value
        
    If ValidName False Then
            Application
    .DisplayAlerts False
            Worksheets
    (Worksheets.Count).Delete
            Application
    .DisplayAlerts True
        End 
    If
    Next myCell
    End With
        
    ' ****************
        MsgBox "" & vbCrLf & vbCrLf & vbCrLf & "Updated:" & vbCrLf & "New Timesheets created." & vbCrLf & "  ", vbInformation, "Indiuns:"
        Sheets("Maintain").Select
        Range("A1:a17").Select
        Selection.ClearContents
    Range("E9").Select
        Application.ScreenUpdating = True
        ' 
    ****************
    Exit 
    Sub

    NameAlreadyUsed
    :
    ValidName False
    Resume Next
    End Sub 
    this works great to create a new sheet from a template and name it accordingly however the new sheet has the following code.
    PHP Code: 

    Private Sub Worksheet_Activate()
    ' macro to ask if they accept the terms
    '
    Sub UserInput()
    Application.ScreenUpdating False

    Dim iReply 
    As Integer
                Me
    .Protect "test", , , , True
                
    If Range("ae12") = "M" Then
                    Range
    ("W:AA").EntireColumn.Hidden False
                
    ElseIf Range("ae12") = "X" Then
                    Range
    ("W:AA").EntireColumn.Hidden True
                End 
    If
        
    iReply MsgBox(Prompt:="I accept that entering false timesheets is an act of gross misconduct"_
                Buttons
    :=vbYesNotitle:="ACCEPTANCE")
        If 
    iReply vbYes Then
       
    If Range("H1").Value "0.00" Then
        Beep
        msg 
    "You have entries on this timesheet!" vbCrLf "   " vbCrLf "Do you wish to reset your timesheet?" vbCrLf " " vbCrLf "CAUTION! Action CAN NOT be undone!"
        
    style vbYesNo vbQuestion vbDefaultButton2
        title 
    "Action required"
        
    response MsgBox(msgstyletitle)
        If 
    response vbNo Then GoTo Xit 'exit sub
        Me.Protect "test", , , , True '
    change test to your password
        On Error Resume Next
        Range
    ("D14:E44").Value ""
        
    Range("G14:s44").Value ""
        
    Range("B49:b56").Value ""
        
    Range("D49:E56").Value ""
        
    Range("g49:s56").Value ""
        
    ActiveSheet.Range("D14").Select
        MsgBox 
    "Your entries will be removed."vbOKOnly"Indiuns:"
                        
    Else
    Xit:
    ActiveSheet.Range("D14").Select
    Exit Sub
    End 
    If
    Exit 
    Sub
        
        
        
    ElseIf iReply vbNo Then
        MsgBox 
    "File wil now close"vbOKOnly"Indiuns:"
        
    ActiveWorkbook.Save
        Application
    .Quit
       
    Else 'They cancelled (VbCancel)
           Exit Sub
       End If
    Exit Sub
    End Sub 
    again this works great as it askes the user if they accept the conditions, checks to see if the sheet has data and asks if they want to clear it. What it also does is unhide rows W to AA if a condition in a cell is met. These need to be in as once the sheet is created it stays till the user leave. It's used every month once created.
    What i'd like to know is using the Sub Add_NameWS2() macro creates a new sheet however it runs through the Private Sub Worksheet_Activate() macro. is there a was of not having it run the Private Sub Worksheet_Activate() macro when the sheet is created BUT have the macro run when the user acutually selects the sheet just created?

    Sorry i'd load up an example but due to some of the data i'm not allowed without taking a lot out.

    Does anyone have any thoughts?

    Thanks again.

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,585
    Before you create the worksheet turn off events, then turn them back on after.
    Please Login or Register  to view this content.
    If posting code please use code tags, see here.

  3. #3
    Registered User
    Join Date
    09-07-2012
    Location
    England
    MS-Off Ver
    Excel 2010
    Posts
    11

    Re: Worksheet Activate when user selects sheet NOT when sheet is created from a template s

    Thanks Norie works a treat can't believe it was something so simple.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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