Czyli auto-rozszerzanie tabeli…

Niby prosta rzecz: mamy chroniony arkusz, w nim obiekt tabela. Chcemy ją oczywiście uzupełniać i oczekujemy, że, jak to ma w zwyczaju tabela, powiększy się ona o dopisane poniżej niej dane.

A tu ZONK! Dane może i się dopisują (zakładając, że te komórki są odblokowane), ale tabela nie powiększa się w chronionym arkuszu!!!

Tak niestety się dzieje…

W tym artykule pospieszę na ratunek (koślawy co prawda – wolałabym, żeby tabele po prosu DZIAŁAŁY w chronionym arkuszu!). Pokażę makro, które spowoduje, że gdy user dopisze nowe dane bezpośrednio pod tabelą – powiększy się ona o ten dopisany rekord.

Dodatkowo, dzięki sprytnemu wykorzystaniu stylów sprawię też, że kopiowane formuły z kolumn obliczeniowych będą chronione, natomiast użytkownik nadal będzie mógł korzystać z list rozwijanych i wpisywać dane w odpowiednie kolumny tabeli.

Efekt będzie następujący:

Jedziemy!

Formatka, czyli z czym mamy do czynienia?

Zanim przejdziemy do pisania kodu – najpierw musimy mieć odpowiednio przygotowaną formatkę. Jest w niej obiekt tabela (nazwany tbFV), w którym przechowujemy dane o płatnościach. Dla ułatwienia kodu, tabela ta zaczyna się w pierwszym wierszu arkusza. Użytkownik uzupełnia pierwsze 4 kolumny (C:F), a ostatnia sama się wylicza, gdyż jest tam formuła. Założenie jest takie, że tej kolumny user ma nie tykać. Dodatkowo, informację o tym, czy faktura została zapłacona czy nie, użytkownik wybiera z listy (OK/NOK).

Formatka wygląda następująco:

Formatka

I tak w naszej formatce:

• kolumny uzupełniane przez usera: C:F
  • w tym kolumna F – dane wybierane z listy
• kolumny z formułą (zablokowane): G

Chcemy, aby po dopisaniu nowych danych – powyższe opcje ochrony zostały takie same! Aby to osiągnąć, należy pamiętać o jednej, niezwykle istotnej rzeczy: tabela zachowuje formatowanie, czyli podczas dopisywania do niej nowych danych – kopiuje formatowanie na nowe komórki! Tzn., że nowe komórki dostaną takie samo formatowanie, jak miały komórki w tej samej kolumnie w tabeli. Przed ochronieniem arkusza musimy więc odpowiednio przygotować komórki zarówno w tabeli, jak i te pod nią:

1. Komórki w tabeli: kolumny C:F muszą być odblokowane. User ma mieć możliwość ich modyfikacji zawsze.
2. Komórki w tabeli: kolumna G: ta kolumna musi być zablokowna do edycji przez usera, a formuły w niej ukryte (nie chcemy, by widziano formułę, choć to opcjonalne)
3. Komórki pod tabelą (te, które zamierzamy w przyszłości uzupełniać nowymi danymi) – wszystkie odblokowane!

A teraz po kolei o tym, jak to zrobić:

Odblokowanie komórek

Dotyczy komórek w kolumnie C:F tabeli i komórek pod tabelą.

Aby odblokować komórki, czyli spowodować, że po założeniu ochrony arkusza będzie można coś do nich wpisywać – należy:

1. Zaznaczyć je
2. Wejść do okienka formatowania komórki (Ctrl + 1), zakładka Ochrona
3. Odznaczyć opcję Zablokuj:

Formatka – odblokowane komórki

Ta opcja będzie aktywna dopiero po uruchomieniu ochrony arkusza.

Zablokowanie komórki, ukrycie formuł

Dotyczy kolumny G w tabeli.

Odwrotną operację zrobimy z kolumną z formułami w tabeli. Tutaj będziemy blokować komórki, żeby user nam nic w nich nie popsuł. Dodatkowo można ukryć formułę, która się w nich znajduje, aby nie mógł jej podpatrzeć np. na pasku formuły.

