Workbook Olayları(Eventleri)

Söz konusu Workbook nesnesi olduğunda, en temel eventler "açılma, kapanma, kaydolma" üçlüsüdür. Bunun dışında pek tabii ki başka olaylar da bulunmaktadır. Ben burada temelini vermeye çalışacağım. Gerisi kurcalama iştahınıza kalmış.

Dosya Açılması

Bir dosya açıldığında Workbook_Open olayı devreye girer. Bu event ile kullanıcıya çeşitli mesajlar verebileceğiniz gibi, log kaydı oluşturma, veritabanı işlemleri gibi başka birçok işlem yapabilirsiniz.

                    
                        Private Sub Workbook_Open()
                          MsgBox "x dosyasına hoşgeldiniz. Dosyayı kullanırken şunlara dikkat edin" & vbCrLf & "falan filan" & vbCrLf & "falan filan"
                          Logger Me.Name, Environ("username"), Date 'log kaydı tutuyoruz, kim ne zaman girmiş diye
                        End Sub

Olay Sırası

Bazı olaylar meydana geldiğinde birden çok event tetiklenebilir. Mesela bir dosya açıldığında sadece Open eventi devreye girmez, aynı zamanda Activate eventi de devreye girer. Bunlardaki sıra önce Workbook_Open sonra Workbook_Activate olacak şekildedir.

Auto_open

Workbook_Open makrosuna benzer bir de Auto_open makrosu vardır. Onla ilgili detaya buradan ulaşabilirsiniz.

Kapanma ve Kaydetme

Bir dosya kapanırken de önce Workbook_BeforeClose, sonra Workbook_Deactivate eventi devreye girer. Kapatma sırasında kaydetme de olacaksa süreç şöyle olur:

                    
                            Workbook_BeforeClose
                            Workbook_BeforeSave
                            Workbook_AfterSave
                            Workbook_Deactivate

                    
                        Private Sub Workbook_BeforeClose(Cancel As Boolean)
                          MsgBox "Dosyadan ayrılıyorsunuz. Falan filan yapmayı unutmayın."
                        End Sub

İptal Parametresi

Kapanma ve Kaydetme olayları öncesinde bazen bu olayın gerçekleşip gerçekleşmeyeceğini de kontrol altına almak isteyebiliriz. Bunu da varsayılan değeri False olan Cancel parametresine kod içinde True değeri atayarak yaparız.

Mesela kaydetme işlemini, sadece belli bir şifreyi bilen kişilere yaptırmak isteyebilirsiniz. Bunun için aşağıdaki gibi bir kod yazılabilir.

                    
                                Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
                                Dim şifre As String
                                şifre = InputBox("Kaydetmek için yetkili şifresini girin")
                                If şifre <> "1234" Then
                                    MsgBox "Dosyayı kaydetmeye yetkili değilsiniz."
                                    Cancel = True
                                Else
                                    MsgBox "dosya başarılı bir şekilde kaydedildi" 'belki bir de hata kontrolü konulabilir buraya
                                End If
                                End Sub

Kapanma/Deactive Olma Durumuna Göre Farklılaşan Mesaj Gösterme Örneği

Aşağıdaki örnekte ise dosya kapanırken farklı, deaktive olurken farklı bir mesaj verme örneği var.

                    
                            Private Sub Workbook_BeforeClose(Cancel As Boolean)
                                Range("Z1000").Value = 1 'hangi hücre müsaitse
                                Me.Saved = True
                            End Sub

                            Private Sub Workbook_Deactivate()
                                If Range("Z1000").Value = 1 Then
                                    MsgBox "çıkma mesajı"
                                Else
                                    MsgBox "deaktive mesajı"
                                End If
                            End Sub

