Microsoft Excel

Herbers Excel/VBA-Archiv

Informationen und Beispiele zum Thema CheckBox
BildScreenshot zu CheckBox CheckBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema MsgBox
BildScreenshot zu MsgBox MsgBox-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema CommandButton
BildScreenshot zu CommandButton CommandButton-Seite mit Beispielarbeitsmappe aufrufen
Informationen und Beispiele zum Thema ToggleButton
BildScreenshot zu ToggleButton ToggleButton-Seite mit Beispielarbeitsmappe aufrufen

Bei CommandButton Calculate() NICHT ausführen | Herbers Excel-Forum


Betrifft: Bei CommandButton Calculate() NICHT ausführen von: Marco Tremer
Geschrieben am: 16.08.2012 11:54:48

Moin,

mal wieder hab ich ein Problem.

Ich habe mehrere CheckBoxen und CommandButtons. Mit einem Sub Worksheet_Calculate() lasse ich verschiedene CommandButtons deaktivieren, wenn bestimmte CheckBoxen aktiviert werden. Einwandvorwegnahme: bei mir funktioniert es nicht mit Worksheet_Change()

Wenn ich nun einen der verbleibenen CommandButtons drücke, sollen von der gerade aktiven Datei in eine Ziedatei Daten kopiert werden. Allerdings funktioniert es nicht, weil vor dem Einfügen der Daten in die Zieldatei das Sub Worksheet_Calculate() ausgeführt wird.

Hier meine Frage: Wie kann ich es verhindern, dass bei Klick auf den (irgend einen) CommandButton der Sub Worksheet_Calculate() ausgeführt wird?

Ich stelle mir so etwas vor:

Private Sub CommandButton_X_Test_X_Click()

'jetzt Worksheet_Calculate() nicht ausführen
'mein Code im CommandButton

End Sub
oder
Private Sub CommandButton_X_Test_X_Click()

'Worksheet_Calculate() ausschalten
'mein Code im CommandButton
'Worksheet_Calculate() anschalten

End Sub
Danke.
Viele Grüße

  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Dirk aus Dubai
Geschrieben am: 16.08.2012 12:10:09

Hallo!

Du kannst Dir eine Globale Variable definieren, z.B. (in ein Modul) Global UserSel as String und dieser Variable einen entsprechenden Wert zuweisen z.B. "jetzt nicht kalkulieren!"

Im Commandbutton Makro dann einfach auf den Wert der globalen Variable pruefen z.B.
If UserSel <> "jetzt nicht kalkulieren!" then
'mein Code im CommandButton
else
'was anderes oder nichts machen
end if


Gruss

Dirk aus Dubai


  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Marco Tremer
Geschrieben am: 16.08.2012 12:23:14

Hallo Dirk (aus Dubai, wow),

leider ist mein VBA-Level nur bescheiden. Kannst Du mir bitte Deine Hinweise so umschreiben, dass ich sie nur noch raus- und reinkopierenbrauche?

Sorry und Danke.

Viele Grüße
Marco aus Hamburg ;-)


  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Josef Ehrensberger
Geschrieben am: 16.08.2012 13:14:16


Hallo Marco,

das löst man so.

Private Sub CommandButton_X_Test_X_Click()
  Dim lngCalc As Long
  
  On Error GoTo ErrExit
  
  With Application
    .EnableEvents = False
    lngCalc = .Calculation
    .Calculation = xlCalculationManual
  End With
  
  'mein Code im CommandButton
  
  ErrExit:
  With Application
    .EnableEvents = True
    .Calculation = lngCalculation
  End With
End Sub






« Gruß Sepp »



  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Marco Tremer
Geschrieben am: 16.08.2012 13:30:37

Hi Sepp,

hm, irgendwie funktioniert es jetzt auch nicht. Diesmal gibt es keine Fehlermeldung. Aber er bleibt einfach stehen.
Mein Code kopiert eine Zeile aus einem anderen Tabellenblatt der Ausgangsdatei, öffnet eine andere Datei, fügt die kopierten Daten dort ein und macht diese wieder zu. Aber nurch die ausgeschalteten Events scheint das Einfügen, Speichern und Schließen ebenfalls ausgeschaltet zu sein.

