Excel VBA Status endlos abfragen bis Tabellenblatt deaktiviert wird?

[Excelmania]

Hallo.

ich möchte gerne über eine Endlosschleife, die beim Aufruf eines Tabellenblattes gestartet wird abfragen, ob sich der Status für den Befehl objekte markierne geändert hat. Wenn dem so ist, soll der ToggleButton zurückgesetzt werden.

Es klappt leider nur nicht. Vielleicht habe ihr eine Idee?

Private Sub Worksheet_Activate()

Dim c As CommandBarButton
Set c = Application.CommandBars.FindControl(, 182) 

Do Until Worksheet_Deactivate

    If c.State = -1 Then
    UserForm1.SetToggleButton (False)
    End If

Loop

end sub

Ergänzung (24. Mai 2021)

Private Sub Worksheet_Activate()

Dim c As CommandBarButton
Set c = Application.CommandBars.FindControl(, 182)

Do while activesheet.activate = true

    If c.State = -1 Then
    UserForm1.SetToggleButton (False)
    End If

Loop

end sub

 

[deineMudda]

@Excelmania Was um alles in der Welt soll das werden? Wenn der User nicht das richtige macht, hast du ne schöne Endlosschleife.

 

[cumulonimbus8]

<blockquote>bis Tabellenblatt deaktiviert wird</blockquote>
Wenn mir nicht Workshhet_BeforeClose einfiele - dann gibt das wirklich eine Endlosschleife bis etwas Gewolltes getan wird.
Aber - ich habe noch nie ein Worksheet deaktiviert, nur ein anders aktiviert… Was also wird da für eine Erbsensuppe gekocht?

CN8

 

[Excelmania]

Guten Morgen.

Es gibt in Excel den Befehlt "Objekte markieren" (standardmäßig ausgeblendet). Wird dieser Verwendet, können Shapes losgelöst vom Koordinatensystem mittels ziehen markiert werden. Durch einen Doppelklick wird jedoch der Modus markieren beendet und somit werden beim ziehen wieder die Zellen markiert.

Den Doppelklick deaktivieren klappt nicht. Daher war meine Idee im Hintergrund beim Aufruf des Tabellenblattes oder alternativ der Userform ein Makro laufen zu lassen, das in Echtzeit den Status des Befehls "Objekte markieren" prüft und sofern der Modus beendet wird, dann die Togglebutton über den ich den Befehl starte zurücksetzt. funktioniert nur leider nicht.

 

[Janush]

Warum hast du das nicht an den ursprünglichen Post drangehängt? Dann wüssten alle worum es geht...mehr oder weniger.

So wie du dir das vorstellst, wird das nichts. Eine Endlosschleife verursacht nur Chaos. Da müsste schonmal mindestens "do events" rein, sonst blockiert dir das Ding alles weg. Es gäbe dann noch application.ontime womit du die Häufigkeit der Abfrage herunter setzen könntest, aber das ist alles nichts Halbes und nichts Ganzes.

Wenn du uns den Workflow deines Programms erläutern würdest, können wir dir vielleicht ein paar Tipps geben, wie man dein Ziel auf eine andere Art erreichen könnte.

 

[Excelmania]

Guten Abend.

wie in meinem vorherige Post geschrieben soll nur der ToggleButton zurück gesetzt werden. Mehr soll nicht passieren. Die Excel-Arbeitsmappe wird auch von weniger versierten Kolleg*innen genutzt werden und die wären sonst verwirrt warummdurch einen Doppelklick der Modus Objekte auswählen deaktiviert wird.

Alternativ und schöner wäre, wenn im Modus Objekte auswählen sich der Doppelklick deaktivieren lies. Aber das geht wohl nur auf einen Zellenbezug.

 

[Janush]

Betrifft das immer nur ein Blatt, oder die gesammte Excel Tabelle? Ist dein UserForm immer aktiv?

 

[Excelmania]

Guten Morgen.

Das betrifft nur ein Tabellenblatt und kann sogar darauf beschränkt werden, wenn die Userform geöffnet ist.

Beim Verlassen des Tabellenblattes wird der Modus "Objekte markieren" beendet.

Worksheet öffnen

Private Sub Worksheet_Activate()
    Notationselemente.Show
    If CommandBars("Ribbon").Controls(1).Height > 100 Then
        Application.CommandBars.ExecuteMso "MinimizeRibbon"
    End If
    Application.FormulaBarHeight = 1
  
    Dim c As CommandBarButton
    Set c = Application.CommandBars.FindControl(, 182)
  
    If c.State = -1 Then
    Application.CommandBars.ExecuteMso ("ObjectsSelect")
    End If
      
End Sub

