Drop-Down Menu with Multiple Conditions

[DocAElstein]

Hi

.....For making these changes, I've recorded the macro by Macro recorder, and then organized them in the above code.
However, I've some doubts regarding 'changes in macro made by me'.
I think, making a just 'working' macro is different from making a 'smooth and fluent' macro. It seems that a smooth macro puts less loads on system, and do the job efficiently....
So what about this final Macro?
Does it need any changes, or it's just ok??

OK, this is very good news that you have got to grips with the macro recorder.

The macro recorder is both
_ a good start point for a beginner
and also
_ it is still used by senior professional programmes, since it is much more efficient to run a macro recording to get some syntaxes rather than trying to learn the almost infinite syntax variations that VBA has.

You are correct that coding produced by a macro recorder is rarely fine and fluent, and some intelligent changing is usually beneficial.


Bear in mind that
_ I don’t have any experience at all with coding drop down list, so I can only give you some general tips on refining coding given by the macro recorder….A fully refined macro involving drop down lists/ validation I am unqualified/ unable to give
and
_ Also I am still not 100% clear on what is going on.. You say that one of your requirement s is:
4. On clicking this cell "R19" a drop down menu appear with 5 option - "Expired" "Divorced" "Break-Up" "Abandonment" "Enter Reason Manually"
But that does not happen. If I click on cell "R19" , then nothing happens. – It is not possible for anything to happen when cell R19 is clicked on, because… So far in this Thread we have been looking just at the event code of Worksheet_Change
This only reacts to a change. It does not react to a select ( clicking on a cell )

What I perceive as what appears to happen with you macro regarding cell R19 .. is the following:
The drop down validation list in cell R19 is produced when the value “Single-Parent Family” is selected in cell J19
If that drop down list is present in cell R19 , and “Enter Reason Manually” is selected from that drop down list in R19, then the drop down validation list in cell R19 is removed.
So any refinement on your coding that I do will reflect that perception

---

So …. , Some general ideas on changing a macro recorder produced coding .,
Two main points, the third point is not so important
_(i) The macro recorder does not have any idea what you are wanting to do. It tries to record exactly what you do.
_(ii) We have eyes. We must see and select cells in order to do anything. VBA does not have eyes. It does not need to see or select anything
_ ( _(iii) With End With )

The consequences are
_(i) Unnecessary stuff
Often much more is recorded then we need. For example, lots of formatting and lots of options (arguments := ) are included which we can simply remove as it serves no purpose for our requirements
_(ii) Activate, Select
It is rarely required to Activate or Select anything. We as humans do need to do this, but VBA does not.
As simple example:
Something like this:

    Range("R19:Z19").Select
      With Selection

can almost always be replaced by

  
      With Range("R19:Z19")

_(iii) With End With ( http://excelmatters.com/2017/02/28/whos-with-me/ )
The macro recorder produces this a lot. Possibly this is because it produces a lot of extra unnecessary stuff, ( as noted in _(i) )
With End With is helpful to keep things tidy if lots of things are done with something. Some programmers also use multiple With End With. This can be confusing to a beginner. I personally would avoid using it too much. You would have possibly noticed that previously I replaced something like

            With Range("R19").Font
            .Color = 10855845
            '.ColorIndex = 48
            End With

with

            Let Range("R19").Font.Color = 10855845

This is partially personal choice of mine.


So the changes I would do are due to 2 things
The issues discussed in _(i) _(ii) _(iii)
and
re writing the macro to follow what I see as the logic:
The drop down validation list in cell R19 is produced when the value “Single-Parent Family” is selected in cell J19
If that drop down list is present in cell R19 , and “Enter Reason Manually” is selected from that drop down list in R19, then the drop down validation list in cell R19 is removed.
I have done two versions of the next macro, here: https://excelfox.com/forum/showthrea...ll=1#post14880
The two macros are exactly the same except one macro is where I have 'commented out some things, and the other macro has those 'commented things removed


Alan