Wie kann ich das beheben?

Viele Grüße
Marco


  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Josef Ehrensberger
Geschrieben am: 16.08.2012 13:38:36


Hallo Marco,

ohne deine gesamten Code zu kennen, kann ich dir nicht helfen.




« Gruß Sepp »



  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Marco Tremer
Geschrieben am: 16.08.2012 13:48:15

Ok, dann versuche ich das mal verkürzt darzustellen. Ich habe verschiedene Prüfungen, ob Zellen ausgefüllt, Checkboxen aktiviert und Dateien vorhanden sind, in verschiedene Funktionen einbebaut. Für Datei, Ziel und Quelle habe ich Namen und Speicherort festgelegt. Die Namen, Pfade und Funktionen habe ich herausgenommen. Verzeih, mir, wenn ich mit meinen bescheidenen Kenntnissen vielleicht die Formulierung und Porgrammierung getroffen habe. So in etwa sieht das dann aus:

Private Sub CommandButton_X_Test_X_Click()

Dim Datei, Ziel, Quelle As String
Set Zeile = Sheets("Datenbank").Range("A45:O45")

        Zeile.Copy
        Workbooks.Open (Ziel & Datei)
        ActiveWorkbook.ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,  _
Transpose:=False
        ActiveWorkbook.ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
        ActiveWorkbook.Close True

End Sub



  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Josef Ehrensberger
Geschrieben am: 16.08.2012 14:17:32


Hallo Marco,

an dem Code ist, außer den .Selects und .Activates nichts auszusetzen und ich sehe auch keinen Grund, warum er nicht laufen sollte.




« Gruß Sepp »



  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Marco Tremer
Geschrieben am: 16.08.2012 17:31:02

Moin,

ich habe mir den Code nun nochmal genauer angeschaut, mit F8 Schritt für Schritt. Dabei läuft mein "alter" Code wunderbar durch. Sobald er an die Stelle kommt, die die Zieldatei aufruft, springt er zum Sub Worksheet_Calculate(). Danach hat er die kopierten Daten (Zwischenspeicher) verloren.

Wenn ich nun den neuen Code drumherumschreibe, lässt er zwar das Sub Worksheet_Calculate() aus und fügt die kopierten Daten ein, kann aber nicht die Zieldatei schließen. Error, kann Close nicht ausführen.

Der "drumherum-Code" setzt auch nicht mehr die automatische Berechnung ein, die ja ausgeschaltet ist. Wie kann ich das nun umsetzten?

Sorry, wenn ich heute auf die Antwort antworte, bin gleich weg. Aber morgen wieder am Start.
Schönen Feierabend!

Viele Grüße
Marco


  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: {Boris}
Geschrieben am: 16.08.2012 18:33:06

Hi,

Sepp ist da ein winziger Variablenfehler unterlaufen:

.Calculation = lngCalculation

sollte heißen

.Calculation = lngCalc

Damit wird die Berechnung wieder so gesetzt, wie sie vor dem Ausschalten war (also bei Dir sicherlich auf Automatik).

VG, Boris


  

Betrifft: @{Boris} - Danke für's mitdenken ;-)) o.T. von: Josef Ehrensberger
Geschrieben am: 16.08.2012 18:35:50

« Gruß Sepp »



  

Betrifft: Kein Problem... von: {Boris}
Geschrieben am: 16.08.2012 18:41:46

Hi Sepp,

...kommt selten genug vor, dass man bei Dir was ausbessern muss!

VG, Boris


  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Marco Tremer
Geschrieben am: 17.08.2012 11:06:42

Hi Leute,

danke für die Korrektur. Damit wird jetzt auch wieder das automatische Berechnen aktiviert.

Dadurch kam ich zumindest auf die Idee, dass damit die automatischen Berechnungen an- und dann wieder ausgeschaltet werden. Um das für mich zu verstehen, habe ich dem Rekorder bedient und die beiden Zeilen vor und hinter meinen Code gebaut (ohne err etc.):

Application.Calculation = xlManual
Application.Calculation = xlAutomatic

