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]

Try this,

[FONT="Consolas"][SIZE="2"][COLOR="Navy"]Option Explicit

Private Sub WorkSheet_Change(ByVal Target As Range)

    Dim oldval As String
    Dim newval As String

    If Target.Count > 1 Then Exit Sub
    If Target.Column = 7 Then
        newval = Target.Value
        If newval <> "" Then
            Application.EnableEvents = False
            Application.Undo
            oldval = Target.Value
            If InStr(oldval, newval) = 0 Then
                Target.Value = IIf(oldval = "", "", oldval & ",") & newval
            End If
            Application.EnableEvents = True
        End If
    End If

End Sub[/COLOR][/SIZE][/FONT]

 

[gopdeep]

yeap... it is working

 

[gopdeep]

code which you given is not working if am closing and then opening the file... pls make it as working at all the time

 

[Mohammad Basem]

The code is a worksheet event. It has nothing to do with opening and closing a workbook.

What are you getting exactly?

I've tried it on my copy and, as expected, not affected by opening and closing a workbook.

 

[gopdeep]

just used this code in my new Excel and applied the code to dropdown, it has woreked fine. but it is not working if am closing the file and opening to re-use it.

pblm is - code not working if i re-opening to use the file once closed, it is not allowing me to select multiple options from dropdown... just i could select only 1 from the list as usual...

 

[Mohammad Basem]

This is weird!

I can think of one thing, what is your Excel version?

If it is 2007 or 2010, then what is the file extension?

 

[gopdeep]

am using Excel 2007 and extension is "Trial.xlsm"

 

[Mohammad Basem]

I was thinking that macros are not enabled. When you copied the code it works but when saving the workbook and load it again the code will not run.

Did you check this?

Do you have other code in the workbook that runs normally?

 

[gopdeep]

yes i have other code in my workbook which will run along with it...

macro is in enabled mode only