Worksheet Olayları(Eventleri)

Bir workbook'un sayfalarındaki çeşitli olaylara tepki vermek adına devreye giren olaylar Worksheet olayları olarak adlandırılır. Bunları da yine Workbook olaylarını seçer gibi seçip içlerini doldurmaya başlayabilirsiniz. İlgili combobox seçildiğinde aşağıdaki gibi eventlerin bir kısmı görünür.

Bunlardan en sık kullanacaklarımız:

Change
SheetChange
BeforeDoubleClick
Activate/Deactivate
Calculate

Pivot tablolarla ilgili olanlar da önemli olup bunlara Pivot İşlemleri konusunda değineceğiz. Şimdi sırayla önemli eventlere bakalım.

Worksheet_Change Event

Kuşkusuz en önemli sayfa olayı sayfada bir hücrenin değişimiyle meydana gelen Change olayıdır. (Bu eventin adını AfterChange gibi düşünmeniz yerinde olur. Zira olay, hücre içi değiştikten sonra meydana gelir. Microsoft geliştiricileri olayın adını keşke böyle yapsalarmış. Ne de olsa After ve Before ile başlayan bir sürü event var.) Syntax'ı aşağıdaki gibidir.

                    
                        Private Sub Worksheet_Change(ByVal Target As Range)

                        End Sub

Küçük Bir Örnek

Bu örnekte, her değişim oldukça sayfanın rengi değişsin. Bu örneği alıp istediğiniz bir dosyanın Sheet1 modülüne yapıştırın ve sonra gidip sayfada rasgele hücrelere bir şey girin. Her Enter'a basışınızda sayfa rengi değişecektir.

                    
                                Private Sub Worksheet_Change(ByVal Target As Range)
                                  x = WorksheetFunction.RandBetween(1, 1000000)
                                  ActiveSheet.Cells.Interior.Color = x
                                End Sub

Tetikleyiciler ve Özel Hususlar

Change olayı kullanıcının manuel bir işlemi sonucunda tetiklenebileceği gibi bir makro kodu sonucunda da tetiklenebilir.

Bazı özel durumlar da vardır:

Manuel hesaplama durumundan otomatik hesaplama durumuna geçildiğinde de hücrelerin içi değişir ama bu durum Change olayını tetiklemez. Yine de yeni duruma göre içerik kontrolü yapacaksanız bu sefer Calculate olayını kullanmanız gerekir.
Bir hücrenin içini silmek de değişiklik olduğu için Change olayı tetiklenir.
Merge butonu ile hücre birleştirmek tetiklemez.
Bir alanı sıralamak tetiklemez.
Goal Seek kullanarak bir hücrenin değişimi tetiklemez.

Target Parametresi

Target parametresi, belli bir hücrenin içeriğini değişip değişmediği öğrenmek amacıyla kullanılabileceği gibi ilgili hedefin tek bir hücre mi yoksa bir range mi olduğunu belirlemek için de kullanılabilir. Aslında Range nesnesinin tüm özelliklerini kontrol etmek için kullanılabilir.

                    
                                    If Target.Address="$A$1" Then 'bu bir adres kontrolüdür
                                    If Target.Cells.Count=1 Then 'bu da tek bir hücre mi yoksa bir range mi kontrolüdür
                                    Target'ın belirli bir aralıkta olup olmadığını öğrenmek için özel bir kullanım şekli vardır: If Not Intersect(Target, Range("..")) Is Nothing Then

Aşağıdaki örnekte değişen hücrenin C3 veya C4'te olması beklenmektedir. Bununla ilgili daha detaylı örnek Çeşitli Örnekler bölümünde yapılacaktır.

                    
                    Private Sub Worksheet_Change(ByVal Target As Range)
                        If Not Intersect(Target, Range("C3:C4")) Is Nothing Then
                            'ana kod bloğu
                        Else
                            MsgBox "Yanlış yerden seçim yapıyorsunuz, sadece C3 ve C4 hücrelerini kullanınız"
                        End If
                    End Sub

Aynı Hücredeki Değişimlerde Bir Önceki Değeri Elde Etme

Değişen hücrenin bir önceki değerini elde etmek istiyorsak Statik değişken kullanırız.

                    
                        Private Sub Worksheet_Change(ByVal Target As Range)

                        Static öncekiDeğer As String
                        Static öncekiAdres As String

                        If öncekiDeğer <> "" And öncekiAdres = Target.Address Then
                           MsgBox "Önceki:" & öncekiDeğer
                        End If

                        öncekiDeğer = Target.Value
                        öncekiAdres = Target.Address

                        MsgBox "yenisi:" & Target.Value

                        End Sub