Buradaki süreç şöyle işleyecek. Diyelim ki kullanıcı dosya açıldıktan bir süre sonra başka bir dosyaya geçmek istedi, Deactivate olayı ilk kez devreye girer, Z1000 hücresinde 1 yazıyor mu diye bakar, yazmadığı için sadece "deaktive mesajını" verir. Sonra diyelim ki yine aktif oldu, bir süre sonra tekrar başka dosyaya geçtiğinde bu süreç aynen tekrar eder. Ne zaman ki dosyadan çıkmak isterse, önce BeforeClose devreye girer ve Z1000'e 1 değerini atar, sonra Deactivate olayı devreye girer, Z1000=1 olduğu için sadece çıkma mesajı verilir. Burada BeforeClose içine hiçbir mesaj yazmadık, böyle yapsaydık hem "Çıkma mesajı" hem de "Deactive mesajı" verilmiş olurdu, bu da arka arkaya 2 farklı mesaj kutusu demek olurdu ki pek şık bir durum olmazdı. Burada tabi ben sadece mesaj farklılaşması yaptım, sizin ihtiyacınıza göre farklı işlemler de yaptırılabilir.

Auto_close

Auto_open için yazılan bilgilerin benzeri aynı mantıkta Auto_close için de geçerlidir.

Kapanmadan Önce Sayfayı Default Ayarlara Getirmek

Başlangıç ayarları özellikle belli değerlere ayarlanmış bir dosyanız olsun. Kullanıcılar, bu dosyada çeşitli oynamalar yaptıktan sonra kapatacaklar diyelim. Dosyayı ilk ayarlarına getirmek için BeforeClose olayı kullanılabilir.

                    
                        Private Sub Workbook_BeforeClose(Cancel As Boolean)
                          Application.EnableEvents = False
                          Range("B1") = vbNullString
                          Range("B2") = 400
                          Range("C21") = "Toplam"
                          '....
                          Application.EnableEvents = True
                        End Sub

Application.EnableEvents kullanmamızın sebebi, sayfada Worksheet_Change eventi varsa bu tetiklenmesin diyedir. Bu konuda daha detay bilgi için buraya bakabilirsiniz.

Diğer Olaylar

Yeni Sayfa Eklenmesi

Yeni bir sayfa yaratıldığında devreye girer. Bazen birilerine gönderdiğiniz bir dosyada yeni sayfa yaratılmasını istemiyor ve kullanıcıların mevcut sayfa(lar) üzerinden çalışmasını istiyorsanız bu eventi aşağıdaki gibi kullanabilirsiniz.

                    
                            Private Sub Workbook_NewSheet(ByVal Sh As Object)
                                Application.ScreenUpdating = False
                                MsgBox "yeni sayfa yaratamazsın"
                                Application.DisplayAlerts = False
                                Sh.Delete
                                Application.DisplayAlerts = True
                                Application.ScreenUpdating = True
                            End Sub

NOT: ScreenUpdating'i kullanma sebebi, kullanıcının geçici de olsa sayfanın yaratıldığını görmesini engellemek içindir.

Başka olaylar da bulunuyor, ancak bunları araştırmayı size bırakıyorum.

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

Her ne kadar workbook eventlerinde kısırdöngüye giren event durumu pek rastlanılan bir durum olmasa da, yine de belirli şartlarda eventleri geçici olarak durdurmak, o özel durum geçince tekrar aktive olmalarını sağlamak isteyebilirsiniz.

Ben mesela QuickAccessToolbar üzerine eventleri toggle buton mantığı ile bir aktif bir pasif yapan bir düğme koydum. Zira sıklıkla schedule edilmiş dosyaları açıp içlerinde güncelleme yapma ihtiyacım oluyor. Normalde bunlar açıldıklarında Workbook_Open makrolarının otomatikman çalışmaları gerekiyor, ama güncelleme yapacaksam bunların çalışmasını engellemek için geçici olarak eventleri pasifleştiriyorum, işimi bitirince tekrar aktifleştiriyorum.

                    
                        Sub toggle_event()
                        If Application.EnableEvents = True Then
                            Application.EnableEvents = False
                            Application.StatusBar = "EnableEvents=False****************************************EnableEvents=False******************************************EnableEvents=False****************************************EnableEvents=False****************************************"
                        Else
                            Application.EnableEvents = True
                            Application.StatusBar = "EnableEvents=True*****************************************EnableEvents=True*******************************************EnableEvents=True*******************************************EnableEvents=True******************************************"
                        End If
                        End Sub

Voltranı Oluşturmak

