Dropdown box coding

Hello,

I am currently designing a spreadsheet that has a number of controls attached to it including a dropdown box, which will display various dates of my choice. I am not sure how I would write VB coding for this so any assistance would be welcome.

Thanks,

f19l
f19lAsked:
Who is Participating?
 
Jorge PaulinoConnect With a Mentor IT Pro/DeveloperCommented:
Two example in the attach file.

Check if any of this two helps
example.xls
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Would you use an userform or place the dropdown on the worksheet ?

Also you want to know how to fill the dropdown ?
0
 
f19lAuthor Commented:
The dropdown box is located on a worksheet and I want to use a pres-set range of dates that are located in another part of the workbook. In other words the dates in the dropdown box will be fixed and never change.
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

 
f19lAuthor Commented:
It looks like you sent only one file but it seems to contain the dropdown box arrangement that I am interested in. However, when I use the VB editor there is nothing there. In the Script editor there is coding but I do not understand it.
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
You didn't look to the first option. Check on B1. The advantage on this is that you can use in a list on all cells.

The second option (the dropdown) are using only formulas (not VBA). You can right click on the dropdown, select format and then in the control tab you can choose the inputrange (the dates) and the cell link. In the cell link, that is set it to C10 will display the position of the selected on the list.Finally in the B10 I use the formula =Index() to get the value from the list.

Hope I was clear
0
 
f19lAuthor Commented:
Okay, I see what you have done. The first option certainly looks like the better of the lot. I tried to replicate what you did on my spreadsheet but I could not. Could you let me know how?
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Ok,

Select the range you want to add the dropdowns, go to menu Data - validation and choose "list". Then in the source select your list.

The only "problem" is that the list must be in the same worksheet.
0
 
f19lAuthor Commented:
Ok, I did that and it all works well. However, I have realized that there might be a problem, how do I identify which date has been selected so that it can be used when I run the rest of the VB code? Would it be better to use one of the other options?
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Can you show the code you have ? There's always a solution.
0
 
f19lAuthor Commented:
It is not complete yet but basically want I would like is that once the user has selected the relevant date he will then click a VB macro button that will identify what date has been selected and then place in it a another cell in the spreadsheet so that it can then be used for calculation purposes.
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Check this example:
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Now with the file :)
example-2.xls
0
 
f19lAuthor Commented:
I do not think this spreadsheet gives me quite what I am looking for. Ideally, using the first option in the prior spreadsheet you sent me, there would be just one cell that would contain the dropdown box listing all specified dates. Once a date had been selected and the relevant macro button clicked the VB code would identify what the date was and place it in another cell.
0
 
f19lAuthor Commented:
I have just been looking at the second option and I think that may solve my problem. The only issue is when I select the dropdown box from the control toolbar and select format I do not have option to select the input range. Could you let me know how you created it on your spreadsheet?
0
 
f19lAuthor Commented:
I think I have managed to solve the problem! Thanks for your help.
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Ok, i'm finishing the sample I will upload asap
0
 
Jorge PaulinoIT Pro/DeveloperCommented:
Ok, thanks for the grade!

Here's another example
example-3.xls
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.