Ob ich das nun um den gesamten Code wickele oder nur um die Zeile, nach dem laut Schritt-für-Schritt-Ablauf das Sub Worksheet_Calculate() aufgerufen wird, hat das keinen Unterschied.
In jedem Fall schein Excel den Zwischenspeicher (die kopierten Daten) zu vergessen, weil die Paste special nicht ausgeführt werden kann. Zumindest deute ich das mit dem wenigen VBA-Wissen.

- Gibt es noch eine andere Methode, um das Aufrufen des Subs Worksheet_Calculate() zu verhindern?

- Gibt es vielleicht eine andere Methode, dass bei Aktivieren und Deaktivieren von Checkboxen (jeweils einzeln und auch in Kombinationen) automatisch verschiedene CommandButtons aktiviert und deaktiviert werden?
Bei Worksheet_Calculate() funktioniert das sofort und life, aber bei Worksheet_Change() muss eine Änderung in einer Zelle vorgenommen werden. Das wird dabei aber nicht gemacht.

Danke.

Viele Grüße
Marco


  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Josef Ehrensberger
Geschrieben am: 17.08.2012 13:53:40


Hallo Marco,

warum zeigst du nicht den gesamten Code?

Immer nur schnippsel zu posten bringt nichts, außer man steht auf Ratespiele.




« Gruß Sepp »



  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Marco Tremer
Geschrieben am: 17.08.2012 15:26:03

Ratespiele ;-)

also gut. Ich finde den Code nur sehr komplex. Außerdem sah ich als einfacher an, nach einer einfachen Lösung, wie "aus und wieder an", zu suchen. Hier ist nun mein Code:

Private Sub CommandButton_X_Test_X_Click()
Set Fso = CreateObject("Scripting.FileSystemObject")
Dim Datei, Ziel, Quelle As String
Quelle = "I:\test\" & Range("C14") & "\KDS\Vorlagen\Tagesgeschäft " & Range("C14") & "\"
Ziel = "I:\test\" & Range("C14") & "\KDS\Tagesgeschäft\" & Range("D14") & "\" & Range("E14") & " _
\"
Datei = "X_Test_X.xlsm"
Set Zeile = Sheets("Datenbank").Range("A45:O45")

If ToggleButton_Produktpartner Then
    Sheets("Produktpartner").Select
    ActiveSheet.Range("B2").Select
    Exit Sub
End If

MakeSureDirectoryPathExists Ziel
If Not Fso.FileExists(Ziel & Datei) Then
    FileCopy Quelle & Datei, Ziel & Datei
End If

If ToggleButton_Datei_nur_öffnen Then
    Workbooks.Open (Ziel & Datei)
    Exit Sub
End If
    
With CommandButton_X_Test_X
    If FileLocked(Ziel & Datei) Then
        .BackColor = RGB(255, 204, 153)
        Exit Sub
    Else
        .BackColor = RGB(150, 150, 150)
        Prüfung_Eingabedaten
        Format_prüfen_Zellschutz_aufheben_Leerzeichen_löschen
        Auswahlfelder_prüfen
        Zeile.Copy
        Workbooks.Open (Ziel & Datei)
        Daten_an_Gesellschaft_übergeben
    End If
End With
End Sub
Die Funktionen Prüfung_Eingabedaten, Format_prüfen_Zellschutz_aufheben_Leerzeichen_löschen und Auswahlfelder_prüfen lasse ich an dieser Stelle weg, weil dort meiner Meinung nichts aufregendes passiert. Letztendlich hakt es hier:

Function Daten_an_Gesellschaft_übergeben()
If ActiveWorkbook.ActiveSheet.Range("A118") <> 0 Then
ActiveWorkbook.Saved = True
ActiveWorkbook.Close True
MsgBox "Tabelle ist voll. Bitte die Daten für den nächsten Arbeitstag eintragen (+1 Tag)"
End
End If

ActiveWorkbook.ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False

