Please help with code to plug date in A1 only on certain she
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 7 of 7

Thread: Please help with code to plug date in A1 only on certain she

  1. #1
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Worksheets are named like *1, *2, etc. I want to plug the current date in A1 of the active sheet if the sheet changes but only if its name is like *2.

    My feeble attempt:

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    For ActiveSheet
    If Sh.Name = Like "*2" Then
    Sh.[A1] = Date
    End If
    Application.EnableEvents = True
    End Sub

    I know there's a problem with the "For..." line but I don't want to have to loop through every sheet. How can I have this work just on the active sheet?

  2. #2
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    This worked for me with the SheetActivate event.

    ----------------------
    Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Application.EnableEvents = False
    If Sh.Name Like "*2" Then
    Sh.[A1] = Date
    End If
    Application.EnableEvents = True
    End Sub
    ----------------------

    Did you want the code to cycle through all worksheets and add the date in A1 if the active sheet ends in 2?

    Regards,
    Jay

  3. #3
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I can't use the Sheet Activate event. There is lots of looking but not much changing. I only want to update A1 with current date if a change is made. So I only want the If statement to run against the active sheet.

  4. #4
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    It just struck me. This code will only be placed on worksheets like *2 so I don't even need the If statement. Duh!

  5. #5
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Pasted this code into sheets like *2 but nothing happens when sheet is changed. What's wrong?

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    Sh.[A1] = Date
    Application.EnableEvents = True
    End Sub

  6. #6
    MrExcel MVP Jay Petrulis's Avatar
    Join Date
    Mar 2002
    Location
    Chicago, IL USA
    Posts
    2,040
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi,

    OK, now I understand. One thing -- are you going to insert the code in a sheet module on any sheet with a name ending in 2?

    No need, your code works fine if you take out the = sign before the Like and delete the For ... line

    This will ensure that you are always covered on this and will be more efficient.

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    If Sh.Name Like "*2" Then
    Sh.[A1] = Date
    End If
    Application.EnableEvents = True
    End Sub

  7. #7
    Board Regular
    Join Date
    Feb 2002
    Location
    Clovis, NM
    Posts
    334
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

      
    Thanks, Jay. That seems to work. The only thing I don't fully understand is what's the difference in putting this code in a module vs. This Workbook?

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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

 

 
DMCA.com