Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Dropdown box coding

Posted on 2008-06-23
17
Medium Priority
?
167 Views
Last Modified: 2011-10-19
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
0
Comment
Question by:f19l
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
17 Comments
 
LVL 48

Expert Comment

by:jpaulino
ID: 21845261
Would you use an userform or place the dropdown on the worksheet ?

Also you want to know how to fill the dropdown ?
0
 

Author Comment

by:f19l
ID: 21845323
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
 
LVL 48

Accepted Solution

by:
jpaulino earned 2000 total points
ID: 21845660
Two example in the attach file.

Check if any of this two helps
example.xls
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:f19l
ID: 21845786
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 21845845
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
 

Author Comment

by:f19l
ID: 21846109
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 21846146
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
 

Author Comment

by:f19l
ID: 21846605
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 21846620
Can you show the code you have ? There's always a solution.
0
 

Author Comment

by:f19l
ID: 21846668
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
 
LVL 48

Expert Comment

by:jpaulino
ID: 21846921
Check this example:
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21846929
Now with the file :)
example-2.xls
0
 

Author Comment

by:f19l
ID: 21847032
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
 

Author Comment

by:f19l
ID: 21847127
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
 

Author Comment

by:f19l
ID: 21847368
I think I have managed to solve the problem! Thanks for your help.
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21847383
Ok, i'm finishing the sample I will upload asap
0
 
LVL 48

Expert Comment

by:jpaulino
ID: 21847423
Ok, thanks for the grade!

Here's another example
example-3.xls
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

598 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question