• Zapisz się do newslettera, aby otrzymywać powiadomienia o nowościach na blogu
    Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Lista rozwijana z uprawnieniami cz. 2 (VBA)

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:

MalinowyExcel Listy rozwijane z uprawnieniami cd VBA Wynik

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:

 

 

 

Spodobał Ci się ten artykuł?

Jeśli TAK, zapisz się na newsletter, aby otrzymywać powiadomienia
o o nowościach na blogu.

Zapisując się, wyrażasz zgodę na przesyłanie Ci informacji o nowościach na tym blogu. Zgodę możesz w każdej chwili wycofać (szczegóły).

Tagi , , , , , , , , , , , , .Dodaj do zakładek Link.

Dodaj komentarz

Twój adres email nie zostanie opublikowany. Pola, których wypełnienie jest wymagane, są oznaczone symbolem *