Data validation multiple selection VBA code - adaptation to whole column - URGENT HELP!

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE] [/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



Also, not sure why each of you lines starts with "***", but that can go away, too.

Once you make the changes, I'll happily post a solution.

Private Sub Worksheet_Change(ByVal Target As Range)

 ***
    Dim oldVal As String
 ***
    Dim newVal As String
 ***
 ***
If Target.Address(0, 0) <> "E8" Then Exit Sub

 ***

 ***If oldVal <> "" And newVal <> "" Then
 *******
         Target.Value = oldVal & ", " & newVal
 ***
    End If

ReEnable:
 ***
   Application.EnableEvents = True
 ***


End Sub
**

Ok, you're trying to comply and you're new, so I'll help you out.

Here's the original:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As String
Dim newVal As String
If Target.Address(0, 0) <> "E40" Then Exit Sub
On Error GoTo ReEnable
Application.EnableEvents = False
newVal = Target.Value
Application.Undo 
oldVal = Target.Value
Target.Value = newVal
If oldVal <> "" And newVal <> "" Then
Target.Value = oldVal & ", " & newVal
End If
ReEnable: Application.EnableEvents = True
End Sub

And here's the code modified to your request:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oldVal As String
Dim newVal As String
If Not Intersect(Range("E40:E350"), Target) Is Nothing Then
    On Error GoTo ReEnable
    Application.EnableEvents = False
    newVal = Target.Value
    Application.Undo
    oldVal = Target.Value
    Target.Value = newVal
    If oldVal <> "" And newVal <> "" Then
        Target.Value = oldVal & ", " & newVal
    End If
End If
ReEnable: Application.EnableEvents = True
End Sub

Not sure what you mean by "work". The code will run on any cell you list in the Intersect Range, which is currently "E40:E350". You could easily do:

Range("E40:F350")

or

Range("E40:E350, H10:H100")

and since you're applying .Values, any cell in those ranges that have formulas in them will be overwritten to contain only the values (end results) that the formulas calculated. the formula would be gone.

If that's not what you want, please post an example workbook that represents your data set along with a specific example of what you want to happen and what the end result would be.

I have applied your formula to column D.
Only when REASON is selected in column A for Context the values in Column B can be selected ( INDIRECT formula)
I would like to be abel to do the same selection separated by comma like in column D in column B without changing/overriding the INDIRECT formula in column B.

Not sure that can be done, or at least I don't know how to do it. In VBA, as soon as you manipulate the .Value of a cell (as your code is doing), the formula goes away. You can't have both a .Formula and a .Value determining the contents of a cell. Either a .Formula dictates what you see (the .Value), or the cell has a direct .Value with no formula.

You can, however, ditch the formulas and just do everything in VBA instead. Let me know if that's a feasible path for you.

If not, and you want to explore the formula approach further, I suggest starting a new thread with the title "can I manipulate the .Value of a cell while maintaining the .Formula?".

Explain a bit, link to this post, and in this post, include a link to your new thread. Like I said, I'm really not sure how what you're asking is possible, but I am by no means the most experienced user here.