Bu örnekte statik değişkenlerimiz ilk başta boş olacaktır, zira henüz "öncesi" yoktur. İlk işlemden sonra önceki statik değişkenler dolmaya başlayacaktır. Akabinde, yeni hücre ile öncekinin aynı olup olmadığı kontrol edilir.

Worksheet_SelectionChange

Seçili hücre her değiştiğinde bu event oluşur.

                    
                        Private Sub Worksheet_SelectionChange(ByVal Target As Range)

                        End Sub

Target, seçilen hücreyi gösterir.

Aşağıdaki örnekte, seçilen hücre pencerenin sol üst köşesindeki ilk hücre olacak şekilde ayarlanır.

                    
                        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
                            With ActiveWindow
                                .ScrollRow = Target.Row
                                .ScrollColumn = Target.Column
                            End With 
                        End Sub

Önceki Seçimi Elde Etme

Seçimden bir önceki hücreye de ihtiyacımız olacaksa Statik bir değişken kullanırız. İlk seçimde çalışmaz, sonrakilerde çalışır, çünkü ilk seçimde henüz "öncesi" yoktur.

                    
                        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
                        Static öncekiRange As String

                        If öncekiRange <> "" Then
                          MsgBox "önceki:" & Range(öncekiRange).Address
                        End If

                        öncekiRange = Target.Address

                        MsgBox "yenisi:" & Target.Address
                        End Sub

Daha farklı bir örnek ise, önceki hücre ile yeni hücre arasındaki alanı kırmızıya boyamak olabilir. "Ne işimize yarayacak" diye sormayın, bu haliyle bir işinize yaramaz, ama farklı bir konuda size fikir verebilir.

                    
                                    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
                                    Static öncekiRange As Range

                                    If Not öncekiRange Is Nothing Then
                                        Range(öncekiRange, Target).Interior.Color = vbRed
                                    End If

                                    Set öncekiRange = Target

                                    End Sub

Worksheet_BeforeDoubleClick

Bir hücreye çift tıklandığında bu olay olur ve Excel'in o anda nasıl davranmasını istiyorsak bu prosedüre bunları yazarız. Syntax'ı aşağıdaki gibidir.

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

                    End Sub

Target'ı şimdiye kadar öğrenmiş olmalısınız; kullanım mantığı yine yukardakilerle aynı. Cancel parametresine ise True değerini atayarak eylemi iptal edebiliriz, yani Excel'e çift tıklama olmamış gibi davrandırtabiliriz.

En sık kullandığım caselerden birisi, toplanmış verileri tutan bir listede ilgili hücreye çift tıklama sonucunda o grubun alt detayını gösteren verilerin uygun miktarda satır açılarak araya eklenmesi; aynı hücreye tekrar çift tıklanması durumunda ise bu kayıtların animasyonlu bir şekilde silinip (sanki bu sitede bordo arkaplanlı başlıklara tıklandığında yavaşça katlanmasını sağlayan Jquery kodlarına benzer) listenin ilk hale gelmesidir. Böyle bir örnek kullanımı ADO içermesi sebebiyle bu sayfada vermeyip bunları veritabanı uygulamaları bölümünde ele alıyor olacağım. İlgili örneğe buradan ulaşabilirsiniz. Aynı örneği ilgili veriyi aynı sayfada gizlenmiş bir şekilde dururken unhide ederek de yapabilirsiniz. Ancak az önceki linkteki örnekteki liste dinamik bir yapıya sahip olduğu için hide etmek bir uygun bir çözüm olmamaktadır.

Başka bir örneği ise burada ele alabiliriz. Bunda da yine gruplu bir liste var. Bu listede bir hücreye çift tıklayınca bu hücreye ait alt veriler ayrı bir dosya olarak açılıyor olsun. Ör: En çok kredi düşüşü yaşayan şube listesinde şube koduna çift tıklayınca bize en çok düşüş yaşayan müşteriler dosyasını açıp bu şubeyi filtrelesin.

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

                            adres = "C:\...\"

                            If Not Intersect(Target, Range("B2:B20")) Is Nothing Then
                            sb = Target.Value
                            Workbooks.Open adres + "Kredisi en çok düşen müşteriler - " & Date - 1 & " Sonuçları.xlsm", ReadOnly:=True
                            ActiveSheet.ListObjects("Query_from_DWH").Range.AutoFilter Field:=2, Criteria:=sb
                            End If

                            End Sub

