Hello all, I've recently just started learning for a few weeks VBA for Excel as I wanted to better utilize Excel in general. As such, I'm sorry if my questions are relatively simple and/ or have been solved through another post.
Currently, as the title explains, I'm trying to get the index number of a drop down list that is created via conditional formatting. I am aware that there are already solutions available on the web for this simple issue of mine, but I am unable to understand their solution as I lack the knowledge in VBA to fully understand it. As such, I would greatly appreciate it if someone could either offer me a solution to my issues along with a simple explanation as to what is going on.
Here is some detailed information regarding my troubles. Basically, as shown in the picture, I am have a sample drop down menu made with conditional formatting. My objective is to return the index value of the selected option to a variable within VBA. For instance, when the user selects "a", "1" is returned to the variable; "2" is returned when "b" is selected, etc.
Temp.png
Thank you very much.
Edit 1: For some further context, I am attempting to basically get a reference to the content of the cell in which a user is able to pick from a fixed drop-down list. I figured if I was able to get the index number of the drop-down list selected, I will later then be able to use a case structure to progress further down the line depending on the user selection. The only other solution that I have managed to though of is a simple 'If-Else' nested structure. While it will work by directly comparing the string of the cell with the various possible options, it really isn't an efficient method of comparison and a very messy solution. I believe the function 'StrComp' is able to perform the same task (correct me if I'm wrong), but in this case, it would be identical to a nested 'If-Else' structure.
Bookmarks