formatting cell

[rich_cirillo]

Cell A2 has a start time and cell A3 has a finish time

Is there anyway of having a dropdown box in A2 and another dropdown box in A3 which has the current time displayed in 24hrs.....i just select the time in the dropdown box and it displayed the current time (in 24hr time) in the cell

Cheers

RC

 

[Peter_SSs]

Do you need a drop-down with only one value? Could you just have the cells formatted as 24 hr time, select the relevant cell and then type Ctrl+Shift+:

 

[rich_cirillo]

For this particular task i would like a single cell dropdown box. Is it possible?Thanks

 

[Peter_SSs]

This will display & use the time at which you first select the cell in question, so by the time you have clicked the drop-down and selected the value it will be outdated. Whether that is an issue for you I don't know.

1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  Select Case Selection.Address(0, 0)
    Case "A2", "A3"
       With Selection.Validation
        .Delete
        .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Format(Now(), "hh:mm:ss")
    End With
  End Select
End Sub

It strikes me that a better option would be to have the person double click the cell to enter the current time. That could be done with this event code instead of the previous.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
  Select Case Target.Address(0, 0)
    Case "A2", "A3"
      Cancel = True
      Target.Value = Format(Now(), "hh:mm:ss")
  End Select
End Sub

 

[James__S]

this may help me as well