Worksheet - Beenden

Private Sub Worksheet_Deactivate()
    Unload Userform1
    If Application.CommandBars("Ribbon").Controls(1).Height <= 100 Then
        Application.CommandBars.ExecuteMso "MinimizeRibbon"
    End If
 
    Dim c As CommandBarButton
    Set c = Application.CommandBars.FindControl(, 182)
 
    If c.State = -1 Then
    Application.CommandBars.ExecuteMso ("ObjectsSelect")
    End If
 
    If Application.DisplayFullScreen = True Then
    Application.DisplayFullScreen = False
    End If
 
End Sub

Worksheet - Doppelklick

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

   Dim c As CommandBarButton
   Set c = Application.CommandBars.FindControl(, 182)
   
    If c.State = 0 And Userform1.ToggleButton1.Value = True Then
    userform1.ToggleButton1.Value = False
    Application.CommandBars.ExecuteMso ("ObjectsSelect")
    End If
 
End Sub

Userform

Private Sub ToggleButton1_Click()
    Application.CommandBars.ExecuteMso ("ObjectsSelect")
End Sub

 

[Janush]

Aha, na dann ist es ja recht einfach abzufangen.

BeforeDoubleClick kannst du schonmal raushaun, denn das feuert nicht beim verlassen des Select-Modes.

Dann baust du dir erstmal die folgende Hilfsfunktion in einem Modul deiner Wahl. Am besten in das Modul wo die anderen Hilfsfunktionen drin sind :-)

Private selectKnopf As CommandBarButton

Public Function ButtonIsOn() As Boolean

    If selectKnopf Is Nothing Then Set selectKnopf = Application.CommandBars.FindControl(, 182)

    ButtonIsOn = selectKnopf.State = -1

End Function

Wie du siehst ist selectKnopf außerhalb deklariert. Damit merkt sich Excel das Objekt wenn es einmal zugewiesen wurde und muss das nicht immer wieder neu machen. Sollte es doch verloren gehen, wird es beim nächsten Mal neu zugewiesen.

Mit dieser Hilfsfunktion, fängst du wiefolgt das selection change event in deinem Worksheet ab.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    userform1.ToggleButton1.Value = ButtonIsOn

End Sub

Selection Change feuert jedes Mal wenn eine neue Zelle in deinem Worksheet geklickt wird. Da das ziemlich oft vorkommt, sollte der Code darin so minimalistisch wie möglich sein. Deswegen wird hier einfach blind der Status des Select-Knopfs auf dein UserForm übertragen, ohne if else irgendwas.

Das funktioniert in fast allen Fällen, außer der User klick zufällig auf die selbe Zelle die schon mariert ist, wenn es/sie Object Select mit einem Doppelklick verlässt.

Probiers einfach mal aus.

Ach ja, mit neuer Hilfsfunktion kannst du die folgenden Eventcodes anpassen.

Private Sub Worksheet_Activate()
    Notationselemente.Show
    If CommandBars("Ribbon").Controls(1).Height > 100 Then
        Application.CommandBars.ExecuteMso "MinimizeRibbon"
    End If
    Application.FormulaBarHeight = 1
 
    If ButtonIsOn Then Application.CommandBars.ExecuteMso ("ObjectsSelect")
End Sub

Hier kannst du zusätzlich noch ein if am Ende raushaun. Kostet alles nur Performance.

Private Sub Worksheet_Deactivate()
    Unload Userform1
    If Application.CommandBars("Ribbon").Controls(1).Height <= 100 Then
        Application.CommandBars.ExecuteMso "MinimizeRibbon"
    End If
 
    If ButtonIsOn Then Application.CommandBars.ExecuteMso ("ObjectsSelect")
   
    Application.DisplayFullScreen = False
End Sub

Edit:

Die Funktion hier kommt mir auch komisch vor. Fragst du da irgendwo den Status ab? Was ist, wenn die beide nicht mehr synchron sind?

Private Sub ToggleButton1_Click()
    Application.CommandBars.ExecuteMso ("ObjectsSelect")
End Sub

 

[Excelmania]

Hallo Janush,

super. Vielen Dank für die Unterstützung und die sehr verständlich Erläuterung.

Ja, beim letzten ToggleButton wurde nie der Status abgefragt. Hatte hier sehr umfangreiche Versuche, die jedoch alle nicht geklappt haben. z. B.

Private Sub ToggleButton1_Click()

    Dim c As CommandBarButton
    Set c = Application.CommandBars.FindControl(, 182)

    If c.State = 0 And Userform1.ToggleButton1.Value = True Then
    userform1.ToggleButton1.Value = False
    Application.CommandBars.ExecuteMso ("ObjectsSelect")