Workbook_Open eventini, Application.OnTime, Logger fonksiyonu ve Veritabanı (Connection veya ListObject) refresh işlemleriyle birlikte kullandığınızda Voltranı oluşturmuş olursunuz. Bu dörtlü sizi ve kurumunuzu inanılmaz bir verimlilik sürecine sokar. Zira bu şekilde istediğiniz sayıda raporu (yeterli cihazın olması varsayımı altında) otomatiğe bağlayabilirsiniz. Diğer üçüyle ilgili detaylara ilgili linklerde ulaşabilirsiniz, ben buraya sadece küçük bir örnek koymak istiyorum.

Application.OnTime ile schedule edilmiş bir makro düşünün. Bu, Voltranın 1. üyesinin devreye girdiği andır. Bu makro çalıştığında (ve çalışırken kod boyunca önemli yerlerde) Log kaydı oluşturacaktır: 2. üye de tamam. Bu makro ile belirli bir Excel dosya açılmaktadır ve devreye bu dosyanın Workbook_Open eventi girer, işte 3. üye. Son olarak da bu makro içinden çeşitli veritabanından data çekme işlemi yapılarak 4. üye de devreye sokulmuş olur. Son 2 üye aşağıda gösterilmiştir.

                    
                                Private Sub Workbook_Open()
                                'Önceki kodlar
                                'Çeşitli ön kontroller (kullanıcı, pc adı vs.) burada yapılabilir

                                For Each cn In ActiveWorkbook.Connections
                                    cn.ODBCConnection.BackgroundQuery = False
                                    cn.Refresh
                                Next

                                'Sonraki kodlar
                                'dosyayı kaydetme ve alıcılara mail gönderme kodları burada yer alabilir

                                End Sub

Kısıtlama Uygulama ve Veri Güvenliği Sağlama

Diyelim ki gizli bilgiler içeren bir dosyayı birine gönderdiniz, veya genel kullanım için bir add-in yazdınız ve bu add-in gizli/hassas bilgiler içeren bir veritabanı dosyasından belli bir sorgu çalıştırıp getiriyor. Her neyse, bu bilgilerin kullanıcılar tarafından yazdırılmasını, copy/paste ile başka dosyaya kopyalanıp orada da yazdırılmasını engellemek istiyorsunuz ve herhangi bir şekilde mail olarak gönderilmesini de engellemek istiyorsunuz. Aşağıda, bunlara ait çözümleri bulabilirsiniz.

Sayfanın Yazdırılmasını Engelleme

Bunu aşağıdaki basit kod ile yapmak mümkündür.

                    
                                Private Sub Workbook_BeforePrint(Cancel As Boolean)
                                  Cancel = True
                                  MsgBox "Bu dosyayı print almanıza izin verilmemiştir."
                                End Sub

Print Alma Yetkisi

