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 Worbook olaylarını seçer gibi seçip içlerini doldurmaya başlayabilirsinz. İ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

eventleridir.

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 yapalım. 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ölmü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 Exceli'n 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 Excele ç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 olmamaktaı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ü sayafası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 v.s 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 evetnleri 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 burda 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 bi hata alır.

Ayrıca düğmeyi silmesin veya başka bi 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 bulabilrisinz.

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ç safya 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)

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 'blank sayısı 0 ise, count=0 kontrolüne gelmediği için o noktayı kaldırdım
    [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
'----------------------------------------------------
Sub temizlik(alan As Range)
For Each a In alan
    a.Font.Color = vbBlack
Next a
End Sub
Sub Fontsizedeğiş(x As Integer, s As Integer)
    For i = 1 To 5
        Call Module2.beklet(s)
        DoEvents
        ActiveCell.Font.Size = x + i * 2
    Next i
    
    For i = 1 To 5
        Call Module2.beklet(s)
        DoEvents
        ActiveCell.Font.Size = x + 10 - i * 2
    Next i
End Sub
'----------------------------------------------------
Sub alancopypaste()
    For Each a In [alan]
        a.Value = a.Value
    Next a
End Sub

#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

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

Data Validation ve Yetki kontrolü

Seçimlere göre veritabanından sonuç getirmek