hippy
12-13-2008, 07:51 PM
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
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