Extend range

[Profi10]

Hi,

Does anyone know why my code breaks if i change Target.address from cell G4 to a range G4:G100

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$G$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[My Aswer Is This]

Hi,

Does anyone know why my code breaks if i change Target.address from cell G4 to a range G4:G100

Thanks

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$G$4" Then
If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
GoTo Exitsub
Else: If Target.Value = "" Then GoTo Exitsub Else
Application.EnableEvents = False
Newvalue = Target.Value
Application.Undo
Oldvalue = Target.Value
If Oldvalue = "" Then
Target.Value = Newvalue
Else
If InStr(1, Oldvalue, Newvalue) = 0 Then
Target.Value = Oldvalue & ", " & Newvalue
Else:
Target.Value = Oldvalue
End If
End If
End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

Please explain in words with specific details what you're attempting to achieve

 

[Profi10]

Please explain in words with specific details what you're attempting to achieve

I am trying to enable multiple options to be selected in a cell from a drop down list.

 

[Profi10]

I am trying to enable multiple options to be selected in a cell from a drop down list.

Should've mentioned that the code works if target address is a single cell however if I make it a range it breaks

 

[My Aswer Is This]

So, you have a data validation list in range ("G4")
And you want to use a sheet change event code to do something when you select a value from the list in Range (G4). It that true?

To start with I do not believe you can select more than one value at a time in a data validation list
The sheet change event will run when you change the value in range (G4)

 

[Profi10]

So, you have a data validation list in range ("G4")
And you want to use a sheet change event code to do something when you select a value from the list in Range (G4). It that true?

To start with I do not believe you can select more than one value at a time in a data validation list
The sheet change event will run when you change the value in range (G4)

I have a data validation list in range G4:G100 however it is only letting me select multiple items from the drop down list in cell G4 while in cells G5:G100 I can only select one item from the drop down list

 

[Profi10]

As per image in cell G4 I can select numerous items while in any other cell from G5 onwards I can only select one item from the list

 

[Profi10]

Have figured out that if I add the following it works however if you have to do it for hundreds of cells it is very time consuming

If Target.Address = "$G$4" Or Target.Address = "$G$5"

 

[My Aswer Is This]

Well, I see nothing in your image

So, I think your wanting to enter certain values in a Range Like G4 to G33

So, after you enter the value in G4 what do you want to happen?

Are you saying if you already have "Alpha" in G4 and then you select Value Bravo in G4 you want
G4 to now have Alpha Bravo?

 

[Profi10]

Yes I want G4 to have values Alpha, Bravo etc. but I also want to be able to do that across other cells in column G.