1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved: Excel multi selections resticted to specific column

Discussion in 'Business Applications' started by arpgis, Jan 29, 2007.

Thread Status:
Not open for further replies.
Advertisement
  1. arpgis

    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?
     
  2. OBP

    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
     
  3. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
  4. arpgis

    arpgis Thread Starter

    Joined:
    Jan 29, 2007
    Messages:
    15
    That's the one... sorry
     
  5. slurpee55

    slurpee55

    Joined:
    Oct 20, 2004
    Messages:
    7,837
    No problem - now if I only knew enough code to help! LOL - ah well, someone will!
     
  6. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Hi there,

    Restrict what into 'one or two columns'? Can you provide anymore details?
     
  7. arpgis

    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’?
     
  8. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    Why don't you just use a ListBox???
     
  9. arpgis

    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
     
  10. Zack Barresse

    Zack Barresse

    Joined:
    Jul 25, 2004
    Messages:
    5,452
    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?
     
  11. slurpee55

    slurpee55

    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
     
  12. arpgis

    arpgis Thread Starter

    Joined:
    Jan 29, 2007
    Messages:
    15
    Thanks Slurpee55 thats exactly it, sorry to be so vague...
     
  13. Sponsor

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 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/539345

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice