display an item in Combo box as the default and Time display question

[22021974]

Hello all,
I have two questions on the display of information on a form built on excel.
1) COMBO box :- I have a couple of items that i display in a combo box on a form. Everytime when the form is launched i want one particular element to be selected the default.
How do we do that?
2) Time display on a form: I have used the NOW fn. to display time on a form. Everytime the form is launched, the time pops up. But I need this time display to be like a ticking clock which would display the accurate time at any given instance.
Any help would be appreciated. Thanks!!

 

[CubeE101]

For the combo box...
Use the Form_Load area to set the Defaults for the Combo...
example...
Private Sub Form_Load()
Combo1.AddItem ("My Default Item&quot
Combo1.AddItem ("My Next Item&quot
Combo1.ListIndex = 0
End Sub

as far as the time goes...
Add a timer Object... and use this code...
Private Sub Timer1_Timer()
Label1.Caption = Time
End Sub

For this Whole example...
Creat a new form, Add:
a label (Label1)
a combo box (Combo1)
a timer (Timer1)
And Paste this code in the Code section of the form

Private Sub Form_Load()
Combo1.AddItem ("My Default Item&quot
Combo1.AddItem ("My Next Item&quot
Combo1.ListIndex = 0
Label1.Caption = Time
End Sub

Private Sub Timer1_Timer()
Label1.Caption = Time
End Sub



Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[CubeE101]

I'm sorry thats for VB...
for VBA use this for the combo (rename ComboBox1 to Combo1)...

Private Sub UserForm_Initialize()
Combo1.AddItem ("My Default Item&quot
Combo1.AddItem ("My Next Item&quot
Combo1.ListIndex = 0
Label1.Caption = Time
End Sub

For VBA... there is a "timer" function built in... but it is a little more complicated... you need to an endless Do:Loop (as much as I hate to) along with DoEvents to allow other parts of the program (and other programs)... to execute...

Private Sub UserForm_Activate()
mainloop 'start mainloop after form apears
End Sub

Private Sub UserForm_Initialize()
Combo1.AddItem ("My Default Item&quot 'this is item 0
Combo1.AddItem ("My Next Item&quot '(optional) this is item 1
Combo1.ListIndex = 0 'set item to 0 (Default Item)
Label1.Caption = Time 'initialize timer
End Sub

Sub mainloop()
Do
Label1.Caption = Time 'Update time
DoEvents 'allow program execution outside loop
Loop 'For THIS case (ONLY)... (with DoEvents) do not worry about endless loop...
'the Close event WILL terminate the window... and all other events will be handled...
End Sub
Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[22021974]

Thank you for the tip, Josh

 

[rmikesmith]

Josh & 22021974,

Caution! The Do..Loop in procedure mainloop() does not terminate when the Userform is closed. This causes bizarre behavior in Excel (I had to kill it from Task Manager). You will need to set a global boolean to terminate the loop. Example:

Public Kill_Loop As Boolean


Sub mainloop()
  Kill_Loop = False
  Do
    UserForm1.Label1.Caption = "Current Time:  " & Time 'Update time
    DoEvents 'allow program execution outside loop
    Application.StatusBar = Time
  Loop Until Kill_Loop
  
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
  Kill_Loop = True
End Sub

Regards,
Mike

 

[CubeE101]

thats a good idea Mike,
I thought about that after I had posted it and logged off for the night last night

thanks for catching that Sometimes... the BASIC things in life are the best...

or at least the most fun ;-)
-Josh Stribling

 

[JustinEzequiel]

We can use Application.OnTime instead of a 'Do While' loop to show a 'clock' on a form.

I will see if I can whip up something that works.

 

[JustinEzequiel]

in a module, add the following:

Option Explicit

Public Sub test_this()
setthetime
End Sub

Private Sub setthetime()
UserForm1.Label1.Caption = Now
Application.OnTime (Now + TimeValue("00:00:01&quot), "VBAProject.test_this"
End Sub



in your form's code, add the following:

Option Explicit

Private Sub UserForm_Initialize()
test_this
End Sub

Private Sub UserForm_Terminate()
Application.OnTime (Now + TimeValue("00:00:01&quot), "VBAProject.test_this", , False
End Sub



modify the hard-coded values accordingly

change 'UserForm1' to the name of your form
change 'Label1' to the name of your label control
change 'VBAProject' to the name of your project