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

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.

--
Regards,
Tom Ogilvy


"Graham Haughs" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>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

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 wrote:
> 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.
>

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

--
Regards,
Tom Ogilvy

"Graham Haughs" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
>
> 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 wrote:
>> 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.
>>

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 wrote:
> 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
>

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.


--
Regards,
Tom Ogilvy

"Graham Haughs" <(E-Mail Removed)> wrote in message
news:%(E-Mail Removed)...
> 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 wrote:
>> 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
>>

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

Graham

Tom Ogilvy wrote:
> 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.
>
>
> --
> Regards,
> Tom Ogilvy
>
> "Graham Haughs" <(E-Mail Removed)> wrote in message
> news:%(E-Mail Removed)...
>> 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 wrote:
>>> 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
>>>
>
>

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

Graham Haughs wrote:
> Thanks for your efforts Tom. I will persevere and see what mistake I am
> making with your code.
>
> Graham
>
> Tom Ogilvy wrote:
>> 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.
>>
>>
>> --
>> Regards,
>> Tom Ogilvy
>>
>> "Graham Haughs" <(E-Mail Removed)> wrote in message
>> news:%(E-Mail Removed)...
>>> 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 wrote:
>>>> 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
>>>>
>>
>>