Solved: Excel multi selections resticted to specific column

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

arpgis

Thread Starter
Joined
Jan 29, 2007
Messages
15
RE the thread - Excel multi selection list results into 1 cell - is it possible to restrict this to one or two columns within the worksheet, if so how?
 

OBP

Joined
Mar 8, 2005
Messages
19,895
Can you post a shortcut to the original thread - Excel multi selection list results into 1 cell
 
Joined
Oct 20, 2004
Messages
7,837
No problem - now if I only knew enough code to help! LOL - ah well, someone will!
 

arpgis

Thread Starter
Joined
Jan 29, 2007
Messages
15
The earlier thread - Excel multi selection list results into 1 cell:
http://forums.techguy.org/business-a...selection+list
allows you to make multiple selections from a list (see code in thread), however the multi selection is applied to all lists in the worksheet.
I have several lists in a worksheet, but only wish to apply the multi selection to one column (or perhaps two later on) is there a way that the multi select can be applied to say a ‘named column’?
 

arpgis

Thread Starter
Joined
Jan 29, 2007
Messages
15
In the worksheet are several list boxes –
some are standard and only require one item to be selected
but some require multi selections

the code in thread ‘Excel multi selection list results into 1 cell’ does exactly what I need – placing multi selections separated by a comma into single cells.

However this is applied to the whole worksheet and enables the user to multi select in all the lists – I need to restrict multi selection to individual columns
 
Joined
Jul 25, 2004
Messages
5,458
Just when I think I'm starting to understand your request, you confuse me again with statements like the last one there. That doesn't make any sense to me. What individual columns?!?! You're either talking about listbox's or range's of values! Which is it?! If listbox's, what type are they (how were they created)? If a range, what is the range and how will you work it?
 
Joined
Oct 20, 2004
Messages
7,837
Zack, I think that what the worksheet does is have cells with dropdown lists that require more than one answer, so they end up looking like "answer a, answer b". But the whole sheet is formatted for that and arpgis just wants that formatting limited to a few columns - so probably the code on the other worksheet needs some addition limiting it to the specified rows.
The current code reads:
Private Sub Worksheet_Change(ByVal Target As Range)
' If InWork Then Exit Sub
' InWork = True
' On Error GoTo NonValidatedCell
' If Selection.Validation.Type = xlValidateList Then
' ColAbs = Target.Column
' RowAbs = Target.Row
' If Sheets("Sheet1").Cells(RowAbs, ColAbs).Value = "." Then
' TotalString = ""
' Else
' TotalString = Sheets("Sheet2").Cells(RowAbs, ColAbs).Value & ", " & Sheets("Sheet1").Cells(RowAbs, ColAbs).Value
' End If
' If Left(TotalString, 1) = "," Then TotalString = Mid(TotalString, 3)
' Sheets("Sheet1").Cells(RowAbs, ColAbs).Value = TotalString
' Sheets("Sheet2").Cells(RowAbs, ColAbs).Value = TotalString
' End If
' InWork = False
'
' Exit Sub
'NonValidatedCell:
' InWork = False
'End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
If InWork Then Exit Sub
InWork = True
On Error GoTo NonValidatedCell
If Selection.Validation.Type = xlValidateList Then
ColAbs = Target.Column
RowAbs = Target.Row
If Sheets("Sheet1").Cells(RowAbs, ColAbs).Value = "." Then
TotalString = ""
Else
Application.Undo
TotalString = Sheets("Sheet1").Cells(RowAbs, ColAbs).Value & ", "
Application.Undo
TotalString = TotalString & Sheets("Sheet1").Cells(RowAbs, ColAbs).Value
End If
If Left(TotalString, 1) = "," Then TotalString = Mid(TotalString, 3)
Sheets("Sheet1").Cells(RowAbs, ColAbs).Value = TotalString
'Sheets("Sheet2").Cells(RowAbs, ColAbs).Value = TotalString
End If
InWork = False

Exit Sub
NonValidatedCell:
InWork = False



End Sub
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Top