VB code for combobox - can we improve this...

[andrewp7]

Hi all,


I've pretty limited skills when it comes to VB, so enlisted the aid of a colleague when I wanted values in the drop down list to be shown as hh:mm format. FYI a named range is feeding the drop down list & there were issues getting it to appear as hh:mm.


Below is what he came up with, it works but is laggy now that I have 6 of the same comboboxes on the same worksheet.


Can anyone suggest refinements or alternatives please?


Many thanks,


AP


-----------


Private Sub ComboBox88_Change()


On Error GoTo errorhandler

If Range("L37") <> "" Then

Range("L37") = CDbl(ComboBox88.Value)

ComboBox88.Value = Format(ComboBox88.Value, "hh:mm")

Else

Range("L37").Value = ""

End If

Exit Sub

errorhandler:

If Err.Number = 13 Then Exit Sub

End Sub

 

[NARAYANK991]

Hi Andrew ,


I am not very sure what your code is doing , so I'll just describe what I have done to try and reproduce your issues :


I have a range X17:X23 , which has values of time , such as 1:15 , 12:45 , 6:05 ,... This range has been formatted as Time.


I have an ActiveX combobox control , which has its ListFillRange property set to X17:X22 , and its LinkedCell property set to X1.


Its code is as follows :


Private Sub ComboBox1_Change()

Application.EnableEvents = False

ComboBox1.Value = Format(ComboBox1.Value, "hh:mm:ss AM/PM")

Application.EnableEvents = True

End Sub


This ensures that the values displayed in the Combo Box are in the Time format. The Linked Cell X1 also displays the selected value in Time format.


Is this your requirement ?


Narayan

 

[andrewp7]

Many thanks Narayan,


That all works beautifully, however I've run into the same problem that led to the development of the original code.


The Linked Cell is showing the value not in hh:mm format, but as a number so 06:00 appears as 0.25 ie one quarter of a day. I have changed the format on the linked cell & all the way through the process to hh:mm to no effect.


The impact of this is it is voiding the conditional formating I have that references the Linked Cell.


Look forward to hearing your thoughts.


AP

 

[NARAYANK991]

Hi Andrew ,


The Linked Cell displays whatever is in your Combo Box. What is your Combo Box showing ? Is it showing 0.25 or is it showing 06:00 ?


If the Combobox_Change procedure does its job , then the Combo Box should show 06:00.


Narayan

 

[andrewp7]

The Combobox is showing 06:00, the linked cell is showing 0.25.


If you refer to the code at the start of this post you can see how we tried to tackle this problem.


When the value passes from the named range, through the combobox & into the linked cell it looses its hh:mm formating.

 

[NARAYANK991]

Hi Andrew ,


What is the basic problem ? Is it that the linked cell is not showing the Time formatted value ?


In my PC , the linked cell follows the value in the Combo Box faithfully , without the need for any additional formatting. I do not know why you are facing this problem. Just BTW , I am using Excel 2007.


The simple workaround is that you do not use the linked cell directly ; use another cell which contains the following formula :


=TEXT(Linked Cell,"hh:mm:ss AM/PM")


The Time format can be whatever you choose for your needs.


Narayan

 

[andrewp7]

I also thought of passing it through a helper cell as a solution, but was waiting to see what you came up with!


I think we've finally nailed it! Rather than using the LinkedCell field in Properties, I added a line of code & it works like a charm.


Many thanks for your efforts Narayan - much appreciated.


AP


------


Private Sub ComboBox88_Change()


Application.EnableEvents = False

ComboBox88.Value = Format(ComboBox88.Value, "hh:mm")

Range("L37") = ComboBox88.Value

Application.EnableEvents = True


End Sub

 

[NARAYANK991]

Hi Andrew ,


Good for you. Thanks for the feedback.


Narayan