Şahsen ben bu eylemi çok önemsiyorum. Bununla ADO'yu birleştirerek yüksek ücretli programlara alternatif programlar yazabilirsiniz. ADO kısmında diğer detayları bulabilirsiniz.

Worksheet_Activate/Deactivate

Belli bir sayfa (yeniden) aktif (veya inaktif) olduğunda çalışmasını istediğiniz kodları bu olayla tetiklenen olay prosedürleri içine yazabilirsiniz.

                    
                            Private Sub Worksheet_Activate()

                            End Sub

Örneğin, ana menü sayfası gibi bir sayfanız var ve buna sadece diğer sayfalardaki Anamenü linki aracılığı ile ulaşmak istiyorsunuz, ve bu sayfalar açıken bu menü sayfası görünmesin istiyorsanız, işte bu menü sayfasından ayrılırken sayfanın gizlenmesini sağlayacak bir kod yazabilirsiniz.

                    
                                Private Sub Worksheet_Deactivate()
                                   Me.Visible = xlSheetHidden 
                                End Sub

                                'aşağıdaki kodu da diğer sayfalardaki Selection_Change eventine yazarsınız
                                If Target.Value = "Anamenü" Then
                                   Sheets("Anamenü").Visible = xlSheetVisible
                                   Sheets("Anamenü").Select
                                End If

Worksheet_Calculate

Bu event, sayfadaki formüller yeniden hesaplandığında tetiklenir. Özetle o formülü etkileyen hücrelerden birinde değişiklik olursa tetiklenir. Mesela bir hücre grubunun altında SUBTOTAL formülü ile toplam/ortalama vs. alınmışsa ve hücre grubundaki filtrede bir değişiklik yapılırsa formülün içeriği de değişeceği için bu event tetiklenir.

Bu eventte hedef bir hücre (Target) bulunmaz, zira tüm hücreler yeniden hesaplanmıştır.

NOT: Sayfa için aynı zamanda Change eventi de varsa kod bloğu içine eventleri geçici olarak bastıran kodları eklemeyi unutmayın. (Bu konuyu hemen aşağıda inceleyeceğiz)

                    
                            Private Sub Worksheet_Calculate()

                            'çeşitli işlemler

                            End Sub

Bu konuya ait güzel bir örneği şurada bulabilirsiniz.

Event Tetiklenmesini Bastırmak (Geçici Olarak Durdurmak)

Makronuzda, bir yerlerde ilgili eventi tekrar tetikleyecek bir kod varsa bu kod sonsuz döngüye girer ve Excel çökebilir (veya ayarlarınıza göre 100 civarı iterasyon sonucunda durabilir, bende 78. iterasyonda duruyor). Change eventi içinde bir hücrenin içeriği değiştirilmesi veya SelectionChange eventi içinde başka bir hücre seçilmesi gibi.

Mesela aşağıdaki örneği F8 ile deneyip görün, her F8 yapışınızda kod hiç durmadan bir aşağı inecektir.

                    
                        Private Sub Worksheet_SelectionChange(ByVal Target As Range)
                           Target.Offset(1, 0).Select
                        End Sub

Aşağıdaki kodda ise sürekli olarak Change olayı kendisini tetikliyor.

                    
                                Private Sub Worksheet_Change(ByVal Target As Range)
                                   Target.Offset(1, 0).Value = Target.Row
                                End Sub

İşte bu tür durumları önlemek için eventin başında Application.EnableEvents = False diyerek eventleri geçici olarak askıya alırız, sonra işlemleri yaptırır, sonra da Application.EnableEvents = True diyerek eventleri tekrar devreye sokarız. Tabi olur da kodumuzda bir hata oluşur da sona gelmeden durursa Eventler askıda kalabilir, bu yüzden bir hata yönetimi bloğu yazıp eventleri burada da tekrar aktive etmeliyiz.

                    
                                Private Sub Worksheet_Change(ByVal Target As Range)
                                On Error GoTo hata
                                    Application.EnableEvents = False
                                    'tetiklemeye neden olabilecek işlemler
                                    Application.EnableEvents = True
                                    Exit Sub

                                hata:
                                    Application.EnableEvents = True
                                End Sub

Workbook'un Sheet Eventleri

Workbook eventleri workbookla ilgili bir eylem gerçekleşince devreye giriyordu, Worksheet eventleri de sayfayla ilgili bir eylem gerçekleşince. Bir de ikisinin karışımı gibi olan ama aslında bir Workbook eventi olan event grubu var.