else if c.state = -1 and Userform1.ToggleButton1.Value = false then
    userform1.ToggleButton1.Value = true
    End If

End Sub

In einer Konstellation stimmt der Status nun nicht mehr. Wird ein Doppelklick gemacht, dann springt der Togglebutton zurück aber die Funktion "Objekte ziehen" ist noch aktiv.

Gibt es eine Möglichkeit den Doppelklick volständig zu deaktivieren im Modus "Objekte auswählen"?

Public Function ButtonIsOn() As Boolean

    If selectKnopf Is Nothing Then Set selectKnopf = Application.CommandBars.FindControl(, 182)

    ButtonIsOn = selectKnopf.State = -1

    Application.DoubleClick = False 'Funktioniert leider nicht.

End Function

Als Alternative habe ich überlegt, könnte der Blattschutz aktiviert werden und es ist nur noch zulässig, dass Objekte formatiert werden können.

Zu meinem Bedauern habe ich dann jedoch ein Problem mit dem einfügen von Shapes. Diese habe ich bisher auf Basis der aktivierten Zelle eingefügt. Auf Basis des sichtbaren Bereichs klappt es nicht. Hier orientiert sich Excel immer an 0/= unabhängig wo ich horizontal und vertiakl hingescrollt habe:

      l = ActiveWindow.VisibleRange.Width / 2
      t = ActiveWindow.VisibleRange.Height / 2
      w = ActiveWindow.VisibleRange.Width
      h = ActiveWindow.VisibleRange.Height

 
    'Abgerundes Rechteck
    'AddShape (Typ, Links, oben, Breite, Höhe)
    ActiveSheet.Shapes.AddShape(msoShapeFlowchartAlternateProcess, l, t, w, h).Select
    Selection.ShapeRange.Height = sh_höhe
    Selection.ShapeRange.Width = sh_Breite
    Selection.ShapeRange.ShapeStyle = msoShapeStylePreset24
    'Standardtext
    Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Start"
    With Selection.ShapeRange.TextFrame2
        .VerticalAnchor = msoAnchorMiddle
        .HorizontalAnchor = msoAnchorNone
    End With
 
    With Selection.ShapeRange.TextFrame2.TextRange.ParagraphFormat
        .Alignment = msoAlignCenter
    End With
 
    'Einstellung unabhängig von Zellenposition und -größe
    Selection.Placement = xlFreeFloating

Ergänzung (27. Mai 2021)

Den Doppelklick zu verhindern habe ich rausbekommen:

Psitionierung der Elemente erledigt.

      l = ActiveWindow.VisibleRange.Left + ActiveWindow.VisibleRange.Width / 2
      t = ActiveWindow.VisibleRange.Top + ActiveWindow.VisibleRange.Height / 2
      w = ActiveWindow.VisibleRange.Width
      h = ActiveWindow.VisibleRange.Height

Die Lösung ist unbefriedigend:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    If Notationselemente.ToggleButton1.Value = True Then
    Application.CommandBars.ExecuteMso ("ObjectsSelect")
    End If

End Sub

Ergänzung (27. Mai 2021)

Lässt sich irgendiwe im Modus "Objekte auswählen" der Doppelklick deaktivieren?

 

[Janush]

Nein sieht nicht so aus, als würde das funktionieren.

Was meinst du mit

In einer Konstellation stimmt der Status nun nicht mehr. Wird ein Doppelklick gemacht, dann springt der Togglebutton zurück aber die Funktion "Objekte ziehen" ist noch aktiv.

Welche Konstellation?

 

[Excelmania]

Folgendes Prozedere:

1. ToggleButton wird gedrückt: Modus "Objekte auswählen" ist aktiv.
2. 1. Doppelklick
ToggleButton springt zurück auf False aber Modus "Objekte auswählen" bleibt aktiv
3. 2. Doppelklick - unabhängig ob verschiedene oder gleiche Zelle
Modus "Objekte auswählen" wird beendet -> wie beschrieben.

 

[Janush]

Ähm, jetzt bin ich raus.

D.h. wenn du im Modus drin bist und irgendwo doppelt klickst, bleibt der Modus aktiv? Das ist doch das, was du am Anfang wolltest, aber wie funktioniert das? Hast du da irgendwo noch ne andere Rückkopplung drin?

Wenn du das ganze Zeug aus ToggleButton1_Click() rausnimmst, geht es dann wieder? Also wenn du es auf den Zustand von heute Morgen bringst?

 

[Excelmania]

Guten Abend.

Deinen Code habe ich 1zu1 umgesetzt.

Hier die Zeilen:

Tabellenblatt:

Option Explicit

