09. december 2009 - 10:37Der er
7 kommentarer og 1 løsning
2 gange "Private Sub Worksheet_Change(ByVal Target As Range)" i samme ark
Hej har et ark med følgende kode tilkoblet.
Grundlæggende er der 2 script. Det ene skal ændre farven i en række og det andet registrere tiden for et valg fra en dropdownliste. Begge dele foregår på baggrund af vælget i den pågældende liste. Det er en valideringsliste og ligger i I5:I500
Jeg kan næsten regne ud af det er fordi der er 2 "Private Sub Worksheet_Change(ByVal Target As Range)" i samme ark, men hvordan kommer jeg ud over dette problem
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I5:I100")) Is Nothing Then rk = Target.Row Select Case Target.Value Case Is = "Ej kontakt" Sheets("Time").Range("A" & rk).Value = Now() DidCellsChange Case Is = "Kontakt" Sheets("Time").Range("B" & rk).Value = Now() Case Is = "Møde" Sheets("Time").Range("C" & rk).Value = Now() Case Is = "Tilbud" Sheets("Time").Range("D" & rk).Value = Now() Case Is = "Salg" Sheets("Time").Range("E" & rk).Value = Now() Case Is = "Afventer" Sheets("Time").Range("F" & rk).Value = Now() Case Is = "Deadline brev" Sheets("Time").Range("G" & rk).Value = Now() Case Is = "Underskrift" Sheets("Time").Range("H" & rk).Value = Now() Case Is = "Scan" Sheets("Time").Range("I" & rk).Value = Now() Case Is = "Slet" Sheets("Time").Range("J" & rk).Value = Now() Case Is = "Nedskrivning" Sheets("Time").Range("K" & rk).Value = Now() Case Else Exit Sub End Select
DidCellsChange End If
End Sub
Sub DidCellsChange() Dim KeyCells As String ' Define which cells should trigger the KeyCellsChanged macro. KeyCells = "T5:T100"
' If the Activecell is one of the key cells, call the ' KeyCellsChanged macro. If Not Application.Intersect(ActiveCell, Range(KeyCells)) _ Is Nothing Then KeyCellsChanged
End Sub
Sub KeyCellsChanged() Dim Cell As Object
For Each Cell In Range("T5:T100")
If Cell = "Ej kontakt" Then Cell.EntireRow.Interior.ColorIndex = 38 ElseIf Cell = "Kontakt" Then Cell.EntireRow.Interior.ColorIndex = 12 ElseIf Cell = "Møde" Then Cell.EntireRow.Interior.ColorIndex = 43 ElseIf Cell = "Tilbud" Then Cell.EntireRow.Interior.ColorIndex = 47 ElseIf Cell = "Salg" Then Cell.EntireRow.Interior.ColorIndex = 4 ElseIf Cell = "Afventer" Then Cell.EntireRow.Interior.ColorIndex = 44 ElseIf Cell = "Deadlinebrev" Then Cell.EntireRow.Interior.ColorIndex = 46 ElseIf Cell = "Underskrift" Then Cell.EntireRow.Interior.ColorIndex = 10 ElseIf Cell = "Scan" Then Cell.EntireRow.Interior.ColorIndex = 7 ElseIf Cell = "Slet" Then Cell.EntireRow.Interior.ColorIndex = 3 ElseIf Cell = "Nedskrivning" Then Cell.EntireRow.Interior.ColorIndex = 40 ElseIf Cell = "" Then
' Otherwise, set the background to none (default). Cell.EntireRow.Interior.ColorIndex = xlNone
Problemet er at det kun er det første script der afvikles. Det script der skriver tiden ned i et andet skema "Time". Farve scriptet virker ikke. Kun når jeg fjerner det første script. Jeg gik ud fra at det var fordi der ikke kunne være 2 Private Sub Worksheet_Change(ByVal Target As Range) scripts i samme ark.
Hvis du vil have afviklet 2 x kode når en event bliver triggeret (f.eks. Worksheet_Change) så put de 2 kodestumper i 2 subs, og kald de 2 subs i Worksheet_Change
Ja det var vist det forkert jeg fik kopieret ind. Her er først farvescriptet og der efter tidscriptet. Begge indeholder Worksheet_change
FARVE SCRIPT
Private Sub Worksheet_Change(ByVal Target As Excel.Range) If Intersect(Target, Range("I2:I5500")) Is Nothing Then Exit Sub Else DidCellsChange End If End Sub
Sub DidCellsChange() Dim KeyCells As String ' Define which cells should trigger the KeyCellsChanged macro. KeyCells = "I2:I50"
' If the Activecell is one of the key cells, call the ' KeyCellsChanged macro. If Not Application.Intersect(ActiveCell, Range(KeyCells)) _ Is Nothing Then KeyCellsChanged
End Sub
Sub KeyCellsChanged() Dim Cell As Object
For Each Cell In Range("I2:I100")
If Cell = "Ej kontakt" Then Cell.EntireRow.Interior.ColorIndex = 38 ElseIf Cell = "Kontakt" Then Cell.EntireRow.Interior.ColorIndex = 12 ElseIf Cell = "Møde" Then Cell.EntireRow.Interior.ColorIndex = 43 ElseIf Cell = "Tilbud" Then Cell.EntireRow.Interior.ColorIndex = 47 ElseIf Cell = "Salg" Then Cell.EntireRow.Interior.ColorIndex = 4 ElseIf Cell = "Afventer" Then Cell.EntireRow.Interior.ColorIndex = 44 ElseIf Cell = "Deadlinebrev" Then Cell.EntireRow.Interior.ColorIndex = 46 ElseIf Cell = "Underskrift" Then Cell.EntireRow.Interior.ColorIndex = 10 ElseIf Cell = "Scan" Then Cell.EntireRow.Interior.ColorIndex = 7 ElseIf Cell = "Slet" Then Cell.EntireRow.Interior.ColorIndex = 3 ElseIf Cell = "Nedskrivning" Then Cell.EntireRow.Interior.ColorIndex = 40 ElseIf Cell = "" Then
' Otherwise, set the background to none (default). Cell.EntireRow.Interior.ColorIndex = xlNone
End If Next Cell
End Sub
TIDS SCRIPT
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("I5:I100")) Is Nothing Then rk = Target.Row Select Case Target.Value Case Is = "Ej kontakt" Sheets("Time").Range("A" & rk).Value = Now() DidCellsChange Case Is = "Kontakt" Sheets("Time").Range("B" & rk).Value = Now() Case Is = "Møde" Sheets("Time").Range("C" & rk).Value = Now() Case Is = "Tilbud" Sheets("Time").Range("D" & rk).Value = Now() Case Is = "Salg" Sheets("Time").Range("E" & rk).Value = Now() Case Is = "Afventer" Sheets("Time").Range("F" & rk).Value = Now() Case Is = "Deadline brev" Sheets("Time").Range("G" & rk).Value = Now() Case Is = "Underskrift" Sheets("Time").Range("H" & rk).Value = Now() Case Is = "Scan" Sheets("Time").Range("I" & rk).Value = Now() Case Is = "Slet" Sheets("Time").Range("J" & rk).Value = Now() Case Is = "Nedskrivning" Sheets("Time").Range("K" & rk).Value = Now() Case Else Exit Sub End Select End If
Sorry. Jeg fik kun lige læse at det ikke var det rigtige scripts.
Synes godt om
Ny brugerNybegynder
Din løsning...
Tilladte BB-code-tags: [b]fed[/b] [i]kursiv[/i] [u]understreget[/u] Web- og emailadresser omdannes automatisk til links. Der sættes "nofollow" på alle links.