Need A Macro

[syedjaveed]

Hi Team,

Please help me with a coding.

I Generally have 7 Queues (Log in, Meeting, Break, East Queue, West Queue, Others,Log out) in a range B5 as a drop down list

And Just below that i have a start time and end time cells in B6 and B7.

I want such a macro where if i select any dropdown from B5 the start time shud appear and when i change the dropdown then a specific range us given where the Start time and End time should get capture and simultaneously the again the new start time should come when i change the Drop down.

please see the below format. And Suggest if it is possible to do so.

Excel Workbook
	A	B	C	D	E	F	G
1
2	Name	NBK	Person #
3
4
5	Code	Break
6	Start Time
7	End Time
8
9
10	Name	NBK	Person #	Code	Start Time	End Time	Comments
11
12
13
14
15
16
17
18
19
20
21
22
Sheet1

Thanks n Regards
Javeed

 

[Andrew Poulsom]

Do you want the date as well as the time? I think you will find it beneficial.

 

[syedjaveed]

Do you want the date as well as the time? I think you will find it beneficial.

Yes Sir, It will be very helpful.

 

[Andrew Poulsom]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    If Target.Address <> "$B$5" Then Exit Sub
    Application.EnableEvents = False
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & LastRow + 1).Value = Range("A3").Value
    Range("B" & LastRow + 1).Value = Range("B3").Value
    Range("C" & LastRow + 1).Value = Range("C3").Value
    Range("D" & LastRow + 1).Value = Target.Value
    With Range("E" & LastRow + 1)
        .NumberFormat = "dd/mm/yyyy hh:mm:ss"
        .Value = Date + Time
    End With
    Range("B6").Value = Range("E" & LastRow + 1).Value
    If LastRow > 10 Then
        With Range("F" & LastRow)
            .NumberFormat = "dd/mm/yyyy hh:mm:ss"
            .Value = Date + Time
        End With
    End If
    Application.EnableEvents = True
End Sub

I don't think you need to record End Time in B7.

 

[syedjaveed]

Try:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim LastRow As Long
    If Target.Address <> "$B$5" Then Exit Sub
    Application.EnableEvents = False
    LastRow = Range("A" & Rows.Count).End(xlUp).Row
    Range("A" & LastRow + 1).Value = Range("A3").Value
    Range("B" & LastRow + 1).Value = Range("B3").Value
    Range("C" & LastRow + 1).Value = Range("C3").Value
    Range("D" & LastRow + 1).Value = Target.Value
    With Range("E" & LastRow + 1)
        .NumberFormat = "dd/mm/yyyy hh:mm:ss"
        .Value = Date + Time
    End With
    Range("B6").Value = Range("E" & LastRow + 1).Value
    If LastRow > 10 Then
        With Range("F" & LastRow)
            .NumberFormat = "dd/mm/yyyy hh:mm:ss"
            .Value = Date + Time
        End With
    End If
    Application.EnableEvents = True
End Sub

I don't think you need to record End Time in B7.

Hi Sir,

I copied the code into the vb window.

however when iam changing the dropdown(B5) its not working. I mean the start date is not coming.

Please assist how to go about it.

Thank You.

 

[Andrew Poulsom]

The procedure needs to go in the module for the worksheet, not a general module.

 

[syedjaveed]

The procedure needs to go in the module for the worksheet, not a general module.

The Procedure i followed. Please suggest if iam wrong.

From Developer Tab>Visual Basic>(VB window opened)>Insert>Module>In Module1 i pasted the code and saved the workbook as macro enabled workbook

 

[S.H.A.D.O.]

Hi syedjaveed,

The Procedure i followed. Please suggest if iam wrong.

From Developer Tab>Visual Basic>(VB window opened)>Insert>Module>In Module1 i pasted the code and saved the workbook as macro enabled workbook

You need to RIGHT click on the WorkSheet TAB and select View Code, then paste the procedure in there.

I hope this helps!

 

[syedjaveed]

Hi,

Iam doing the same sir.

Please see the below video how iam pasting this code.

excel - YouTube

Regards,
Syed Javeed

 

[Andrew Poulsom]

No, in Excel right click the sheet tab and choose View Code. Then paste the code in the window on the right. The code must go in the module for Sheet1, not in a module that you insert.