ComboBox not loop around

[satoshi_sd]

I have a ComboBox (active x) with other VBA running which has loop on and every time I clicked on the dropdown of the combobox, it will loop around and starts again from the first item (in other word I couldn't even scroll/drag down the list. Is there a way to fix this?

 

[HaHoBe]

Hi satoshi_sd,

maybe you should share your code...

Holger

 

[satoshi_sd]

2 codes here, 1st one - I'm making a running live clock. 2nd one - I want to make a selectable timezone by choosing from dropdownlist

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim Hour As Boolean
Hour = Not (Hour)
Do While Hour = True
DoEvents
Range("B2") = Now()
Loop
End Sub

Private Sub ComboBox1_GotFocus()
    ComboBox1.ListFillRange = "DropDownList"
    Me.ComboBox1.DropDown
End Sub

 

[HaHoBe]

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
Sample Running Time

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=myTimeList

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):

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"):

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:

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

 

[satoshi_sd]

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
Sample Running Time

Cells with Data Validation
Cell	Allow	Criteria
B4	List	=myTimeList

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):

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"):

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:

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

Thank you, do you mind sharing your template? I can't seem to get it working.

 

[satoshi_sd]

Thank you, do you mind sharing your template? I can't seem to get it working.

@HaHoBe nevermind. It's working perfectly. Thank you so much!!!