Przeliczenie arkusza po zmianie formatowania komórki   strona główna:
A po co ten Excel ;-)
 
Dziś nadrabiał zaległości :-) Już od jakiegoś czasu chcę napisać parę słów o tym temacie ale jakoś czasu nie starcza.  
 
   >> Wormsek Napisałem prostą funkcyjkę, którą zliczam sumę komórek pogrubionych: Wszystko fajnie, ale pogrubienie   
                      komórki nie włącza przecież przeliczenia arkusza. Znacie jakiś sposób, aby pogrubienie komórki to zrobiło.  
                     Czy tylko SelectionChange zostaje? <<  
 
Temat z rodzaju "niewykonalne" :-| Standardowo godzimy się na to że funkcja nie reaguje na zmianę formatowania. Jednak liczy  
zgodnie z założeniami a to najważniejsze. Zawsze jest jeszcze F9 (w funkcji koniecznie Application.Volatile = True) i na ogół to   
wystarcza. Ale czy na pewno nić się nie da zrobić?  
 
Metoda.1  
    Jedna z opcji to wspomniane przez Wormska wykorzystanie zdarzenia Worksheet_SelectionChange. A więc wywołanie   
metody Calculate Arkusza w którym zdefiniowano zdarzenie np.: przez Me.Calculate Metoda ma jednak słabą stronę:  
przeliczenie formuł następuje dopiero po zaznaczeniu innej komórki niż formatowana. A więc: nadajemy formatowanie - formuły się  
nie przeliczają, zaznaczamy inną komórkę - następuje przeliczenie formuł, również naszej, która uwzględni zmianę formatowania.  
No cóż... denerwujące, ale za zwyczaj wystarcza :-)  
 
Metoda.2  
    Inną opcją jest podpięcie pod przycisk, w tym przypadku [B] własnej procedury :-)   (wersja testowana pod E2003)
Pierwszą rzeczą jaką należy ustalić to ID danego przycisku. Napiszmy do tego celu prostą procedurę wypisującą ważne dla nas  
informacje do okna Immediate Edytora VBA (VBE/Ctrl+G)  
 
Sub TestIDnr()  
    Dim xlCmdBar As Office.CommandBar  
    Dim objCtrl As Office.CommandBarControl  
      
    Set xlCmdBar = Application.CommandBars("Formatting")  
    For Each objCtrl In xlCmdBar.Controls  
        With objCtrl  
            Debug.Print .ID, .Caption, .Type  
        End With  
    Next  
    Set xlCmdBar = Nothing  
End Sub  
 
Przy moich ustawieniach paska Formatowanie (ustawienia standardowe) zostają zwrócone następujące wyniki:  
 
 1728         &Czcionka:                   4   
 1731         &Rozmiar czcionki:           4   
 113          &Pogrubienie                 1   
 114          &Kursywa                     1   
 115          &Podkreślenie                1   
 120          Wyrównaj do l&ewej           1   
 122          Wyśro&dkuj                   1   
 121          Wyrównaj do pr&awej          1   
 402          &Scal i wyśrodkuj            1   
 1643         &Waluta                      1   
 396          &Zapis procentowy            1   
 397          &Zapis dziesiętny            1   
 398          &Zwiększ dziesiętne          1   
 399          Z&mniejsz dziesiętne         1   
 3162         &Zmniejsz wcięcie            1   
 3161         &Zwiększ wcięcie             1   
 203          &Obramowanie                 13   
 1691         Kolor &wypełnienia           13   
 401          Kolo&r czcionki              13   
 
Interesujący nas fragment wytłuściłem. A więc: ID przycisku [B] to 113.  
    Teraz.. Musimy wziąć pod uwagę że każdy użytkownik może wstawić przycisk "pogrubienia" na który pasku mu pasuje ;-)   
Więc podpięcie naszej procedury tylko pod jeden przycisk może być nieskuteczne. Procedura ustawiająca nasze działanie przycisku  
i przywracająca standardowe jego działanie powinna wyglądać tak:  
 
Sub UstawAkcje(Wlacz As Boolean)  
    Dim myControls As CommandBarControls  
    Dim myControl As CommandBarControl  
    Dim strMakro As String  
 
    Set myControls = CommandBars.FindControls(Type:=msoControlButton, ID:=113)  
 
    If Not myControls Is Nothing Then  
        If Wlacz Then  
            strMakro = "Przelicz"  
        Else  
            strMakro = ""  
        End If  
 
        For Each myControl In myControls  
            myControl.OnAction = strMakro  
        Next myControl  
    End If  
 
    Set myControls = Nothing  