W tym celu powtarzamy kroki jak przy odblokowywaniu komórek, tylko zaznaczamy opcje, jak na obrazku:

Formatka – zablokowane komórki

Ja zwykle stosuję w swoich arkuszach zasadę, że formuły zaznaczam na niebiesko i ustawiam im właśnie zablokowanie i ukrycie formuły. Ponieważ robię to za każdym razem, w każdym arkuszu – stworzyłam sobie specjalny styl Formuła, żeby jednym kliknięciem ustawiać dokładnie to formatowanie. Styl ten podpięłam do domyślnego arkusza, więc mam go w każdym nowym pliku – mega oszczędza mi to czas! O stylach i domyślnym szablonie arkusza mówiłam na tym webinarze.

Teraz, gdy te czynności są już wykonane, możemy zakładać ochronę arkusza.

Ochrona arkusza

Ochronę zakładamy np. z menu Recenzja/ Chroń arkusz. Jeśli chcesz ochronić arkusz hasłem – tutaj jest miejsce, aby to hasło podać. Jeśli nie podasz – każdy będzie mógł zdjąć ochronę (jeśli wie jak ;)).

To, czy nadasz hasło czy nie – zależy tylko od Ciebie. Pamiętaj jednak, że będzie to miało znaczenie w makrze. Ja ochronę założę z hasłem, aby pokazać jak to hasło uwzględnić w kodzie.

Zakładanie ochrony arkusza (z hasłem)

Teraz właśnie jesteśmy na etapie, że tabela się nie powiększa, gdy dopiszemy pod nią dane. Będziemy naprawiać tę niedoróbkę.

Założenia makra

Zanim jednak przejdę do omawiania kodu – najpierw założenia:

1. Arkusz jest chroniony
2. Komórki w wierszach poniżej tabeli nie są chronione (musimy mieć możliwość dopisania w nich danych, gdy arkusz jest zablokowany)
3. Tabela zaczyna się w pierwszym wierszu arkusza

Nie ukrywam, że te założenia są po to, aby uprościć sytuację, a co za tym idzie – kod.

Omówienie kodu

Logika kodu jest taka, że makro sprawdzi, czy użytkownik wpisał wartość w jedną z komórek zakresu tuż pod tabelą (C21:G21). Jeśli tak – zostanie zdjęta ochrona arkusza, zakres tabeli zostanie rozszerzony, a następnie arkusz znów zostanie ochroniony.

Makro będzie przechowywane w module prywatnym arkusza i będzie to zdarzenie Worksheet_Change, czyli uruchomi się w momencie jakiejkolwiek zmiany wartości komórki arkusza. O tym, gdzie wpisywać kod (stworzyć zdarzenie) pisałam już np. tutaj (możesz to też zobaczyć na filmie na końcu tego artykułu).

1. Zaczynamy od deklaracji zmiennych i określenia hasła (ja to zrobiłam w zmiennej lokalnej, żeby już nie mieszać). Jeśli Twój arkusz nie będzie miał hasła przy ochronie – pomiń zmienną Hasło i jakiekolwiek jej wystąpienie w kodzie poniżej.

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim OstWiersz As Long, Tabela As ListObject, Zakres As Range
    Dim Haslo As String

    Haslo = "t"
End Sub

2. Następnie jest obsługa błędów, sprawdzenie, czy zmieniana komórka przypadkiem nie jest pusta i wyłączenie obsługi zdarzeń.

Jeśli jakikolwiek błąd wystąpi – przejdziemy do kawałka kodu (Obsluga), który (na razie) włączy obsługę zdarzeń:

    On Error GoTo Obsluga
    If Target.Value = "" Then Exit Sub

    Application.EnableEvents = False

Obsluga:
    Application.EnableEvents = True
End Sub

3. Dalsze linijki wpisujemy w środek powyższego kodu, czyli przed etykietą Obsluga.

