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
Solved

Excel Dropdown list.  Multiple items in a string

Posted on 2013-12-29
16
24 Views
Last Modified: 2016-07-12
In the attached file:
DropDown sheet contains 2 Named Ranges
1. DaysOfWeek in Col A
2. Daily Start Times in Col B


STudentRoster contains 3 Named Ranges
1. Student Name in Col A       eg. AAA
2. Days Attending  in Col B     eg MonWedFri
3. Daily End Time in Col C       eg  1500

For each Student I want to be able to select:
1.  multiple Days from Days of Week (in DropDowns sheet) ,  concatenate the Days selected  and enter the  result in Days Attending (Student Roster sheet)

2. Select a value from Daily End Times (in DropDowns sheet) and enter the value in
   Daily End Time (in Student Roster sheet)

A VBA solution would be preferable

Thanks
Ed
0
Comment
Question by:lifeactuary
  • 6
  • 5
  • 2
16 Comments
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39745918
ps.  EE is for asking questions about how to do something.  It isn't a place to give an 'order' for a piece of code.
Explaining what bit you can't do is also useful.
0
 

Author Comment

by:lifeactuary
ID: 39746167
Sorry. I didn't intend to mean it as an "order" Any advice as to how to achieve the results I'm looking for would be great.

Ed
0
 
LVL 81

Expert Comment

by:byundt
ID: 39746217
Ed,
The different Topic Areas may have different expectations regarding coding.

AndyAinscow is very active in Kernel And Operating System Specific Programming and in Windows MFC Programming, and has a Genius rating in each. I'd be quite willing to believe that the expectation is that people posting in those TAs have a full-time job doing programming, so they are looking for a tip or advice on overcoming a vexing issue.

I am active in the Excel, and in that TA we are much more willing to write short macros for people. The definition of short will vary with the Expert, but most of us are willing to write up to a hundred lines of code or so. That ought to be enough for your immediate task.

The starting point, however, is for you to specify which version of Excel you are using, and to post a sample workbook showing sample inputs and desired results. In this case, data for 3 imaginary students ought to suffice.

Brad
0
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 

Author Comment

by:lifeactuary
ID: 39746272
Hi Brad:
Thanks for the tip.
In the attached sheet, I've included an explanation of what I'm trying to accomplish.
Hope this is what you're looking for.
Again, thanks.
Ed
0
 
LVL 44

Expert Comment

by:AndyAinscow
ID: 39746321
Nicely put Brad, that is part of it.
Give a man a fish and you feed him for a day.
Teach a man to fish and you feed him for a lifetime.

I strongly believe it is best to try first oneself and be given advice on the bits one can't achieve.  It is so easy to say I'll study the code tomorrow when given a complete solution.
0
 
LVL 81

Expert Comment

by:byundt
ID: 39746463
Ed,
While you may have tried twice, you haven't actually succeeded in attaching a workbook.

Please review the step by step instructions in my opening Comment.

Brad
0
 

Author Comment

by:lifeactuary
ID: 39746850
Brad:
Thought I was doing it as u told me but guess not.
Any, I hope you got what I just sent. May have sent more than one. Sorryfor the agg.
Ed


<<Ed subsequently mailed byundt the file. He attached it at 6:20 PM CST on 12/30/13>>
MultiSelectDropDownQ28327236.xlsx
0
 
LVL 81

Expert Comment

by:byundt
ID: 39747143
Ed,
If you email the file to me, I'd be glad to post it for you. My address is my screen name at Experts-Exchange.com

If you have already done so (as of your latest Comment), please double-check the address as I have not received it.

Brad
0
 
LVL 81

Expert Comment

by:byundt
ID: 39747229
Ed,
I got the file and (wearing my Topic Advisor hat) attached it to your last Comment.

