Microsoft Excel

Herbers Excel/VBA-Archiv

Bewerten Sie hier bitte das Excel-Portal

Der kurze Weg zum Ziel: Auftragsprogrammierung

Verknüpfen von Funktionen

nach unten

Betrifft: Verknüpfen von Funktionen von: Mirko F.
Geschrieben am: 27.10.2020 12:20:04

Hallo,

ich habe eine Arbeitsmappe mit vielen Sheets.
Es werden auch einige Sheets immer wieder in diese Arbeitsmappe reinkopiert.

in dem Sheet ist folgender VBA Text, mit dem Zwecks ständig auf Änderungen zu reagieren.
Hierbei wird auf die Funktion, die mit im VBA Text des Sheets steht "StatusZeile" aufgerufen.

Public Sub Worksheet_Change(ByVal Target As Range)
'** Mehrfachauswahl über DropDown-Liste (Gültigkeitsprüfung)
'** Einfügen im Code-Container des betreffenden Arbeitsblattes

'** Dimensionierung der Variablen
Dim rngDV As Range
Dim wert_old As String
Dim wertnew As String

Dim iStartZeile As Integer
Dim iLetzteZeile As Integer

iStartZeile = 8
iLetzteZeile = 8

'** Errorhandling
On Error GoTo Errorhandling

'** SPALTE x******Mehrfachauswahl im definierten Bereich (Bsp. B4:B14) durchführen
If Not Application.Intersect(Target, Range("x" & iStartZeile & ":" & "x" & iLetzteZeile)) Is  _
Nothing Then

  '**Range definieren
  Set rngDV = Target.SpecialCells(xlCellTypeAllValidation)
  If rngDV Is Nothing Then GoTo Errorhandling

  '** Prüfen, ob eine gültige Zelle ausgewählt wurde und Werte eintragen
  If Not Application.Intersect(Target, rngDV) Is Nothing Then
    Application.EnableEvents = False
    wertnew = Target.Value
    Application.Undo
    wertold = Target.Value
    Target.Value = wertnew
    If wertold <> "" Then
      If wertnew <> "" Then
        Target.Value = wertold & ", " & wertnew
      End If
    End If
  End If
  Application.EnableEvents = True
End If
'* ENDE SPALTE x
Errorhandling:
Application.EnableEvents = True

Call StatusZeile

End Sub

Sub StatusZeile()
' Leere Statuszeile ausblenden und einblenden
Dim Zelle As Range
Dim ws As Worksheet

Call Unprotect

'ActiveSheet.Unprotect Password:="oktober2020"

Application.ScreenUpdating = False

Set ws = Application.ThisWorkbook.ActiveSheet

For Each Zelle In ws.Range("b20:b98").Cells
If Zelle = "" Then
ws.Rows(Zelle.Row).Hidden = True
Exit Sub
Else
ws.Rows(Zelle.Row).Hidden = False
End If
Next

'ActiveSheet.Protect Password:="oktober2020"
Call Protect
'DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub

Der VBA Text Statuszeile greift wieder rum auf Funktionen zu, die aber in der Arbeitsmappe in den Modulen hinterlegt ist zu.

Option Explicit
Public Function Protect()

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
        , AllowFiltering:=True, Password:="oktober2020"

End Function
Public Function Unprotect()

ActiveSheet.Unprotect Password:="oktober2020"

End Function

Leider wird dann die Funktionen Protect und Unprotect nicht richtig ausgeführt.
Wenn ich den VBA Code so eingebe bei dem Code der StatusZeile funktioniert das.

Mache ich hier etwas falsch mit Public private usw. ?!

lg Mirko

nach oben  nach unten

Betrifft: AW: Verknüpfen von Funktionen
von: Rudi Maintaire
Geschrieben am: 27.10.2020 13:22:11

hallo,
kein Wunder.
Du verlässt die Prozedur nach der ersten gefundenen Zelle =""
Somit wird Protect nicht mehr aufgerufen.
Sub StatusZeile()
 ' Leere Statuszeile ausblenden und einblenden
Dim Zelle As Range
Dim ws As Worksheet

Call Unprotect

Application.ScreenUpdating = False

Set ws = Application.ThisWorkbook.ActiveSheet

For Each Zelle In ws.Range("b20:b98")
  Zelle.EntireRow.Hidden = Zelle = ""
Next

Call Protect

 End Sub
Gruß
Rudi

nach oben  nach unten

Betrifft: AW: Verknüpfen von Funktionen
von: Mirko F.
Geschrieben am: 27.10.2020 14:20:21