End Sub  
 
Sub Przelicz()  
    Dim kom As Excel.Range  
    If TypeName(Selection) = "Range" Then  
        With Selection  
            .Font.Bold = Not ActiveCell.Font.Bold  
            .Parent.Calculate  
        End With  
    End If  
End Sub  
 
OK. .Parent.Calculate - wiadomo dlaczego ale po co to .Font.Bold = Not ActiveCell.Font.Bold ??  
    Chodzi o fakt że podpinając coś pod OnAction przycisku pozbawiamy się możliwości korzystanie z jego oryginalnego działania.  
Trzeba więc go symulować :-) Linia oznacza ustawienie odwrotnego pogrubienia zakresu Selection niż pogrubienie aktywnej komórki  
 
Metoda.3   (wersja testowana pod E2003)
Ostatnią znaną mi techniką jest wykorzystanie zdarzenia kliknięcia przycisku na pasku.  
    Do projektu VBA dodajemy moduł Class. Nadajemy mu nazwę clsCBEvents  i w kodzie wpisujemy:  
 
Option Explicit  
 
Public WithEvents cmdBold As Office.CommandBarButton  
 
Private Sub cmdBold_Click(ByVal Ctrl As Office.CommandBarButton, _  
                          CancelDefault As Boolean)  
    If TypeName(Selection) = "Range" Then  
        CancelDefault = True  
        With Selection  
            If Not IsNull(ActiveCell.Font.Bold) Then  
                .Font.Bold = Not ActiveCell.Font.Bold  
                .Parent.Calculate  
            Else  
                .Font.Bold = False  
            End If  
        End With  
    End If  
End Sub  
 
Żeby taka procedura działała należy określić przyciski których zdarzenia będziemy przechwytywać. Najlepiej wg mnie określić to w   
zdarzeniu Workbook_Open. Zatem w Module ThisWorkbook wpiszemy.  
 
Option Explicit  
Dim clsCBClass As New clsCBEvents  
 
Private Sub Workbook_Open()  
    Dim myControls As Office.CommandBarControls  
    Dim myControl As Office.CommandBarControl  
      
    Set myControls = Excel.CommandBars.FindControls(Type:=msoControlButton, ID:=113)  
    For Each myControl In myControls  
        Set clsCBClass.cmdBold = myControl  
    Next  
    Set myControls = Nothing  
End Sub  
 
Private Sub Workbook_BeforeClose(Cancel As Boolean)  
    Set clsCBClass = Nothing  
End Sub  
 
Nie wykorzystujemy tu właściwości OnAction więc nie ma czego cofać. Jednak i tak jesteśmy zmuszeni do symulowania działania  
przycisku. Chodzi o kolejność wykonywania zdarzeń.  
 - Oryginalnie jest tak:   
Kliknięcie na przycisk -> Nadanie formatowania  
 - Jak wykorzystamy zdarzenie kliknięcia na przycisk:   
Kliknięcie.. -> Zdefiniowane działanie, a więc Calculate -> Zmiana formatowania (więc lipa :-|)  
 - Jeżeli będziemy symulować działanie przycisku to:   
Kliknięcie.. -> Zdefiniowane działanie: zmiana formatowania i Calculate -> Koniec!! (CancelDefault = True)  
 
    Jednak istnieje spore ograniczenie dotyczące przechwytywania zdarzeń przycisków na paskach narzedzi.  
Nie każdą zmianę formatowania możemy tak przechwytywać.  
Manewry takie mogą dotyczyć jedynie kontrolek typów msoControlButton (1) i msoControlComboBox (4) ponieważ w module   MsoControlType Enumeration
Class WitEvents można otrzymać dostęp do zdarzeń tylko:  
 
    Public WithEvents cmdButton As Office.CommandBarButton  
    Public WithEvents cmdCombo As Office.CommandBarComboBox  
 
Zobaczmy jeszcze raz na dane zwracane przez w.w. Sub TestIDnr() Zauważmy że trzecia kolumna: .Type zwraca właśnie tą  
właściwość kontrolki. Jednak dla kontrolek np.: Kolor &wypełnienia  zwracany jest typ 13 tj msoControlSplitButtonPopup a do  
tego typu kontrolek nie ma zdarzeń do przechwycenia, czort wie czemu :-| Nazrazie żadnego sposobu na to nie znam.  
Pozostaje nam więc obsługa tych kontrolek których .Type zwraca 1 lub 4.  
 
  (link do przykładów)