Teraz zdefiniujemy naszą Tabelę, sprawdzimy jaki jest jej ostatni wiersz i określimy zakres tuż pod tabelą (do niego użytkownik wpisuje nowe wartości i wtedy tabela ma się powiększyć):

    Set Tabela = Me.ListObjects("tbFV")
       OstWiersz = Tabela.Range.Rows.Count
    Set Zakres = Tabela.Range.Rows(OstWiersz + 1)

4. Teraz sprawdzimy czy zakres pod tabelą, Zakres, i nasza uzupełniona komórka (Target) mają część wspólną (Intersect), czyli po prostu, czy Target znajduje się w Zakresie. Jeśli nie – nic się nie stanie, jeśli tak – odblokowujemy arkusz, a następnie zwiększamy rozmiar tabeli.

Dodatkowo, ponieważ własnie zdjęliśmy ochronę, koniecznie musimy ją założyć, aby pozostawić arkusz w takim stanie, w jakim go zastaliśmy. Dodajemy więc linijkę zakładającą hasło zaraz pod etykietą Obsluga:

    If Not Intersect(Zakres, Target) Is Nothing Then
        Me.Unprotect Haslo
        Tabela.Resize Me.Range("$C$1:$G$" & OstWiersz + 1)
    End If

Obsluga:
    Me.Protect Haslo
    Application.EnableEvents = True
End Sub

5. Całość wygląda tak:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim OstWiersz As Long, Tabela As ListObject, Zakres As Range
    Dim Haslo As String

    Haslo = "t"

    On Error GoTo Obsluga
    If Target.Value = "" Then Exit Sub

    Application.EnableEvents = False

    Set Tabela = Me.ListObjects("tbFV")
       OstWiersz = Tabela.Range.Rows.Count
    Set Zakres = Tabela.Range.Rows(OstWiersz + 1)

    If Not Intersect(Zakres, Target) Is Nothing Then
        Me.Unprotect Haslo
        Tabela.Resize Me.Range("$C$1:$G$" & OstWiersz + 1)
    End If

Obsluga:
    Me.Protect Haslo
    Application.EnableEvents = True
End Sub

W nowym wierszu wszystko działa pięknie: jest on częścią tabeli, kolumna z formułą jest chroniona, a pozostałe nie. Dzieje się tak dlatego, że podczas dodawania kolejnych wierszy do tabeli kopiowane jest tabelowe formatowanie komórki, czyli skopiowana formuła dostaje format z ochroną, a kolumny ręcznie uzupełniane przez usera – bez ochrony. Poprawność danych (lista rozwijana) też jest kopiowana.

Pamiętaj, aby plik zapisać tak, aby obsługiwał makra, czyli z rozszerzeniem .xlsm lub .xlsb.

O to dokładnie chodziło! Jeśli u Ciebie też działa i jest to coś, czego szukała(e)ś – mam do Ciebie ogromną prośbę: udostępnij ten artykuł swoim znajomym. Uważam, że to bardzo przydatna wiedza i, nieśmiało stwierdzę – kawał dobrej roboty z mojej strony. Na pewno przyda się wieeelu osobom. Dlatego udostępniaj! Dziękuję! :). Na koniec tradycyjnie gotowiec do pobrania tutaj.

Powiązane produkty

• WEBINAR: Motywy, style i domyślny szablon Excela: żeby przyspieszyć określanie komórek, które mają być chronione lub nie – zastosuj style
• WEBINAR: Walidacja danych – TRIKI: tutaj dowiesz się więcej o sprawdzaniu poprawności (w artykule były to użyte w tabeli listy rozwijane)

A tutaj film pokazujący to rozwiązanie:

Na koniec dodam, że problem niedziałania tabel w chronionym arkuszu denerwuje wieeeelu użytkowników. Dlatego do Microsoftu zostało już to zgłoszone – jeśli chcesz dodać swój głos w tej sprawie – zrób to tutaj (strona Excel User Voice). W prośbie tej zawarte jest o wiele więcej, niż podejmuję się w tym artykule, dlatego tym bardziej warto zagłosować :).