Excel VBA - If Target.Column N sau global?
Last Updated: Feb 19 2015 10:49, Started by
dUa
, Feb 19 2015 10:49
·
0
#1
Posted 19 February 2015 - 10:49
Va salut,
Am un fisier (sheet) Excel in care am un dropdown list cu multiple select si am nevoie ca multiple select-ul sa poata fi controlat si prin stergerea unei valori eronate. Astfel, am ajuns la acest cod, insa, nefiind programator, ma lovesc de urmatoarea problema: If Target.Column 4care ma limiteaza doar la coloana 4. Eu vreau ca acest cod sa ruleze pe fiecare coloana din Sheet. Intrebarile mele, de necunoscator, sunt: 1. Am reperat corect linia care imi limiteaza doar aceasta coloana? (imi imaginez ca pot enumera fiecare coloana, insa nu vreau numai sa-mi rezolv problema, cat sa si inteleg) 2. Exista o valoare globala pe care o pot adauga a.i. sa-mi ia toate coloanele? Private Sub Worksheet_Change(ByVal Target As Range) Dim rngDV As Range Dim oldVal As String Dim newVal As String Dim lUsed As Long 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 = 4 Then If oldVal = "" Then 'do nothing Else If newVal = "" Then 'do nothing Else lUsed = InStr(1, oldVal, newVal) If lUsed > 0 Then If Right(oldVal, Len(newVal)) = newVal Then Target.Value = Left(oldVal, Len(oldVal) - Len(newVal) - 2) Else Target.Value = Replace(oldVal, newVal & ", ", "") End If Else Target.Value = oldVal _ & ", " & newVal End If End If End If End If End If exitHandler: Application.EnableEvents = True End Sub Va multumesc pentru ajutor. Edited by dUa, 19 February 2015 - 10:50. |
Anunturi
Bun venit pe Forumul Softpedia!
▶ 0 user(s) are reading this topic
0 members, 0 guests, 0 anonymous users