Eğer belli bir kişinin/kişilerin print alma yetkisi olsun isterseniz belli bir sicili/sicilleri kontrol edebilirsiniz. Aşağıda tek sicil numarasının kontrol örneği var, siz isterseniz bir diziye (veya collection) tüm yetkilileri atıp o dizi içinde var mı diye de kontrol edebilirsiniz. (Username'in sicil döndürdüğü varsayımı ile hareket edilmiştir. Sizin kurumda isim de kullanılıyor olabilir, o zaman daha farklı bir yöntem denemeniz gerekebilir. Sadece özel bir şifreyi bilenlerin kaydetmeye yetkisi olması gibi)

                    
                    Private Sub Workbook_BeforePrint(Cancel As Boolean)
                    If Environ("username")<>12345 Then
                      Cancel = True
                      MsgBox "Bu dosyayı print almanıza izin verilmemiştir."
                    End If
                    End Sub

Cut/Copy Engelleme

Cut/Copy işlemlerini engellemek için biraz daha uğraşmamız gerekiyor. Öncelikle yasaklama işlemini hem Workbook_Open hem de Workbook_Activate eventlerinde kullanmamız gerekiyor. Ayrıca kullanıcı başka bir dosyayı açtığında o dosyada yasakların kalkıp normale dönmesi gerektiği için de işlemlerin tersinin Workbook_Deactivate eventinde yapılması gerekiyor.

Aşağıdaki örnekte kullanıcıya bir de mesaj veriyoruz. Mesaj vermek istemiyorsak aşağıda "cutcopyengel" olan her şeyi "" olarak değiştirmeniz yeterlidir.

                    
                            Private Sub Workbook_Open()
                            With Application
                                .CutCopyMode = False
                                .OnKey "^c", "cutcopyengel" 'Ctrl+C ile copy
                                .OnKey "^x", "cutcopyengel" 'Ctrl+X ile cut
                                .OnKey "^{INSERT}", "cutcopyengel" 'Ctrl+Insert ile copy
                                .OnKey "+{DELETE}", "cutcopyengel" 'Shift+Delete ile cut
                                .OnKey "+{DEL}", "cutcopyengel" 'Shift+Del ile cut
                                'mousela taşıma iptali
                                .CellDragAndDrop = False
                                'sağ tıklama ile engel
                                .CommandBars("Cell").Controls(1).Enabled = False 'cut
                                .CommandBars("Cell").Controls(2).Enabled = False 'copy
                            End With
                            End Sub

                            Private Sub Workbook_Activate()
                            With Application
                                .CutCopyMode = False
                                .OnKey "^c", "cutcopyengel" 'Ctrl+C ile copy
                                .OnKey "^x", "cutcopyengel" 'Ctrl+X ile cut
                                .OnKey "^{INSERT}", "cutcopyengel" 'Ctrl+Insert ile copy
                                .OnKey "+{DELETE}", "cutcopyengel" 'Shift+Delete ile cut
                                .OnKey "+{DEL}", "cutcopyengel" 'Shift+Del ile cut
                                'mousela taşıma iptali
                                .CellDragAndDrop = False
                                'sağ tıklama ile engel
                                .CommandBars("Cell").Controls(1).Enabled = False 'cut
                                .CommandBars("Cell").Controls(2).Enabled = False 'copy
                            End With
                            End Sub

                            'Deactivate eventinde OnKey'in ikinci parametresini boş bırakarak 
                            'işlemi tersine çeviriyoruz
                            Private Sub Workbook_Deactivate()
                            With Application
                                .CutCopyMode = True
                                .CellDragAndDrop = True
                                .OnKey "^c"
                                .OnKey "^x"
                                .OnKey "^{INSERT}"
                                .OnKey "+{DELETE}"
                                .OnKey "+{DEL}"
                                .CommandBars("Cell").Controls(1).Enabled = True
                                .CommandBars("Cell").Controls(2).Enabled = True
                            End With
                            End Sub

                            'Bu da ayrı bir modül içindeki kodumuz
                            Sub cutcopyengel()
                               MsgBox "Bu dosyada cut/copy yapamazsınız"
                            End Sub

Mail Göndermeyi Engelleme

Dosyayı mail ile göndermeyi engellemek için iki iş yapmak lazım:

Excel içinden File menüsündeki Send As Attachment düğmesini kullanmayı engellemek
Dosyayı kaydedip Windows Explorer üzerinden veya Outlook ile göndermeyi engellemek

1. yöntemde Ribbon'u özelleştirmek gerekiyor, aynı zamanda ilgili mail gönderme butonu QAT (Quick Access Toolbar) üzerinde de olabilir diye QAT'ı göstermeyi de engellemek lazım. Bunları ileri yöntemler sayfasında göreceğiz.

2. yöntemde ise dosyanın kaydolmasını engellemeliyiz.

                    
                        Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
                          Cancel = True
                          MsgBox "Bu dosyayı kaydedemezsiniz"
                        End Sub

Tabii yine belli kişiler için kaydetme izni vermek isterseniz Environ("username") özelliğini kullanabilirsiniz.

Tüm Sayfaları İlgilendiren Olaylar

Her ne kadar tüm sayfaları ilgilendiren olaylar Workbook eventleri olsa da onları burada değil Worksheet olayları içinde anlatmayı uygun buldum. Bu sayfaya alttaki İleri butonuna tıklayarak ulaşabilirsiniz.