Page 1 of 2 1 2 LastLast
Results 1 to 10 of 11

Thread: Marlett check a date to another sheet

  1. #1

    Marlett check a date to another sheet



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I need an expert like you to solve my problem.

    I have a Attendant Table for Staff, which consists of Name, Staff ID and so on.
    I have already create the code for “Automatic appearance of date in Column D when you Double Click the Column C of attendance column “. The code is like this:


    Code:
    Option Explicit
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
        'Limit Target count to 1
        If Target.Count > 1 Then Exit Sub
        'Isolate Target to a specific range
       If Intersect(Target, Range("CheckBoxs")) Is Nothing Then Exit Sub
        'Set Target font to "marlett"
        Target.Font.Name = "marlett"
        'Check value of target
        If Target.Value <> "a" Then
            Target.Value = "a"    'Sets target Value = "a"
            Cancel = True
            Exit Sub
        End If
        If Target.Value = "a" Then
            Target.ClearContents    'Sets target Value = ""
            Cancel = True
            Exit Sub
        End If
    End Sub
    
    Private Sub Worksheet_Change(ByVal Target As Range)
        'Limit Target count to 1
        If Target.Count > 1 Then Exit Sub
        'Isolate Target to a specific range
       If Intersect(Target, Range("CheckBoxs")) Is Nothing Then Exit Sub
        'Select a specific subset of the range "Ckboxes"
        Select Case Target.Address
        Case Else
    
            If Target.Value = "a" Then
                Target.Offset(0, 1).Value = Date
            Else:
    
                Target.Offset(0, 1).Value = ""
            End If
        End Select
    Name and staff id already available in sheet2. Sheet2 is the database that will be store all the information. Date that will captured is current date(today date) when double click the Column C of attendance column then date will appear. I will clicking in the row at attendance column. The tick is create by using marlett function. You can refer the coding above.The action should be done is like this, if sheet2 have similar name and staff id like in sheet1 then date is should copy or ??? in sheet2 (column date)



    I hope you can help me bout this problem. I attach an image to clear what I need.
    I appreciate your help.
    Click image for larger version. 

Name:	Marlett.jpg 
Views:	66 
Size:	74.8 KB 
ID:	693

    TQ

  2. #2
    Hi,
    Make it simple. You don't need VBA.
    Select B2:B5, change text to Marlett. Type lower case "a" to have tick appear.
    On the cell D2 enter formula =IF(C2="a",TODAY(),""). As soon as you type "a" lower case on column C will show as tick, but in fact that is "a", so formula will add a date anytime has "a" on the column C.
    On the sheet2 cell C2 all you need is formula =Sheet1!D2. Drag this formula down and you are good to go.
    Note: If sheet1 & 2 are not aligned let me know. There is a different formula for that. Also after you put a tick if you need to make dates values only let me know...because formula Today() will change your date every day as soon as you open your file.
    Thanks
    Attached Files Attached Files

  3. #3
    hi Jim..

    Thanks for the formula. It really work on it.
    But I have a problem with it. I want the date captured is same with the date of ticked. The formula that have u given just is captured the same date. I want it will be execute like below:

    If the user have tick today, so it will captured today date (11 July 2012).
    But if the user have tick for tomorrow, so it will capture the date of tomorrow. (12 July 2012).

    Thank so much!

    -nia-

  4. #4
    Nia, are you saying you have the dates on the sheet2, and you want to bring that date to sheet1 when you tick the name?

  5. #5
    Quote Originally Posted by Jim View Post
    Nia, are you saying you have the dates on the sheet2, and you want to bring that date to sheet1 when you tick the name?
    It's okay Jim.
    I've already solve my problem.
    Sorry take your time. Thank so much

  6. #6
    Quote Originally Posted by Jim View Post
    Nia, are you saying you have the dates on the sheet2, and you want to bring that date to sheet1 when you tick the name?
    Jim, based on excel that you attached, what I mean is:

    1) On sheet1, when user tick at column attendance on 12 July 2012, the column of date will show date of 12 July 2012. Same with Sheet2, also appear date of 12 July 2012.

    2) But, when user tick at column attendance on 15 July 2012 (Sheet1), the column of date will show date of 15 July 2012. Same with sheet2, also appear date of 15 July 2012.

    can you understand with my explanation?

  7. #7
    so Nia,
    are you ok now, or do you want me to review?

  8. #8
    Yes I'm okay Jim. Sorry to make you confuse. Can you review again about that?
    I'm really need your help.

  9. #9
    Quote Originally Posted by niailmar View Post
    Yes I'm okay Jim. Sorry to make you confuse. Can you review again about that?
    I'm really need your help.
    OK here how I understand. On the sheet1 you have entered different dates including future dates (column D). When booking has been confirmed with client you want to mark with a tick on sheet1 column C. Now same date you want to appear on sheet 2 for all ticked on sheet 1. See attached for worksheet with modified formulas to meet your needs. I hope this works for you. If not let me know we can modify again until it is right. Good luck!
    Attached Files Attached Files

  10. #10
    Quote Originally Posted by niailmar View Post
    Yes I'm okay Jim. Sorry to make you confuse. Can you review again about that?
    I'm really need your help.
    OK here how I understand. On the sheet1 you have entered different dates including future dates (column D). When booking has been confirmed with client you want to mark with a tick on sheet1 column C. Now same date you want to appear on sheet 2 for all ticked on sheetTick File (1).xlsx1. See attached for worksheet with modified formulas to meet your needs. I hope this works for you. If not let me know we can modify again until it is right. Good luck!

Page 1 of 2 1 2 LastLast

Tags for this Thread

Posting Permissions

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