Private Sub Worksheet_Activate()
    userform1.Show
    If CommandBars("Ribbon").Controls(1).Height > 100 Then
        Application.CommandBars.ExecuteMso "MinimizeRibbon"
    End If
    Application.FormulaBarHeight = 1
 
    If ButtonIsOn Then Application.CommandBars.ExecuteMso ("ObjectsSelect")
   
End Sub

Private Sub Worksheet_Deactivate()
    Unload userform1
    If Application.CommandBars("Ribbon").Controls(1).Height <= 100 Then
        Application.CommandBars.ExecuteMso "MinimizeRibbon"
    End If
 
    If ButtonIsOn Then Application.CommandBars.ExecuteMso ("ObjectsSelect")
 
    Application.DisplayFullScreen = False
   
End Sub

Private Sub PM_CB_Menü_Click()
    userform1.Show
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    'If Notationselemente.ToggleButton1.Value = True Then
    'Application.CommandBars.ExecuteMso ("ObjectsSelect")
    'End If

    userform1.ToggleButton1.Value = ButtonIsOn

End Sub

Modul:

Option Private Module

Public selectKnopf As CommandBarButton

Public Function ButtonIsOn() As Boolean

    If selectKnopf Is Nothing Then Set selectKnopf = Application.CommandBars.FindControl(, 182)

    ButtonIsOn = selectKnopf.State = -1

End Function

userform1

Private Sub ToggleButton1_Click()

    Application.CommandBars.ExecuteMso ("ObjectsSelect")
   
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

   If ButtonIsOn Then Application.CommandBars.ExecuteMso ("ObjectsSelect")

End Sub

Sonst nimmt kein Code bezug auf den Befehl "Objekte markieren"

Bei folgenden Szenario springt der Befehl raus:
Modus wird über Userform aktiviert
1. Doppelklick auf eine freie Fläche setzt den ToggleButton zurück
2. Doppelklick deaktiviert erst den Modus "Objekte markieren"

Wird vor dem zweiten Doppelklick wieder Objekte markieren in der Userform gedrückt sind Stellung des ToggleButton und des Befehls "objekte markieren" asynchron.

Die schönste Lösung wäre, wenn im Modus "Objekte markieren" der Doppelklick deaktiviert werden könnte. Hättest Du eine Idee?

könnte das doppelklick-Ereignis bei togglebutton = True das Problem lösen?

alternativ eine schleife Objekte markieren bis togglebutton = False?

 

[Janush]

Naaaa da ist eine Rückkopplung drinne. Ich musste auch erst ewig probieren, aber offensichtlich triggert UserForm1.ToggleButton1.Value = False/True das ToggleButton click event, welches dann den Modus wieder aktiviert.

Wenn du ein ToggleButton Event nimmst, welches nicht vom VBA code ausgelöst wird, geht es.

Private Sub ToggleButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
    Application.CommandBars.ExecuteMso ("ObjectsSelect")
End Sub

Das hat allerdings den Nachteil, dass es sich "verschluckt", wenn es zu schnell hintereinander gedrückt wird.


Edit:

Hier ist deine Lösung

Private Sub ToggleButton1_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

    Call CheckSync
    
End Sub

Private Sub CheckSync()

If (Me.ToggleButton1.Value And ButtonIsOn) Or _
   (Not Me.ToggleButton1.Value And Not ButtonIsOn) Then
    selectKnopf.Execute
End If

End Sub

Und Togglebutton click raus

 

[Excelmania]

Hallo Janush,

Du bist der Beste. Perfekt. Vielen Dank für die super Lösung.

 

[Excelmania]

Guten Morgen.

Ich habe noch einmal mit dem Blattschutz experimentiert. Schöne wäre es, wenn das Tabellenblatt im Standard geschützt ist und die Nutzer nur berechtigt sind die Userform und Formen zu nutzen.

Durch eine Doppelklickabfrage wollte ich daher realisieren, wenn der Modus "Objekte auswählen" beendet wird, starte ihn wieder. Leider klappt es nicht.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

If Not ButtonIsOn Then Application.CommandBars.ExecuteMso ("ObjectsSelect")

'Blattschutzhinweis abfangen
If Me.ProtectContents = True And Target.Locked = True Then
        Cancel = True
End If
     
End Sub

In Ergänzung mit mit Worksheet_Change fumktioniert es leider auch nicht:

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    
    If Not ButtonIsOn Then Application.CommandBars.ExecuteMso ("ObjectsSelect")
    
End Sub

Jemand eine Idee, woran das liegen und ich das Problem umgehen kann?

 

[Excelmania]

Guten Morgen. Habe es lösen können. Der Thread kann geschlossen werden.