If ActiveWorkbook.ActiveSheet.Range("A26") <> 0 Then
ActiveSheet.Unprotect
ActiveWorkbook.ActiveSheet.Range("A9:Q9").Select
Selection.Copy
ActiveWorkbook.ActiveSheet.Range("A27:Q27").Select
ActiveSheet.Paste
ActiveWorkbook.ActiveSheet.Range("A27:Q27").Select
Selection.RowHeight = 30.75
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
If ActiveWorkbook.ActiveSheet.Range("A49") <> 0 Then
ActiveSheet.Unprotect
ActiveWorkbook.ActiveSheet.Range("A9:Q9").Select
Selection.Copy
ActiveWorkbook.ActiveSheet.Range("A50:Q50").Select
ActiveSheet.Paste
ActiveWorkbook.ActiveSheet.Range("A50:Q50").Select
Selection.RowHeight = 30.75
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
If ActiveWorkbook.ActiveSheet.Range("A72") <> 0 Then
ActiveSheet.Unprotect
ActiveWorkbook.ActiveSheet.Range("A9:Q9").Select
Selection.Copy
ActiveWorkbook.ActiveSheet.Range("A73:Q73").Select
ActiveSheet.Paste
ActiveWorkbook.ActiveSheet.Range("A73:Q73").Select
Selection.RowHeight = 30.75
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
If ActiveWorkbook.ActiveSheet.Range("A95") <> 0 Then
ActiveSheet.Unprotect
ActiveWorkbook.ActiveSheet.Range("A9:Q9").Select
Selection.Copy
ActiveWorkbook.ActiveSheet.Range("A96:Q96").Select
ActiveSheet.Paste
ActiveWorkbook.ActiveSheet.Range("A96:Q96").Select
Selection.RowHeight = 30.75
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
ActiveWorkbook.ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
ActiveWorkbook.Close True
End Function Entweder stoppt er beim Befehl, die kopierten Daten einzufügen, oder beim Befehl zu schließen. Laut Schritt-fürSchritt-Anzeige springt er bereits beim Öffnen der Zieldatei auf Worksheet_Calculate(). Letzlich soll nur dieser Sprung verhindert werden. Wenn ich den gesamten Bereich von Worksheet_Calculate() auskommentiere funktioniert es schließlich.

Danke.

Viele Grüße
Marco


  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Josef Ehrensberger
Geschrieben am: 17.08.2012 15:44:03


Hallo Marco,

der Code ist nicht komplex, er ist Schrott!

Und ob in den nicht gezeigten Funktionen der Hund begraben liegt bleibt auch im Dunkeln.

Entferne erst mal alle .Selects und .Activates und bring den Code ein eine lesbare Form, dann schau ich noch mal drüber, so hole ich mir ja Augenkrebs.


« Gruß Sepp »



  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Marco Tremer
Geschrieben am: 17.08.2012 16:04:44

Rudi, sag nicht, dass irgendetwas, was ich gemacht habe, Schrott ist. Ich habe Blut und Wasser geschwitzt, unzählige Arbeitsstunden investiert, genervte Kollegen und Chefs riskiert und dieses Forum durch Fragen und Recherchen genutzt. Das Ergebnis ist ein interaktives Tool, welches Daten von links nach rechts kopiert, E-Mails und Briefe schreibt und Datenbanken abfragt. Und das von jemanden mit wenig VBA-Kenntnissen. Es geht dann schon ein wenig ans Selbstwertgefühl, wenn das plötzlich Schrott ist. Auszüge sind Rätselraten, der ganze Code Augenkrebs.

Ist gut, ich reiße mich ja schon zusammen. Fall ich jetzt meinerseits geätzt habe: sorry. Dieses Forum hat mir in den letzten Jahren zuverlässig Hilfe gegeben. Und das soll es auch weiterhin.

Hier ein gekürzter Code. Die für mich unwichtigen Funktionen habe ich ausgelassen, die um die es geht, an entsprechende Stelle integriert:

Private Sub CommandButton_X_Test_X_Click()
Set Fso = CreateObject("Scripting.FileSystemObject")
Dim Datei, Ziel, Quelle As String
Quelle = "I:\test\"
Ziel = "I:\test\"
Datei = "X_Test_X.xlsm"
Set Zeile = Sheets("Datenbank").Range("A45:O45")

