Live-Forum - Die aktuellen Beiträge
Datum
Titel
29.03.2024 13:14:12
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1596to1600
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

Zwei Change Events verknüpfen (Makro schon da)

Zwei Change Events verknüpfen (Makro schon da)
03.01.2018 11:45:37
Nati
Hi Leute,
wie kann ich diese beiden Change Events ( bei Änderung in Spalte K und Änderung in Spalte I), verknüpfen ? (ja, wahrscheinlich mit einer If abfrage, aber ich komme nicht ganz klar wie ich das hierbei handlen soll:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Long
Dim rngz As Range
Dim rang As Range
Dim rangz As Range
On Error GoTo Ende
Application.EnableEvents = False
For Each rngz In Application.Intersect(Columns("K"), Target).Cells
If rngz  0 Then
res = MsgBox("You are about to set the index at: " & rngz & " .Please note, that once the  _
index is set, it can only be changed by pressing the 'New Index / New Change No.'-Button on the  _
top left of the spreadsheet. If you want to set the index at: " & rngz & " please press 'yes'", vbInformation + vbYesNo, "Setting index")
Select Case res
Case vbYes
Case vbNo
rngz.Clear
End Select
End If
Next rngz
For Each rang In Application.Intersect(Columns("I"), Target).Cells
If rang  0 Then
res = MsgBox("You are about to set the index at: " & rang & " .Please note, that once the  _
index is set, it can only be changed by pressing the 'New Index / New Change No.'-Button on the  _
top left of the spreadsheet. If you want to set the index at: " & rang & " please press 'yes'", vbInformation + vbYesNo, "Setting index")
Select Case res
Case vbYes
Case vbNo
rang.Clear
End Select
End If
Next rang
Ende:
Application.EnableEvents = True
End Sub

LG Nati

2
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Zwei Change Events verknüpfen (Makro schon da)
03.01.2018 12:08:02
fcs
Hallo nati,
du kannst die Spalte der geänderten Zelle (Target) prüfen, um entsprechend den Ablauf zu steuern.
Gruß
Franz
Private Sub Worksheet_Change(ByVal Target As Range)
Dim res As Long
Dim rngz As Range
Dim rang As Range
Dim rangz As Range
On Error GoTo Ende
Application.EnableEvents = False
If Target.Column = 11 Then 'Spalte K
For Each rngz In Application.Intersect(Columns("K"), Target).Cells
If rngz  0 Then
res = MsgBox("You are about to set the index at: " & rngz _
& " .Please note, that once the index is set, it can only be changed " _
& "by pressing the 'New Index / New Change No.'-Button on the " _
& "top left of the spreadsheet. If you want to set the index at: " _
& rngz & " please press 'yes'", vbInformation + vbYesNo, "Setting index")
Select Case res
Case vbYes
Case vbNo
rngz.Clear
End Select
End If
Next rngz
ElseIf Target.Column = 12 Then 'Spalte L
For Each rang In Application.Intersect(Columns("I"), Target).Cells
If rang  0 Then
res = MsgBox("You are about to set the index at: " & rang _
& " .Please note, that once the index is set, it can only be changed " _
& "by pressing the 'New Index / New Change No.'-Button on the " _
& "top left of the spreadsheet. If you want to set the index at: " _
& rang & " please press 'yes'", vbInformation + vbYesNo, "Setting index")
Select Case res
Case vbYes
Case vbNo
rang.Clear
End Select
End If
Next rang
End If
Ende:
Application.EnableEvents = True
End Sub

Anzeige
AW: Zwei Change Events verknüpfen (Makro schon da)
03.01.2018 13:02:03
Nati
Super :) Danke Franz =)

29 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige