Czyli jeśli góra zatwierdziła – nie można edytować!

W poprzednim wpisie pokazywałam jak zahasłować wybrane zakresy komórek w arkuszu tak, aby mogły je edytować jedynie osoby, które znają hasło. Dzisiaj pokażę kod VBA, który nie pozwoli edytować komórek, jeśli konkretna komórka została już uzupełniona.

W naszej sytuacji akceptowania warunków zamówień będzie to działać tak, że jeśli Kierownik Kontroli Kredytowej (KKK) zaakceptuje umowę (zaakceptuje = Tak), osoby wcześniej akceptujące (Dział Obsługi Klienta (DOK) i Dyrektor Sprzedaży (DS)) nie będą mogli już zmieniać swoich decyzji. Czyli, krótko mówiąc, zamówienie zostanie finalnie zaakceptowane.

Ma to działać tak:

Zabierzmy się więc za stworzenie takiego rozwiązania.

Założenia

Są takie same jak w poprzednim wpisie, a w szczególności:

• nazwane zakresy kolumn tabeli: AkceptDS, AkceptDOK

Pamiętajmy też, że na arkusz założona jest ochrona i chronione są zakresy (z hasłem). Zanim użytkownik zmienić wartość w komórce, a raczej będzie próbował – pojawi się monit o hasło. Monit ten pojawi się tylko raz w jednej sesji otwarcia pliku.

Lista rozwijana z uprawnieniami – kod VBA

Sprawdzanie, czy KKK zaakceptował zamówienie ma się odbywać wtedy, gdy użytkownik próbuje zmienić wartość w zakresie AkceptDS lub AkceptDOK. Wtedy więc chcemy uruchomić makro. Triggerem, czyli akcją uruchamiającą ma być więc zdarzenia arkusza: zmiana wartości komórki, czyli Change. Tam właśnie umieścimy nasz kod. O tym, jak dodawać zdarzenia arkusza pisałam np. tutaj.

Sam kod ma sprawdzać:

1. czy komórka, którą próbuje zmienić user jest w zakresie AkceptDS lub AkceptDOK
2. jeśli tak, to cofnąć operację wpisania wartości
3. wyświetlić komunikat

Jak widać niewiele :). Dorzucimy sobie jeszcze kilka technicznych kwestii jak obsługa błędów i wyłączanie zdarzeń (żeby makro się nie zapętlało). Ale najpierw podstawa, czyli:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Zakres As Range
    
    Set Zakres = Union(Me.Range("AkceptDOK"), Me.Range("AkceptDS"))
  
    If (Not Intersect(Target, Zakres) Is Nothing) And Me.Cells(Target.Row, 7).Value = "Tak" Then
       
        Application.Undo
        
        MsgBox "Nie możesz edytować już tych danych! KKK zaakceptował!", vbExclamation, "Niedozwolona operacja"
        
    End If
    
End Sub

Powyższy kod  po pierwsze definiuje zakres, w którym mamy sprawdzać zmiany: zmienna Zakres, która łączy – Union – oba interesujące nas zakresy w arkuszu.

Następnie sprawdza, czy zmieniona komórka (Target) jest w tym zakresie i czy wartość komórki w kolumnie nr 7 (tam są decyzje KKK) jest Tak. Jeśli to się zgadza – cofa operację i wyświetla komunikat o błędzie w okienku z wykrzyknikiem (vbExclamation).

Teraz tylko wspomniane wcześniej kwestie techniczne. Gdybyśmy uruchomili ten kod – zapętlałby się. Dlaczego? Ponieważ cofnięcie operacji też jest zmianą komórki. A na każdą zmianę komórki nasze makro jest odpalane. Trzeba więc wyłączyć na tę chwilkę obsługę zdarzeń w arkuszu. Jest to jedna linijka, którą wrzucimy do if-a, który teraz będzie wyglądał tak:

    If (Not Intersect(Target, Zakres) Is Nothing) And Me.Cells(Target.Row, 7).Value = "Tak" Then
        
        Application.EnableEvents = False
        Application.Undo
        
        MsgBox "Nie możesz edytować już tych danych! KKK zaakceptował!", vbExclamation, "Niedozwolona operacja"
        
    End If

Ok. Zdarzenia wyłączyliśmy. Dalej w kodzie cofamy operację, wyświetlamy komunikat, ale… trzeba jeszcze te zdarzenia włączyć, czyli po sobie posprzątać :). Za if-em dodamy więc linijkę włączającą obsługę błędów, o tak:

    Application.EnableEvents = True

Jeszcze warto dorzucić tutaj obsługę błędów, jakby użytkownik np. postanowił zmienić kilka komórek jednocześnie. Odpowiada za to instrukcja On Error GoTo Obsluga, gdzie Obsluga to miejsce w kodzie, do którego mamy przejść, gdy napotkamy błąd. Czyli miejsce, gdzie przywracamy obsługę zdarzeń. Całość wygląda tak:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Zakres As Range
        
    On Error GoTo Obsluga
    
    Set Zakres = Union(Me.Range("AkceptDOK"), Me.Range("AkceptDS"))
  
    If (Not Intersect(Target, Zakres) Is Nothing) And Me.Cells(Target.Row, 7).Value = "Tak" Then
        
        Application.EnableEvents = False
        Application.Undo
        
        MsgBox "Nie możesz edytować już tych danych! KKK zaakceptował!", vbExclamation, "Niedozwolona operacja"
        
    End If
    
Obsluga:
    Application.EnableEvents = True
    
End Sub

I to tyle. Jak widać kod krótki :).

Mam nadzieję, że Ci się przyda!canv

Powiązane produkty

• Listy rozwijane – podczas tej lekcji pokazuję jak zrobić listę rozwijaną zarówno taką, jak we wpisie (czyli sprawdzanie poprawności), jak i formant formularza, oraz jak je wykorzystać np. na wykresach.
• Nagrywanie makr – podczas tej dwugodzinnej lekcji dowiesz się jak w sprytny sposób nagrywać całkiem inteligentne makra
• Kurs Excel w codziennej pracy – mający na celu uporządkować Twoją wiedzę na temat Excela. Jest świetny zarówno dla osób, które chcą uporządkować swoją wiedzę, zdobywaną do tej pory “po omacku”, jak i takich, które dopiero zaczynają swoją przygodę z Excelem.

Plik do pobrania:

MalinowyExcel Lista rozwijana z uprawnieniami cd VBA dw.zip

I film na Youtubie, gdzie pokazuję powyższe rozwiązanie: