Drop-Down Menu with Multiple Conditions

[DocAElstein]

_ continued from last post

Part A)


This is fairly similar to what we did here: https://excelfox.com/forum/showthrea...-in-excel-cell

My start point, as previously, would be to get some of the syntax for the coding, based on some investigation. This is because I don’t know, and will probably never want to learn, the almost infinite number of formatting possibilities and syntaxes
( By the way, I understand nothing about drop down boxes…. )
A lot of your requirements involve Event coding, so I am going once again into the worksheet object code module.
( Right click on the worksheet tab and select View Code https://imgur.com/klH9ehK )
( One of the main reasons why Microsoft have given us access rights to the object code module of worksheets is because there we find the event macros. They are all already there, and available to us, but we do not always see them until we access them , ( via the drop down lists there in the code module
First Select Worksheet : https://imgur.com/2UqSmkZ
Then choose a macro : https://imgur.com/n3kwQqR ).

From the VBEditor, if I hit the key combination, Ctrl+g , I can get the Immediate window, which I can drag around freely. I can even drag it around anywhere on my screen outside the VB Editor window
( https://imgur.com/MVp4geJ )

My next move is to type in the Immediate window a few things to help gat some syntaxes. First I type a ? , after which I can type typical VBA code lines.
I am looking at the formats in your list table, ( https://excelfox.com/forum/showthrea...ll=1#post14863 , https://excelfox.com/forum/showthrea...ll=1#post14864 )
Here are a few results, ( I got some the command ideas from my previous post: https://excelfox.com/forum/showthrea...ll=1#post14812 )
https://imgur.com/IJKPqSR
? Range("AM15").font.ThemeColor
7
? Range("AM15").font.TintAndShade
0
? Range("AM15").font.Color
10855845
? Range("AM15").font.Colorindex
48
? Range("AM16").font.tintandshade
0
? Range("AM16").font.Bold
Falsch
? Range("AM16").font.Color
6751362
? Range("AM16").font.Colorindex
13
? Range("AM16").font.Bold
Falsch
? Range("AT19").Font.Tintandshade
0
? Range("AT19").Font.Color
0
? Range("AT19").Font.colorindex
-4105
? Range("AT19").Font.Bold
Falsch

I think that gives me some of the formatting syntax that I need ,at least for the first half of the problem.
I probably don’t need all that information.

The coding is then very similar to what we did previously ( https://excelfox.com/forum/showthrea...ll=1#post14814 )
Possibly the use of ElseIf below maybe new to you.
( Some use of the formatting done by the coding may be unnecessary, at least at this stage. … )

Here is the coding for Part A) https://excelfox.com/forum/showthrea...ll=1#post14868

---

Part B)
4. On clicking this cell "R19" a drop down menu appear with 5 option - "Expired" "Divorced" "Break-Up" "Abandonment" "Enter Reason Manually"
5. On selecting an item, it appear in normal color and format
6. On selecting "Enter Reason Manually" from this drop-down, the cell "R19" becomes empty, so that the reason can be entered manually

I am slightly puzzled why you are not simply adding a drop down list here, as in Cell J19

I am not sure why clicking on a cell should be required to make something like a drop down list for that cell. Why not just make a drop down list for that cell?

I think I need some clarity as to your reasons to do this, or possibly I have not understood exactly what you want.
You are asking for approximately 2 things in PartB):
B(i)) A selectable list to appear on cell selection. That is basically what a drop down list is
B(ii)) ( Your 6. ) This is similar to Part A)

The coding to make a selectable list appear based on a cell selection may involve a use of UserForms. This is a somewhat advanced area of VBA. It is not one that I am too familiar with. I discussed this with you before : https://excelfox.com/forum/showthrea...ll=1#post14805
This might require a lot of work, which would take it out of the realms of a free help forum. You might be lucky and find someone willing to do this for you at one of the larger Excel Forums, but I think it is unlikely.

Alternatively, we may be able to get VBA to make a drop down list. I would start this option by running the macro recorder whilst making manually a drop down list for the cell R19. But I am not sure what the advantages of this would be over simply having initially the drop down list there.
I did just record a macro whilst selecting the arrow on a drop down list. Unfortunately , no coding is produced by this: Not all actions taken manually produce coding by the macro recorder. So this does not help us.


I wonder if in PartB) , what you are asking for is something similar to what you asked for here: https://excelfox.com/forum/showthrea...-List-in-Excel ? – You requirement in Part B) , seems to be an attempt again to get a solution for a tree like Horizontal Hierarchical List structure…

I am not personally sure what is the best way for you to proceed with requirement Part B)
The only way I Know to do it would be a complicated coding involving UserForms. It is too advanced for you to be involved with, and it would be a lot of work for somebody to do for you…
Other possibly ways to do this were given by Logit : https://excelfox.com/forum/showthrea...ll=1#post14807
You also said you had ideas for how to do it. https://excelfox.com/forum/showthrea...ll=1#post14806


I think at the end of the day, you wont get an easy answer. Either you or someone else is going to have to take some time to do some work., unless you can find a finished solution that has already been done by someone.

Part B) ( 4. 5. 6. ) is really a completely separate issue to Part A) ( 1. 2. 3. 7. )

Alan