Change event problem

[julhs]

I need some help with a certain aspect of using the codes below
My conundrum is; using a Data Validation list to select either "m2" or "ha" and using a straight "Change" event the "ComboBox15.Visible = False" line fires immediately.
But when I use an ActiveX Combobox to make the selection from I need to use a "SelectionChange" event
BUT it requires 2 additional click events for "ComboBox15.Visible = False" to be initiated.

Where or what is it I'm missing with the Combox method that requires the additional click events?

Code for Data Validation list change:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("g18")
If rng = "ha" Then
ComboBox15.Visible = False
End If
End Sub

Code for ActiveX Combobox change:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim rng As Range
Set rng = Range("g18")
If rng = "ha" Then
ComboBox15.Visible = False
End If
End Sub

 

[6StringJazzer]

A sheet only has one change event. The event occurs when the content of any cell changes. If you want to detect a change event in different cells you need to test for that.

Private Sub Worksheet_Change(ByVal Target As Range)

   If Target.Column = 1 Then
      ' do stuff for column A
   ElseIf Target.Column = 5 Then
      ' do stuff for column E
   End If

End Sub

 

[julhs]

Hopefully, with your help I have a grasp on the use of “Worksheet Change”, we will see!!
I know I have deviated from the initial question by quite some degree, so appreciate you sticking with me.
Provided I understand you correctly, in post #11, the below is my equivalent of what it is you are saying??

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
Set rng = Range("D32")
If rng = "" Then
rng.Value = "Select a Supplier"
End If
If Target.Address = "$D$32" Then
Range("$D$33").Value = "Select a Chemical"
End If
End Sub

IF this is not the case then please excuse me for wasting your time and patience

 

[6StringJazzer]

Here is the problem. I don't know what you want your code to do, so I can't tell if it is correct.

Here is what the code in your latest post does:

Any time that there is a change in any cell in the sheet, do the following two things (note that a change means "user entered something into the cell"; if the only change is the result of a formula, the event does not occur):

1. Check to see if D32 is blank. If so, populate that cell to say "Select a supplier. Note that if you populate that cell, it will cause a change event and this sub will call itself!
2. Check to see if the cell that changed is D32. If so, populate D33 to say "Select a chemical". Note that if you populate that cell, it will cause a change event and this sub will call itself!

In Worksheet_Change it is a best practice to use this before the code changes any cell in the sheet:

Application.EnableEvents = False

and this before you exit sub

Application.EnableEvents = False

to prevent a recursive call, sometimes infinitely recursive.

 

[julhs]

I’m sorry this post has caused so much confusion; the supplementary question certainly didn’t help either!!
I’ve had to quickly learn everything regards using; Dependent validation lists to external Workbooks, INDIRECT function and multiple Change events etc all from scratch.
My initial question all really stemmed from my desire to use ComboBoxes for the validation and not the standard Data Validation lists that I had set up.

What you said regarding what the code I last posted was doing, is entirely what is intended.
D32 is the selected value of the PRIMARY Data Validation list of “Suppliers”
and D33 is a DEPENDENT Validation list (that uses INDIRECT function linked to D32) to find the list of “Chemicals” supplied by the selected supplier.
So in a nut shell; if there is a “Supplier” selection change in D32, I then need to select a chemical from the NEW suppliers “Chemical” list in D33 (I’ve used named ranges for all of this)

I pray this clarifies things?
I really appreciate the help and advice you have given me, it now means I can get on with my day job of garden landscaping.
Julhs