Brad
0
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39747558
I decided to display a userform with checkboxes to pick the days of the week. This userform is triggered by clicking on a cell in column B using a Worksheet_SelectionChange event sub. The checkboxes will prepopulate with any previous choices the user might have made. If the user clicks OK, the boxes checked will be concatenated as requested. If the "X" in upper right corner or Cancel are clicked, then no change is made.

I used a data validation dropdown for the daily ending time selection. It uses a dynamic named range to restrict the choices to values entered in column C of the DropDowns worksheet. The Refers To property of that named range is:
=DropDowns!$C$2:INDEX(DropDowns!$C$2:$C$8,COUNTA(DropDowns!$C$2:$C$8))

Code for the event sub and userform follow:
'This code goes in the code pane for worksheet StudentRoster
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim cel As Range, targ As Range
Set targ = Range("StudentName").EntireRow.Columns(2)
Set targ = Intersect(targ, Target)
If targ Is Nothing Then Exit Sub

Application.EnableEvents = False
For Each cel In targ.Cells
    cel.Select
    UserForm1.Show
Next
Application.EnableEvents = True
End Sub

Open in new window

'This code goes behind the userform
Dim cel As Range, targ As Range

Private Sub cmdCancel_Click()
UserForm1.Hide
Unload UserForm1
End Sub

Private Sub cndOK_Click()
Dim s As String
If cbMonday.Value = True Then s = s & "Mon"
If cbTuesday.Value = True Then s = s & "Tue"
If cbWednesday.Value = True Then s = s & "Wed"
If cbThursday.Value = True Then s = s & "Thu"
If cbFriday.Value = True Then s = s & "Fri"
If cbSaturday.Value = True Then s = s & "Sat"
If cbSunday.Value = True Then s = s & "Sun"
targ.Value = s
UserForm1.Hide
Unload UserForm1
End Sub

Private Sub UserForm_Initialize()
Dim s As String
Set cel = ActiveCell
Set targ = cel.EntireRow.Range("B1")
s = targ.Value
lblName.Caption = cel.EntireRow.Range("A1").Value
cbMonday.Value = InStr(1, s, "Mon", vbTextCompare) > 0
cbTuesday.Value = InStr(1, s, "Tue", vbTextCompare) > 0
cbWednesday.Value = InStr(1, s, "Wed", vbTextCompare) > 0
cbThursday.Value = InStr(1, s, "Thu", vbTextCompare) > 0
cbFriday.Value = InStr(1, s, "Fri", vbTextCompare) > 0
cbSaturday.Value = InStr(1, s, "Sat", vbTextCompare) > 0
cbSunday.Value = InStr(1, s, "Sun", vbTextCompare) > 0
End Sub

Private Sub UserForm_Terminate()
UserForm1.Hide
Unload UserForm1
End Sub

Open in new window

Brad
MultiSelectDropDownQ28327236.xlsm
0
 

Author Comment

by:lifeactuary
ID: 39747930
When I try to open the file I get a message:

"The Visual Basic Applications (VBA) macros in this workbook are corrupted and have been deleted. The macro corruption most likely exists in the current file. To recover the macros, open a backup copy of this file if you have one."
0
 

Author Comment

by:lifeactuary
ID: 39748213
OK. Did a repair on Office and rebooted. Seems to have cleared it up.
I'll work with it and get back to you
0
 
LVL 81

Expert Comment

by:byundt
ID: 41704565
This question was answered by code (and a sample workbook) posted in https:#a39747558 

The proposed solution is written in VBA (as requested) and satisfies both objectives requested in the question body.

I just downloaded the workbook and confirmed that it works exactly as claimed.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question to Pivot table 1 63
User Authentication using Digital Certificate 2 59
In-state Vs. out-of-state UW Cost 4 40
Please explain purpose of GZIP 4 35
The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
Article by: Leon
Software Metering within our group of companies has always been an afterthought until auditing of software and licensing became a pain point. Orchestrator and SCCM metering gave us the answer and it was an exciting process.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

829 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