MakeSureDirectoryPathExists Ziel
If Not Fso.FileExists(Ziel & Datei) Then
    FileCopy Quelle & Datei, Ziel & Datei
End If

With CommandButton_X_Test_X
    If FileLocked(Ziel & Datei) Then
        .BackColor = RGB(255, 204, 153)
        Exit Sub
    Else
        .BackColor = RGB(150, 150, 150)
        Zeile.Copy
        Workbooks.Open (Ziel & Datei)
        ActiveWorkbook.ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,  _
Transpose:=False
        Application.CutCopyMode = False
   
        ActiveWorkbook.ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
        ActiveWorkbook.Close True

    End If
End With
End Sub
Geht es so? Ich weiß sonst nicht, wie ich es sonst schildern und darstellen kann.

Danke.

Viele Grüße
Marco


  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Josef Ehrensberger
Geschrieben am: 17.08.2012 16:16:57


Hallo Marco,

ich wollte dir nicht die Freude an deinem Werk nehmen, aber ich nenne die Dinge gern beim Namen, und .Select/.Activate sind nun mal zu 99% unnötig, machen den Code langsam, unleserlich und unübersichtlich.

Ich kann eigentlich nicht erkennen, warum der Code nicht läuft, natürlich weiß ich nicht, was sonst noch alles geschieht.
Ich würde das so lösen (ohne .Copy)

Private Sub CommandButton_X_Test_X_Click()
    Dim FSO As Object, objWB As Workbook, Zeile As Range
    Dim Datei As String, Ziel As String, Quelle As String 'jede Variable muss mit Typ deklariert werden
    Dim lngCalc As Long
    
    On Error GoTo ErrExit
    
    With Application
      .ScreenUpdating = False
      .EnableEvents = False
      lngCalc = .Calculation
      .Calculation = xlCalculationManual
      .DisplayAlerts = False
    End With
    
    Quelle = "I:\test\"
    Ziel = "I:\test\"
    Datei = "X_Test_X.xlsm"
    
    Set FSO = CreateObject("Scripting.FileSystemObject")
    
    Set Zeile = Sheets("Datenbank").Range("A45:O45")
    
    MakeSureDirectoryPathExists Ziel
    If Not FSO.FileExists(Ziel & Datei) Then
      FileCopy Quelle & Datei, Ziel & Datei
    End If
    
    With CommandButton_X_Test_X
      If FileLocked(Ziel & Datei) Then
        .BackColor = RGB(255, 204, 153)
        'Exit Sub 'unnötig!
      Else
        .BackColor = RGB(150, 150, 150)
        Set objWB = Workbooks.Open(Ziel & Datei)
        objWB.ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Resize(Zeile.Rows.Count, Zeile.Columns.Count) = Zeile.Value
        'objWB.ActiveSheet.Range("A65536").End(xlUp).Offset(1, 0).Select
        objWB.Close True
      End With
    End If
  End With
  
  ErrExit:
  
  With Err
    If .Number <> 0 Then
      MsgBox "Fehler in Prozedur:" & vbTab & "'CommandButton_X_Test_X_Click'" & vbLf & String(60, "_") & _
        vbLf & vbLf & IIf(Erl, "Fehler in Zeile:" & vbTab & Erl & vbLf & vbLf, "") & _
        "Fehlernummer:" & vbTab & .Number & vbLf & vbLf & "Beschreibung:" & vbTab & _
        .Description & vbLf, vbExclamation + vbMsgBoxSetForeground, _
        "VBA - Fehler in Modul - Modul2"
      .Clear
    End If
  End With
  
  On Error GoTo 0
  
  With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = lngCalc
    .DisplayAlerts = True
  End With
  
  Set objWB = Nothing
  Set FSO = Nothing
  Set Zeile = Nothing
End Sub






« Gruß Sepp »



  

Betrifft: AW: Bei CommandButton Calculate() NICHT ausführen von: Marco Tremer
Geschrieben am: 17.08.2012 16:34:18

Yuhuu, es funktioniert

Puh.
Danke für Deine Geduld!

Viele Grüße
Marco


Beiträge aus den Excel-Beispielen zum Thema "Bei CommandButton Calculate() NICHT ausführen"