04.08.2018 tarihinde VBA bölümüne ObjelerDünyası sayfası eklenmiştir

25.07.2018 tarihinde VBA bölümüne Outlook programlama sayfası eklenmiştir

13.07.2018 tarihinde Formlar-Kontroller sayfası eklenmiştir

25.05.2018 Hosting şirketi dğeiştirmekten kaynaklı bir hata nedeniyle Excelent add-ini indirirken hata alınmaktaydı. Bu hata düzeltilmiştir. İki ayrı download alternatifi sunulmuştur. Kurumunuzun BT politikalarının veya şahsi PC’nizdeki güvenlik ayarlarının izin vermesi durumunda yöntemlerden biriyle kurulum yapabilmelisiniz. Bi sorun olursa bana iletebilirseniz sevinirim.

VBAMakro Olaylar 2

Workbook Olayları(Eventleri)

Sözkonusu Workbook nesnesi olduğunda, en temel eventler "açılma, kapanma, kaydolma" üçlüsüdür. bunun dışında pek tabiki 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 geçekleşip gerçekleşmeyeceğini de kontrol altına almak isteyebilriiz. 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 şeklide 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

Burdaki 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, bi 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, sora 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 default sayfayı 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.

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ünelleme 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.

İlgili düğmeye atadığım kod şöyle:

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ı v.s gbi) 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ıtlamalar 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ı doyasından belli bir sorgu çalıştırıp getiriyor. Herneyse, 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	

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 Workboook_Open hem de Workboook_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 Workboook_Deactivate eventinde yapılması gerekiyor.

Aşağıdaki örnek 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

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

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.

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

1.yöntemde Ribbonu özelleştirmek gerekiyor, aynı zamanda ilgili mail gönderme butonu QAT(QuickAccessToolbar) üzerinde de olabilir diye QAT'ı göstermeyi de engellemek lazım. Bunları ileri yöntemler sasyfası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

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

Tüm sayfaları ilgilendiren 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.

YORUMLAR