Autocomplete not triggering worksheet change

[Graham Haughs]

I was at the last hurdle of a program thanks to help from this group
until I hit a snag with the worksheet change procedure below. This
worked perfectly with the target ranges K12:K60 and J12:J60 being data
validation drop down combo boxes. Then because the list were very long I
introduced the worksheets procedure to create an auocomplete box shown
on the excellent site of Debra Dalgleish. This worked fine as far as the
autocomplete was concerned but the worksheet change procedure below was
not triggered. I tried an alternative in that K12:K60 changes a Vlookup
formula in the same rows, 12 to 60 in column L and J12:J60 changes
values in column M rows 12 to 60, so I made them the target range but as
they were changed by formula I presume the worksheet change was not
triggered. I then tried the worksheet calculate event but couldn't see
how to set up target ranges in that type of procedure. I would value any
solution to this situation.


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("K12:K160")) Is Nothing Then
n = Target.Row
If Cells(n, 11) = "Rough Grazing" Then
Cells(n, 15).Value = "No N"
Cells(n, 16).Value = "N"
Cells(n, 17).Value = "Rough Grazing"
Else
If UCase(Cells(n, 12)) = "GRASS" Then
Cells(n, 15).Value = "Low N"
Cells(n, 16).Value = "N"
Else: Cells(n, 15) = ""
Cells(n, 16) = ""
Cells(n, 17) = ""
End If
End If
End If
If Not Intersect(Target, Me.Range("J12:J160")) Is Nothing Then
n = Target.Row
If Cells(n, 10) = "Rough Grazing" Then
Cells(n, 14).Value = ""
Cells(n, 20).Value = "No N"
Else
If UCase(Cells(n, 13)) = "GRASS" Then
Cells(n, 14).Value = ""
Cells(n, 20).Value = "Low"
Else: Cells(n, 20) = ""
Cells(n, 23) = ""

End If
End If
End If
ws_exit:
Application.EnableEvents = True
End Sub

Kind Regards
Graham Haughs
Turriff, Scotland

 

[Tom Ogilvy]

Debra has a lot of code on her site, some only in downloadable workbooks.
So I don't know the specific code, but somewhere in the code, it has to
write the value selected to a cell. It it is doing it by setting the
linkedcell property, then change it to write the value with code instead.

something like
Activecell.Value = Activesheet.Combobox1.Value

This will then cause the change event to fire.

 

[Graham Haughs]

Still struggling a bit Tom, I tried what you suggested but didn't seem
to help. The two relevant procedures are below if you are good enough to
look at it.
Graham

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = Target.Address
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub
'=========================================
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet

Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
.Value = ""
End With
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

 

[Tom Ogilvy]

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, _
Cancel As Boolean)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Set ws = ActiveSheet
Cancel = True
Set cboTemp = ws.OLEObjects("TempCombo")
On Error Resume Next
With cboTemp
'clear and hide the combo box
.ListFillRange = ""
.LinkedCell = ""
.Visible = False
End With
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
'if the cell contains a data validation list
Application.EnableEvents = False
'get the data validation formula
str = Target.Validation.Formula1
str = Right(str, Len(str) - 1)
With cboTemp
'show the combobox with the list
.Visible = True
.Left = Target.Left
.Top = Target.Top
.Width = Target.Width + 5
.Height = Target.Height + 5
.ListFillRange = ws.Range(str).Address
.LinkedCell = "" '<== changed
End With
cboTemp.Activate
End If

errHandler:
Application.EnableEvents = True
Exit Sub

End Sub

' added:

Private Sub TempCombo_Click()
Dim rng as Range
set rng = ActiveSheet.OleObjects("TempCombo").TopLeftCell
rng.Value = me.TempCombo.Value
end Sub

 

[Graham Haughs]

Sorry to be a pain Tom but selections from the combo boxes are not being
accepted with this, ie they are taking an entry but not the one
selected, and the event is not being triggered.

Graham

 

[Tom Ogilvy]

I tested it after your post with all code you had posted to date and
including my changes and my added event, and it worked for me. I would
probably add code to clear the selection each time in the TempCombo or
include a mousedown event to do it so you can select the same item - but as
for the issue at hand, as I said, it worked for me.

 

[Graham Haughs]

Thanks for your efforts Tom. I will persevere and see what mistake I am
making with your code.

Graham

 

[Graham Haughs]

Tom,
Just to confirm I got it to work. I hadn't activated the seperate
procedure in the correct place but now I have done what I should have
one in the first place it works perfecly. Many thanks for your efforts.

Graham