Excel Userform :: Time Combobox

[RAKESH230583]

Dear All,

Userform1 :

Combobox1 - Which shows different country list. "Country A, Country B, Country C, Country D, Country E, Country F, Country G, Country H & Country I"

Combobox2 - Which is required to show the Start Time + 1 Hour, based on selection from combobox1.

Now, For each countries there is a pre-defined start time. Which is maintained in Excel sheet Named as "Source".
Column A2:A10 shows country Names and Column E2:E10 shows the default start time.

So, for example If I had select "Country A" from combobox1 then combobox2 must default show me time as 06:30 PM (Default Start Time 05:30:00 PM + 1 Hour). Which can be further modified by the user from combobox2 only.

I am using the below line of cods in combobox1 Click event.

If Me.Combobox1.Value = "Country A" Or If Me.Combobox1.Value = "Country B" Then
Userform1.Combbox2.value = "06:30:00 PM"

ElseIf Me.Combobox1.Value ="Country C" Or Me.Combobox1.Value = "Country D" Or Me.Combobox1.value = "Country E" Then
Usefom1.Combobox2.value ="07:30:00 PM"

(Elseif Continued :"08:30:00 PM" for Country F, G & H & "10:30:00 PM" For Country I.)

Also - Using below codes in Userform_Initialize event for Combobox2 to show the Time list.

Dim H As long
Dim M As Long

For H = 12 To 23
For M = 0 To 59

Me.Combobx2.Additem Format(TimeSerial(H,M,0), "hh:mm:ss AM/PM")
Next M
Next H

Question:

1) Though by using above codes I am getting the results i.e. Start Time + 1 Hour (But - I don't want combobox to show the times which is before the Start Time as per Country selection in Combobox1.

 

[RAKESH230583]

Dear All,

Please confirm that, is there any way out that, time before start time can be restricted to show in Time Combobox1 based on country selection ?

 

[MickG]

Try this in your Userform Module.

Option Explicit
[COLOR="Navy"]Dim[/COLOR] Dic [COLOR="Navy"]As[/COLOR] Object
Private [COLOR="Navy"]Sub[/COLOR] UserForm_Initialize()
[COLOR="Navy"]Dim[/COLOR] H [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long,[/COLOR] M [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Long[/COLOR]
[COLOR="Navy"]Dim[/COLOR] Rng [COLOR="Navy"]As[/COLOR] Range, Dn [COLOR="Navy"]As[/COLOR] Range
[COLOR="Navy"]With[/COLOR] Sheets("Source")
    [COLOR="Navy"]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR="Navy"]End[/COLOR] With

[COLOR="Navy"]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR="Navy"]For[/COLOR] [COLOR="Navy"]Each[/COLOR] Dn [COLOR="Navy"]In[/COLOR] Rng
    Dic(Dn.Value) = Dn.Offset(, 1).Value
[COLOR="Navy"]Next[/COLOR]
ComboBox1.List = Application.Transpose(Dic.Keys)
[COLOR="Navy"]For[/COLOR] H = 12 To 23
 [COLOR="Navy"]For[/COLOR] M = 0 To 59
    Me.ComboBox2.AddItem Format(TimeSerial(H, M, 0), "hh:mm:ss AM/PM")
 [COLOR="Navy"]Next[/COLOR] M
[COLOR="Navy"]Next[/COLOR] H
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]


Private [COLOR="Navy"]Sub[/COLOR] ComboBox1_Change()
ComboBox2.Value = Format(TimeValue(Dic(ComboBox1.Value) - TimeSerial(1, 0, 0)), "hh:mm:ss AM/PM")
[COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

Regards Mick

h

 

[RAKESH230583]

Hi Mick,

Thank you very much

With your solution, Yes Combobox2 does shows the correct time (i.e. Standard Time "From Source Sheet" + 1 Hour) , once country is selected from Combobox1 and if user wants to change it he can change it.

But there is one problem - If user click Combobox2 - List displays all time (as set in H) i.e time before Standard Time also maintained in source sheet.
Cant we have combobox2 to only display time after country standard time.

For Example :
Country A - Standard Time is 05:30 PM

User Form_Initialize
User Selected country A from Combobox1.
Combobox2 - Auto displays 06:30 PM (standard time 05:30 + 1 Hour)

Now, When user try to Manually change the time- User must not get the complete list of time, Starting from 12 (As maintained in H)
Requirement :
From combobox2 - User must only get the option to select time list, post the standard time i.e. 05:30 PM till 23 (as maintained in H) for country A and same goes to other countries as per their standard time maintained in Source sheet.

Please revert in case if above example is not clear.

 

[MickG]

Try this:-

Option Explicit
[COLOR=navy]Dim[/COLOR] Dic [COLOR=navy]As[/COLOR] Object
Private [COLOR=navy]Sub[/COLOR] UserForm_Initialize()
[COLOR=navy]Dim[/COLOR] H [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] M [COLOR=navy]As[/COLOR] [COLOR=navy]Long[/COLOR]
[COLOR=navy]Dim[/COLOR] Rng [COLOR=navy]As[/COLOR] Range, Dn [COLOR=navy]As[/COLOR] Range
[COLOR=navy]With[/COLOR] Sheets("Source")
    [COLOR=navy]Set[/COLOR] Rng = .Range("A2", .Range("A" & Rows.Count).End(xlUp))
[COLOR=navy]End[/COLOR] With

[COLOR=navy]Set[/COLOR] Dic = CreateObject("scripting.dictionary")
Dic.CompareMode = vbTextCompare
[COLOR=navy]For[/COLOR] [COLOR=navy]Each[/COLOR] Dn [COLOR=navy]In[/COLOR] Rng
    Dic(Dn.Value) = Dn.Offset(, 1).Value
[COLOR=navy]Next[/COLOR]
ComboBox1.List = Application.Transpose(Dic.Keys)
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]



Private [COLOR=navy]Sub[/COLOR] ComboBox1_Change()
[COLOR=navy]Dim[/COLOR] c [COLOR=navy]As[/COLOR] [COLOR=navy]Long,[/COLOR] dt [COLOR=navy]As[/COLOR] Date
[COLOR=navy]With[/COLOR] ComboBox2
   .Clear
    .Value = Format(TimeValue(Dic(ComboBox1.Value) - TimeSerial(1, 0, 0)), "hh:mm:ss AM/PM")
    [COLOR=navy]Do[/COLOR] Until dt = TimeSerial(12, 0, 0)
        c = c + 1
        dt = Format(DateAdd("N", c, .Value), "hh:mm:ss AM/PM")
        Me.ComboBox2.AddItem dt
 [COLOR=navy]Loop[/COLOR]
[COLOR=navy]End[/COLOR] With
[COLOR=navy]End[/COLOR] [COLOR=navy]Sub[/COLOR]

Regards Mick

 

[RAKESH230583]

Hi Mick,

Thanks for responding -
Please be noted that after using above i am getting
Run Time Error 380
Could not set the value property. Invalid Property Value.
.Value = Format(TimeValue(Dic(Combobox1.value) - Timeserial(1,0,0)), "hh:mm:ss AM/PM"

 

[MickG]

Try this example file see if it works for you !!!
https://app.box.com/s/2s13wnassy2wse2m8oh0nlc9r0nznckd

 

[RAKESH230583]

Hi Mick,

You are great - Its working great ! Really appreciated...

Thanks a lot for all your support.

 

[MickG]

You're welcome