Bunların bir listesi aşağıdaki gibi olup, belli bir sayfada değil de herhangi bir sayfada bir eylem gerçekleştiğinde tetiklenirler.

Mesela aşağıdaki kod ile hangi sayfa seçilirse onun adı bize MsgBox ile gösterilir.

                    
                        Private Sub Workbook_SheetActivate(ByVal Sh As Object)
                           MsgBox Sh.Name
                        End Sub

Farklı Kullanıcılarda Eventlerin Tetiklendiğinden Emin Olmak

Giriş bölümündeki Güvenlik ayarları bölümünü okumadıysanız öncelikle orayı okumanızı öneririm. Orada belirtildiği gibi makro ayarları Disable All şeklindeyse sonuçta bir makro olan Event Prosedürleriniz de devreye girmez.

Örnek Senaryo

Hazırladığınız bir dosyanın anlamlı olabilmesi için eventlerin çalışması gerekmekte olsun. Ancak kullanıcının makro ayarları Disabled ise kullanıcı dosyadan istenen verimi alamayacaktır, üstelik sizin istemediğiniz şekilde yetkisi olmayan görüntülemeler bile yapabilecektir (Farklı şubenin rakamlarını görmek gibi).

Bunu engellemek için benim geliştirdiğim yöntem aşağıdaki gibidir (Daha iyi veya daha kötü yöntemler var olabilir, ben araştırdığımda hiçbir şeyle karşılaşmadığım için kendi çözümümü böyle geliştirmiştim).

Çalışmanın tam üstüne denk gelecek şekilde bir düğme koyarım ve bu düğme için bir kod yazarım. Eğer makrolar enable ise düğme kaybolur, makrolar disabled ise aşağıdaki gibi bir hata alır.

Düğmeyi Silme veya Taşıma

Ayrıca düğmeyi silmesin veya başka bir yere taşımasın diye sayfaya protection da koymamız gerekiyor. Makro sırasında dosyayı gizlerken geçici olarak kaldırıyor, gizledikten sonra tekrar koyuyoruz, ki protection'ı başka amaçlar için de kullanabilelim. Buna ait bir örneği Çeşitli Örnekler bölümünde 2. örnekte bulabilirsiniz.

Düğmenin Click Eventi

Düğmenin Click eventi ise şöyledir:

                    
                        Sub Button1_Click()
                           Sheets(1).Unprotect Password:="1234"
                           ActiveSheet.Shapes("Button 1").Visible = msoFalse 'düğmeyi gizler
                           Sheets(1).Protect Password:="1234"
                        End Sub

Kısıtlar Uygulamak

Sayfanın Yazdırılmasını Engellemek

Diyelim ki kullanıcıların belli sayfaları basmasını istemiyorsunuz. Aşağıdaki kodu ilgili dosyanın Workbook_BeforePrint eventine yazmanız gerekir.

                    
                            Private Sub Workbook_BeforePrint(Cancel As Boolean) 
                                For Each s In ActiveWorkbook.SelectedSheets
                                    If s.Name = "Ham Data" Then
                                        MsgBox ("Bu sayfayı basamazsınız!!!")
                                        Cancel = True 
                                    End If
                                Next
                            End Sub

Workbook içinde hiçbir sayfanın bastırılmasını istemiyorsanız bu sefer hiç sayfa kontrolü yapmadan doğrudan MsgBox ve Cancel=True satırları yeterli olacaktır.

Gördüğünüz gibi bu işlemi bir worksheet eventi ile değil workbook eventi ile yapıyoruz.

Sayfada Cut/Copy Engellemek

Bu işlemin tüm dosya bazında yapılmasıyla ilgili örnek şurada olup, sayfa bazında yapmak için Worksheet_Activate ve Worksheet_Deactivate olaylarında kullanılması yeterlidir.

Mevduat Fiyatlama Hesap Makinası (Animasyonlu)

Bu örnekte, 4 parametreden oluşan bir denklemin herhangi 3'ü bilinirken diğer 4.sünün tespit edilmesine yönelik bir kod yazacağız. Klasik Excel yöntemiyle yapmak istediğinizde 4 ayrı çalışma yapmanız gerekirken VBA ile tek bir format ile tüm senaryoları ele alabileceğiz.

Bunun için aşağıdaki gibi bir form hazırladım. Dosyanın kendisine buradan ulaşabilirsiniz.

Çalışmaya Ait Kodlar

