pls help me in this

[gopdeep]

i've written code select multiple options from dropdown...but that is allowing to select the same option again and again...

here is the code which i've written,,,

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
Else
Application.EnableEvents = False
newval = target.Value
Application.Undo
oldval = target.Value
target.Value = newval
If target.Column = 7 Then
If oldval = "" Then
Else
If newval = "" Then
Else
target.Value = oldval & "," & newval
End If
End If
End If
End If
exithandler:
Application.EnableEvents = True
End Sub

i've tried like

If target.Column = 7 Then
If oldval = "" Then
Else
If newval = "" Then
Else
If oldval<>newval Then
target.Value = oldval & "," & newval
Else
MsgBox "Already Selected"
End If
End If
End If

but it doesn't work... can anyone me in this

 

[Mohammad Basem]

Is it possible to share the workbook? You can clear all data but keep column G with its data validation list.

 

[gopdeep]

yeap i can share... but pls lemme knw that how can i share here ??? is there any way ???

 

[Mohammad Basem]

Upload the file to any file hosting site like; 4shared.com, Hotfile.com, Rapidshare.com, etc. and post the link.

 

[gopdeep]

yeap its working correctly... i tried to insert a msgbox in it where i made mistake... just now only i've found & corrected that... consequently it is working now...

thx a lot

 

[Mohammad Basem]

Al-hamdullah, you are most welcome and thank you for the feedback!

 

[Hutchings]

Hello,

I have been following this thread and I have the provided code working. I was wondering if any one can suggest what changes would need to be made to allow this to run in a range of columns rather than just the 7th.

Rather new at macros but I have tried changing the "Target.Column = 7" to something like Target.Column = Range("7:8") or Target.Column = 7,9 but no luck. I would appreciate some help.

Thank You,

Corey.

 

[Mohammad Basem]

To consider more columns use something like,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]If Target.Column = 7 Or _
    Target.Column = 8 Or _
    Target.Column = 9 Then[/COLOR][/SIZE][/FONT]

 

[Hutchings]

Ah that worked perfectly.

Much thanks.

 

[Mohammad Basem]

Most welcome!