Multi-select from drop down list in Excel 97
Hi
I am using the following code to enable multi select from drop down lists in XL2003, but have a group of users requiring this feature in XL97... Public InActivity As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If InActivity Then Exit Sub InActivity = True On Error GoTo NonValidatedCell If Selection.Validation.Type = xlValidateList Then ColAbs = Target.Column If ColAbs < 3 Then GoTo NonValidatedCell RowAbs = Target.Row If Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value = "Delete Contents" Then TotalString = "" Else Application.Undo TotalString = Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value & ", " Application.Undo TotalString = TotalString & Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value End If If Left(TotalString, 1) = "," Then TotalString = Mid(TotalString, 3) Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value = TotalString End If InActivity = False Exit Sub NonValidatedCell: InActivity = False End Sub The spreadsheet opens in XL97 without error but you can only select one item from the list... Selecting a second item just overwrites the first.. Any suggestions?????? |
Multi-select from drop down list in Excel 97
There's information here on Excel 97 data validation and worksheet events:
http://www.contextures.com/xlDataVal08.html#Change arpgis wrote: Hi I am using the following code to enable multi select from drop down lists in XL2003, but have a group of users requiring this feature in XL97... Public InActivity As Boolean Private Sub Worksheet_Change(ByVal Target As Range) If InActivity Then Exit Sub InActivity = True On Error GoTo NonValidatedCell If Selection.Validation.Type = xlValidateList Then ColAbs = Target.Column If ColAbs < 3 Then GoTo NonValidatedCell RowAbs = Target.Row If Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value = "Delete Contents" Then TotalString = "" Else Application.Undo TotalString = Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value & ", " Application.Undo TotalString = TotalString & Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value End If If Left(TotalString, 1) = "," Then TotalString = Mid(TotalString, 3) Sheets("Recreation_Activity").Cells(RowAbs, ColAbs).Value = TotalString End If InActivity = False Exit Sub NonValidatedCell: InActivity = False End Sub The spreadsheet opens in XL97 without error but you can only select one item from the list... Selecting a second item just overwrites the first.. Any suggestions?????? -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
All times are GMT +1. The time now is 01:23 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com