Sheet1 Modülü:

                    
                                Private Sub Worksheet_Change(ByVal Target As Range)
                                On Error GoTo çıkış
                                    Application.EnableEvents = False

                                    If Target = [stopaj] And IsEmpty(Target) Then
                                        Target.Value = "0,15 (TL, 6 aya kadar)"
                                    End If

                                    If Not Intersect(Target, [alan]) Is Nothing Then
                                        ActiveSheet.Unprotect 1234
                                        Call temizlik([alan])
                                        If [alan].Cells.SpecialCells(xlCellTypeBlanks).Count = 1 Then
                                            [alan].SpecialCells(xlCellTypeBlanks).Select
                                            Select Case ActiveCell
                                                Case [anapara]
                                                    ActiveCell.Formula = "=365*NetGetiri/(Vade*Faiz*(1-value(left(stopaj,4))))"
                                                Case [Faiz]
                                                    ActiveCell.Formula = "=365*NetGetiri/(Vade*Anapara*(1-value(left(stopaj,4))))"
                                                Case [Vade]
                                                    ActiveCell.Formula = "=365*NetGetiri/(Anapara*Faiz*(1-value(left(stopaj,4))))"
                                                Case [NetGetiri]
                                                    ActiveCell.Formula = "=Anapara*Faiz*Vade*(1-value(left(stopaj,4)))/365"
                                                Case Else
                                                    MsgBox "Böyle bir seçenek bulunmamaktadır"
                                            End Select
                                            ActiveCell.Font.Color = vbRed
                                            [uyarı].Value = ""
                                            Call Fontsizedeğiş(24, 20)
                                            Call alancopypaste
                                        End If
                                    End If
                                    Application.EnableEvents = True
                                    ActiveSheet.Protect 1234

                                    Exit Sub

                                çıkış:
                                If Err.Description = "No cells were found." Then
                                    [uyarı].Select
                                    ActiveCell.Value = "Lütfen hangi alanın yeniden hesaplanmasını istiyorsanız onu silin."
                                    Call Fontsizedeğiş(14, 10)
                                End If
                                Application.EnableEvents = True
                                ActiveSheet.Protect 1234
                                End Sub

Standart Modül İçeriği

Bunda sleep metodu kullanıldığı için aşağıdaki özel kod en başa eklenmiştir.

                    
                        #If VBA7 Then
                        Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr) '64 Bit Sistemler için
                        #Else
                        Public Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long) '32 Bit Sistemler için
                        #End If

                        Sub beklet(sure As Integer)
                            Sleep sure
                        End Sub

Temizlik Butonunun Kodu

                    
                        Sub Button1_Click()
                          Range("alan").ClearContents
                          ActiveSheet.Unprotect 1234
                          [uyarı].Value = ""
                          ActiveSheet.Protect 1234
                        End Sub

Bu hesap makinesinin kullanımı şöyledir:

Kullanıcı diyelim ki, bilinen olarak anapara, faiz ve vadeyi girip müşterinin net kazancını hesaplamak istiyor olsun. Bu üçünü yazınca net kazanç bilgisi otomatik hesaplanır. Bu hesaplamanın sonucu da bir döngü ile font hacminin önce büyüyüp sonra da küçülmesiyle animasyonlu bir şekilde gösterilir. Kullanıcı diyelim ki sonradan kazanç bilgisini de manuel değiştirdi, o zaman tüm alanlar dolu olacağı için kodumuz neye göre hesaplama yapacağını bilmez ve kullancıya "Lütfen hangi alanın yeniden hesaplanmasını istiyorsanız onu silin" mesajını yine animasyonlu bi şekilde gösterir. Çalışma mantığı ise şöyledir: Sayfada belli name'ler tanımlanmış durumda. Makronun tetiklenmesi için "alan" isimli namede bir hücrenin değişmesi beklenmekte. Tabi değişklikler sonucunda başka tetkilenme olmasın diye eventler geçici olarak baskılanmakta. Değişlik sonucunda alan isimli name'de boş hücre sayısının 1 olup olmadığına bakılmaktadır([alan].Cells.SpecialCells(xlCellTypeBlanks).Count = 1 kodu ile). Böylece bu boş olana uygun formül yazılmakta ve sonuç copy-paste yapılmaktadır. Alan isimli namede 2 hücre doluyken 3.sünün doldurulması durumunda da, 4 hücre doluyken birinin silinmesi durumunda kontrol sonucu 1 dönecek ve esas işi yapan kod bloğu çalışmış olacaktır.