Hi Rudi, danke für deine Antwort.
Es geht mir eher um die Unprotect.

Wenn ich nur den Code
ActiveSheet.Unprotect Password:="oktober2020"
einfüge

anstatt
Call Unprotect

funktioniert es.
Also muss es am Händeln der Funktionen untereinander liegen ?!

vg

Mirko

nach oben  nach unten

Betrifft: AW: Verknüpfen von Funktionen
von: Rudi Maintaire
Geschrieben am: 27.10.2020 15:03:12

versuchs mal mit Weitergabe des Blatt an die untergeordneten Prozeduren.
Im Arbeitsblatt:
Private Sub Worksheet_Change(ByVal Target As Range)
  '** Mehrfachauswahl über DropDown-Liste (Gültigkeitsprüfung)
  '** Einfügen im Code-Container des betreffenden Arbeitsblattes
  
  '** Dimensionierung der Variablen
  Dim rngDV As Range
  Dim wertold As String
  Dim wertnew As String
  
  Dim iStartZeile As Integer
  Dim iLetzteZeile As Integer
  
  iStartZeile = 8
  iLetzteZeile = 8
  
  '** Errorhandling
  On Error GoTo Errorhandling
  
  '** SPALTE x******Mehrfachauswahl im definierten Bereich (Bsp. B4:B14) durchführen
  If Not Application.Intersect(Target, Range("x" & iStartZeile & ":" & "x" & iLetzteZeile)) Is  _
_
    Nothing Then
    
    '**Range definieren
    Set rngDV = Target.SpecialCells(xlCellTypeAllValidation)
    If rngDV Is Nothing Then GoTo Errorhandling
    
    '** Prüfen, ob eine gültige Zelle ausgewählt wurde und Werte eintragen
    If Not Application.Intersect(Target, rngDV) Is Nothing Then
      Application.EnableEvents = False
      wertnew = Target.Value
      Application.Undo
      wertold = Target.Value
      Target.Value = wertnew
      If wertold <> "" Then
        If wertnew <> "" Then
          Target.Value = wertold & ", " & wertnew
        End If
      End If
    End If
    Application.EnableEvents = True
  End If
  '* ENDE SPALTE x
Errorhandling:
  Application.EnableEvents = True
  
  Call StatusZeile(Me)
  
End Sub

In einem Modul:
Sub StatusZeile(wks As Worksheet)
  ' Leere Statuszeile ausblenden und einblenden
  Dim Zelle As Range
  
  Application.ScreenUpdating = False
  Call Unprotect(wks)
    
  For Each Zelle In wks.Range("b20:b98")
    Zelle.EntireRow.Hidden = Zelle = ""
  Next
  
  Call Protect(wks)
End Sub

Public Function Protect(wks As Worksheet)
  wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _
          , AllowFiltering:=True, Password:="oktober2020"
End Function

Public Function Unprotect(wks As Worksheet)
  wks.Unprotect Password:="oktober2020"
End Function

Gruß
Rudi

nach oben  nach unten

Betrifft: AW: Verknüpfen von Funktionen
von: Mirko F.
Geschrieben am: 27.10.2020 15:37:16

Hi Rudi Dabke für den Code.
Bekomme jedoch immer die Fehlermeldung, das wks schon als objekt vorhanden ist.

Du sagst ich soll dasin einem Modul machen.
ABER...
die Funktionen Worksheet_Change und auch Statuszeile werden in einer anderen Einzeldatei Z/ Blatt verwendet.
Dann wird dieses Sheet in eine andere Arbeitsmappe X transferiert. Mit dem VBA Code.
Dann soll sich Statuszeile aus dem Modul der Arbeitsmappe X bedienen.

nach oben  nach unten

Betrifft: AW: Verknüpfen von Funktionen
von: Mirko F.
Geschrieben am: 28.10.2020 11:24:12

Hi Rudi Danke.

Habe mir nochmal deine Erste Antwort angeguckt.

Ich hatte da einen Fehler.

Ich habe das jetzt auch so gemacht, dass ich die vba codes in beiden Mappen pflege.
Geht eh nicht anders.

vg

Und vielen vielen Dank. !!!

Beiträge aus dem Excel-Forum zum Thema "Verknüpfen von Funktionen"

Alle Prozeduren und Funktionen auflisten
Handhabung von Arrays in benutzerdefininierten Funktionen
Variablen auf mehrere Arten in Funktionen integrieren
Eine Zusammenstellung von Matrix-Funktionen