show ComboBox at target in sharedworkbook

[hippy]

When the target cell intersects one of the orange ranges in the "Template" sheet of the attachement the current time is inserted in the target cell. For the blue shaded range a combo box is displayed at the target cell, is actived to enable autocomplete from a list of geographical locations. All works very well.

I had created this workbook for a colleague who initially only needed a single access workbook, who now needs a shared work book.The auto time feature works as well, though the combo box following the target cell does not work in the shared workbook.

A fall back position is to create a number of single use workbooks and combine them at the end of each busienss day, though I would prefer a share workbook.

I would appreciate any assistnace to acheive the combo box displaying at the target cell ina sharde workbook.

Code below;


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim CurrentTime As Date
Dim MyCombo As Object
Dim WatchRange As Range
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("R3:R65365, H3:H65365, N3:N65365")) Is Nothing Then
CurrentTime = Time
ActiveCell.Value = CurrentTime
Else
Set WatchRange = Range("K3:K65536")
On Error Resume Next
Me.cboCombo = cboCombo
Set MyCombo = Me.cboCombo

If Not Intersect(Target, WatchRange) Is Nothing Then
With MyCombo
.Top = Target.Top
.Left = Target.Left
.LinkedCell = Target.Address
.Visible = True
Me.cboCombo.Activate
End With
Else
MyCombo.Visible = False
Set WatchRange = Nothing
Set MyCombo = Nothing
End If
End If

End Sub

[lucas]

What if you use a data validation list instead of creating comboboxes on the fly:

see attached.

the list is compiled from a named range "MyList"

[hippy]

Hello Lucas, thanks for replying. The auto-complete feature of the combo box is really what we are after. Many differnet data entry people wor at a service counter. There are some 8500 geographic places names to consider, so that auto-complete feature is just great in the situation.

[lucas]

combo box following the target cell does not work in the shared workbook

how does it not work? does it deny access? error?

In regards to your last post. I understand that you would prefer the comboboxes but I don't see the difference in the end result.....can you explain why the combo is preferable to data validation?

[hippy]

Sorry is it Steve I can not read. Well I could see that I can achieve the autocomplete feature with data validation. If you can advise on autocomplete for data validation i would be very appreciative. May be I am missing something very simple. Thanks.

[lucas]

Hi hippy,
Does the file I posted do what you want it to do? Do you just want to know how it is done?

By the way hippie, if that is the case, did you try it as a shared file to make sure it works?

[hippy]

Hello Steve. The file you had created and attachedas I see it works as a single use or share workbook in terms of providing user access to a combo box listing all the required locations.

However in both cases the user as I see the file will have to scroll to the required location to make a selection, autocomplete is not available, and I could not use a leter key to navigate to the first location begining with that letter.

So may be I am missing something by way of know how on this one, but I can not see how to move ahead. I have read adn tried the procedure at http://www.contextures.com/xlDataVal10.html with the same result as the file you had attached.

Hippy