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]

The SelectionChange event is for selecting a cell. Selecting or changing a combobox will not fire that event. If you want code to run when an item in the combobox is changed, use the ComboBox15_Change event instead.

 

[julhs]

Hi 6stringjazzer
Thanks for response

But that’s the conundrum!!
The “Change” event code above works if I use a Data Validation list,
Eg Make a selection change in DV list to “ha” >> ComboBox15 is “Hidden”
BUT!!!! if I use a ComboBox instead of a Data Validation List to make the selection, then ComboBox15 is NOT hidden??
Only thing I can think of is the syntax required for the ComboBox way is different to that of a DV list?

 

[6StringJazzer]

First of all you may misunderstand what "Selection" means here.

The SelectionChange does not occur because you "select" a different item in a cell having data validation with a dropdown list. It occurs when you click on a cell that is different than the last active cell.

1. Current active cell is A1. You click on B2. Worksheet_SelectionChange occurs.
2. Current active cell is G18. You pick an item from the dropdown list. Worksheet_SelectionChange does not occur, but Worksheet_Change occurs.
3. Current active cell is A1. You click on the combobox and pick an item. ComboBox15_Change occurs. Worksheet_SelectionChange and Worksheet_Change do not occur.

Only thing I can think of is the syntax required for the ComboBox way is different to that of a DV list?

Totally. To detect a change in what is picked in a ComboBox use Sub ComboBox15_Change()

If you explain what you actually want to do, rather than showing code that does not do what you want to do, we can provide the appropriate code.

 

[julhs]

Hi Jeff
Your right when it comes to “What constitutes a Selection”!!
From your last post I just picked up on your ref to ComboBox15_Change, (missed its significance in post # 2)
Will experiment with changes that are necessary.
If I have no joy; then I will upload an example, but both will take me a while (particularly a Xl2bb as I will need to bare down the actual Worksheet to its barebones)
I’ll get back to you tomorrow
Thanks
Julhs

 

[6StringJazzer]

I think XL2BB doesn't support comboboxes or other controls. If you just write a walkthrough of the scenario you want to happen (what actions the user takes, what occurs as a result) I think I can help with the code. I think you're pretty close as it is.

 

[julhs]

While I’ve not tested ComboBox15_Change, I‘ve done a xl2dd that at least gives a sort of picture?
The crux to all this is: if G18 = “ha” then Combobox 16 is hidden, or if G18 = ”m2” then Combobox 15 is hidden
This is the peared down sheet.
It may help to a degree!!!!!!!
The Ornage cells are the CB drop down locations and the Yellow cells are thier rusults

00 Fert-Chemical-Nozzle calculator NEW Currant.xlsm
	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
7
8														This is reference section for ComboBox 15 & 16
9
10			ComboBox14							This is reference section for ComboBox14 list
11
12										Select a Format below				ml	m2	lts	m2 of a ha
13										m2				0.0100	50	0.100	0.0050
14										ha				0.0103	49	0.103	0.0049
15														0.0105	48	0.105	0.0048
16														0.0107	47	0.107	0.0047
17														0.0109	46	0.109	0.0046
18	= CB 14 selection result		m2											0.0112	45	0.112	0.0045
19														0.0114	44	0.114	0.0044
20														0.0117	43	0.117	0.0043
21			=Is CB list selection result of "ha" list					Is Dependent ComboBox15 for "ha" & range						0.0120	42	0.120	0.0042
22														0.0122	41	0.122	0.0041
23														0.0125	40	0.125	0.0040
24			=Is CB list selection result of "m2" list					Is Dependent ComboBox16 for"m2" & range						0.0129	39	0.129	0.0039
25														0.0132	38	0.132	0.0038
26														0.0136	37	0.136	0.0037
27														0.0139	36	0.139	0.0036
28														0.0143	35	0.143	0.0035
29														0.0148	34	0.148	0.0034
30														0.0152	33	0.152	0.0033
Sheet1

 

[julhs]

Hi Jeff
In testing: "IF" I hadn’t just substituted "Private Sub Worksheet_Change (ByVal Target As Range)"
for "Private Sub ComboBox15_Change (ByVal Target As Range)", instead of using "Private Sub ComboBox15_Change()" then your advice would have resulted in the desired outcome sooner!!

If it’s not pushing the boundaries of Forum rules???
Can I ask a connected BUT supplementary question?
Based on my understanding, if you want/need multiple “Change Events” on the same sheet you need to have the
1st change event set as “Sub Worksheet_Change” and then sub OUT the End Sub of the 1st event and then sub OUT the “Sub Worksheet_Change” of the 2nd change event and its "End Sub", so on and so on.
Somewhat silly question but want to clarify??
Do the same rules apply if you use multiple Combobox changes??
Silly in as much as “ComboBox15_Change would be specific to CB15 and NOT to CB16, so NOT a generic change event as in “Worksheet_Change”????

 

[6StringJazzer]

Based on my understanding, if you want/need multiple “Change Events” on the same sheet you need to have the
1st change event set as “Sub Worksheet_Change” and then sub OUT the End Sub of the 1st event and then sub OUT the “Sub Worksheet_Change” of the 2nd change event and its "End Sub", so on and so on.
Somewhat silly question but want to clarify??

I do not understand what you are trying to describe here, particularly the phrase "sub OUT the End Sub of the 1st event" which means nothing to me.

I am going to overexplain, and I hope the answer to your question is in here somewhere. Each worksheet may have only a single Sub Worksheet_Change. That Sub is called by the VBA runtime in a process known as a "callback". Normally you can write a sub and call it from somewhere else in your code. With a callback, you write a sub and the system calls it at a defined time. In this case, Worksheet_Change is called whenever the user changes the content of a cell in that worksheet. If there are multiple conditions you want to check for (for example, user types FINISHED into B4, or types STARTED into C3) you have to have logic in the sub to check which cell changed and check all the desired conditions. Worksheet_Change has one argument Target which is the worksheet range that changed.

Do the same rules apply if you use multiple Combobox changes??
Silly in as much as “ComboBox15_Change would be specific to CB15 and NOT to CB16, so NOT a generic change event as in “Worksheet_Change”????

Each control has to have its own change event Sub. However, in some cases, they all do the same thing, so they could all call a common Sub to do it. There is a technique for one sub to handle the change event for any type of control, like you have 12 checkboxes, one for each month, and they all do the same thing for their own month. This technique involves creating a Class so is not in the VBA 101 course.

 

[julhs]

Thank for the detailed explanation, I’ve not been able to fully digest it, but still trying to.

But to clarify what I meant by “Sub OUT”.
Suppose we only have one change event on Sheet1, code would be something like:

Private Sub Worksheet_Change(ByVal Target As Range)
‘ Code to change item 1 here
End Sub

But if you want 2 or more change events on Sheet1; what I mean by "Sub out", would be something like:

Private Sub Worksheet_Change(ByVal Target As Range)
‘ Code to change item 1 here
           ‘End Sub of 1st Change event is NOT put in/used
           ‘Private Sub Worksheet_Change(ByVal Target As Range) of 2nd Change event is is NOT put in/used
‘ Code to change item 2 here
End Sub

So for 2 or more Change events on same sheet, actual code would be this;

Private Sub Worksheet_Change(ByVal Target As Range)
‘ Code to change item 1 here
‘ Code to change item 2 here
End Sub

Hope that has cleared things up
Many thanks for your help
Julhs