inserting multiple items from list

[elisep]

Hi!

I'm a newbie to the list, and a newbie to VB. I found a clip of text that is supposed to do what I want it to, but hasn't been working in my workbook thus far.

from http://www.contextures.com/excelfiles.html#CondFormat -- DataValMultiSelect.zip

(SameCell worksheet)
code looks like this:


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
If Target.Count > 1 Then GoTo exitHandler

On Error Resume Next
Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
On Error GoTo exitHandler

If rngDV Is Nothing Then GoTo exitHandler

If Intersect(Target, rngDV) Is Nothing Then
'do nothing
Else
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
Target.Value = newVal
If Target.Column = 3 Then
If oldVal = "" Then
'do nothing
Else
If newVal = "" Then
'do nothing
Else
Target.Value = oldVal _
& ", " & newVal
End If
End If
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub


------------

Also, would there be a way to prioritize items in the list so that if, say the first two items are selected, those appear first and second, instead of second and then first?

Thanks much for any advice!!

e

 

[Norie]

How is it not working?

What do you actually want to do?

 

[elisep]

A couple days of fiddling got it working. I think I had it saved in the wrong place, initially. Thanks for responding! All good now...

 

[elisep]

Is there a way to save this to the main worksheet code module instead of to each individual page? I'd like this code to work on all the worksheets in my workbook...

Does the code have to be modified now? (I tried saving it to the workbook module and it didn't work any longer, but when I re-placed it on the sheet module it would work again for that sheet)

Thank you!
elise

 

[elisep]

Is there anyone who could let me know how to modify this code to operate for all pages from the workbook module instead of the individual sheet modules? The destination column for the data in each sheet is the same.

elise

 

[Norie]

elise

So you want this to work on every sheet in the workbook?

You could try this which goes in the ThisWorkbook module.

Option Explicit
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim rngDV As Range
Dim oldVal As String
Dim newVal As String
    
    If Target.Count > 1 Then GoTo exitHandler

    On Error Resume Next
    
    Set rngDV = Sh.Cells.SpecialCells(xlCellTypeAllValidation)
    
    On Error GoTo exitHandler

    If rngDV Is Nothing Then GoTo exitHandler

    If Intersect(Target, rngDV) Is Nothing Then
        
    Else
        Application.EnableEvents = False
        newVal = Target.Value
        Application.Undo
        oldVal = Target.Value
        Target.Value = newVal
        If Target.Column = 3 Then
            If oldVal = "" Then
            'do nothing
            Else
                If newVal = "" Then
                'do nothing
                Else
                    Target.Value = oldVal & ", " & newVal
                End If
            End If
        End If
    End If

exitHandler:
Application.EnableEvents = True
End Sub

 

[elisep]

I just tried your code in my worksheet (saved to the correct place), and for some reason it's not working. The data ends up as the second item chosen, instead of adding the second item as a string to the first item.

Do you know why that might be?

 

[Norie]

Where have you put the code?

 

[elisep]

In "ThisWorkbook." The pulldown menu on the left, above the VBA workspace, says "Workbook"