Need help with with timesheet i am creating

[Dona88]

Hi,

I am creating a timesheet on excel currently.

The sheet consist of different cells,start finish,total hours etc, they are all fine.

I have a drop down menu as one of the cells, which consist of working day, annual leave and public holiday, what I am trying to figure out, is I need to link the drop down option with a certain hour when choosing it,so for example if I choose annual leave as an option on the drop menu in column C, I need the column for total hours column G to automatically change to show 07:12 but I dont how to do it, I know how to add a conditional format to colour code it but how to link a specific value to a drop down option.

Any help would be great.

Thanks

 

[HighAndWilder]

If you create a Worksheet_Change procedure in the Worksheet code module as below then you will be able to check
the value selected using the drop down menu and then do something. Example assumes that the drop down menu is in cell L2.

If you need help in filling in the code then please be specific on what you need.

Private Sub Worksheet_Change(ByVal Target As Range)

    If Target.Address = "$L$2" Then
        
        ' Do something here.
    
          MsgBox Target.Address
    
    End If
    
End Sub

 

[Dona88]

Yes I do need help with the code, what do you need from me. I have tried to attach a photo but saying to large

 

[HighAndWilder]

For each of the options on the drop down menu what do you want to happen?

If you zoom the screen in and use the snipping tool to grab an image we can zoom it out.

 

[Dona88]

There you go, managed to add a photo for you.

So as you can see the drop menu is on column c, what I what to happen is if I choose AL on the drop down menu, is there a way you can automatically have column g (total hours column) to change to 07:12 (7hours 12mins) and al2 automatically change to 03:36 (3 hours 36 minutes). I have tried different formulas, I tried conditional format it but none of that seems to work.
So each option needs to show this on column G -

WD - 00:00, as this will be manually added by the collegues
NWD - 00:00
AL - 07:12
AL2 - 03:36
TIL - 00:00
SL - 07:12
SL2 - 03:36
PH - 07:12

If this is possible, could you please advise how it can be done? So I can formulate on my sheet.

Thank you

 

[HighAndWilder]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim arrTimes() As String
Dim arrOptions() As String

    arrOptions = Split("NWD,AL,AL2,TIL,SL,SL2,PH", ",")
    arrTimes = Split("00:00,07:12,03:36,00:00,07:12,03:36,07:12", ",")
    
    If Target.Column = 3 Then
        
        Range("G" & Target.Row) = arrTimes(Application.WorksheetFunction.Match(Target.Value, arrOptions, 0) - 1)
    
    End If
    
End Sub

 

[Dona88]

So it worked,thank you but an error message came up when I went to choose WD asan option.

Please see attached image.

Should I maybe add WD in the list on the code as 00:00?

 

[HighAndWilder]

Does you list of options on the drop down menu match the list in the code and in the same order?