Hi satoshi_sd,
if you want to choose from a dropdownlist to my opinion you should no use
Worksheet_SelectionChange
which gets fired each time the cursor is moved around in the sheet.
Are you looking for something like this:
Sample Running Time in Cell 230202.xlsm |
---|
|
---|
| A | B | C | D | E | F | G | H | I | J | K | L | M | N |
---|
1 | | | | | | | | | | Name | related | | | |
---|
2 | | | | | | | | | | UTC | -1 | | | |
---|
3 | | | | | | | | | | Berlin | 0 | <-- time from computer | | |
---|
4 | | Berlin | | | | | | | | EST | -6 | | | |
---|
5 | 11:40:43 | local time | | | | | | | | JST | 8 | | | |
---|
6 | | | | | | | | | | | | | | |
---|
7 | | | | | | | | | | | | | | |
---|
|
---|
Code in a standard module (I renamed the module to
mod_RunningTimeCell, if newly added should be
ModuleX where
X stands for a running number):
VBA Code:
Public gdteNextStart As Date 'next starting time for macro
Public gdblTime As Double 'time difference from chiosen timezone to computer time
Public Const gdteTimeSpan As Date = "00:00:01" 'time to update macro
'
Sub StartRunningTimeInCell()
Worksheets("Sample Running Time").Range("A5") = Format(Time + gdblTime, "hh:mm:ss")
gdteNextStart = Now + gdteTimeSpan
Application.OnTime gdteNextStart, "StartRunningTimeInCell"
End Sub
Private Sub StopRunningClock()
On Error Resume Next
Application.OnTime EarliestTime:=gdteNextStart, _
Procedure:="StartRunningTimeInCell", _
Schedule:=False
End Sub
Code behind the sheet with the running clock (for my sample
Worksheets("Sample Running Time"):
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
gdblTime = TimeSerial(WorksheetFunction.VLookup(Range("B4"), Range("myTimeList").CurrentRegion, 2, 0), 0, 0)
End Sub
Code in
ThisWorkbook:
VBA Code:
Private Sub Workbook_Open()
StartRunningTimeInCell
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime EarliestTime:=gdteNextStart, _
Procedure:="StartRunningTimeInCell", _
Schedule:=False
End Sub
Ciao,
Holger