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ą:
- Komórki w tabeli: kolumny C:F muszą być odblokowane. User ma mieć możliwość ich modyfikacji zawsze.
- 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)
- 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:
- Zaznaczyć je
- Wejść do okienka formatowania komórki (Ctrl + 1), zakładka Ochrona
- 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:
- Arkusz jest chroniony
- Komórki w wierszach poniżej tabeli nie są chronione (musimy mieć możliwość dopisania w nich danych, gdy arkusz jest zablokowany)
- 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ć :).
Mam pytanie
czy istnieje dopisanie codu w moim makro, żeby przed działaniem zdejmowało ochronę z arkusza „formuły”, w którym są zapisane wiersze z odpowiednimi formułami obliczeniowymi i specyfikacja, w którym pracuję i do którego są przekopiowywane wiersze z arkusza formuły. Wszystko działa dobrze jak nie ma ochrony na arkuszu „formuły” a ochrona na arkuszu „specyfikacja” wyłącza np listę rozwijaną, która jest przekopiowywana. Marzy mi się aby arkusze „formuły” i „specyfikacja” miały ochronę a byłaby zdejmowana podczas uruchamiania makra a na koniec z powrotem ochrona wracała.
makro działa pod formantem z listy rozwijanej
poniżej makro. byłbym wdzięczny za jakąś podpowiedź.
Sub Wklej_wiersz_2K()
’
’ Kopiowanie wierszy i wstawianie przez
’ Menu „WSTAW skopiowane komórki” – „KOŁOWE”
’ Makro zarejestrowane 05-04-2011, autor JW
’
Dim c, d, poz2
c = 25
Application.ScreenUpdating = False
Selection.EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Sheets(„Formuły”).Select
d = Worksheets(„Formuły”).Cells(46, 1).Value
poz2 = c + d
Rows(poz2).Select
Selection.Copy
Sheets(„Specyfikacja”).Select
'Selection.Insert Shift:=xlDown
ActiveSheet.Paste
Sheets(„Formuły”).Select
Application.CutCopyMode = False
Sheets(„Formuły”).Cells(46, 1).Value = 1
Sheets(„Specyfikacja”).Select
Application.ScreenUpdating = True
ActiveCell.Offset(1, 0).Activate
End Sub
Hej, do zdejmowania ochrony jest taki kod np.: Sheets(1).unprotect „hasło”
Hasło może być czystym tekstem albo zmienną oczywiście.
🙂
A ja mam pytanie co w sytuacji gdy moja tabela nie zaczyna się w pierwszym wierszu arkusza? Jestem początkująca, a właśnie potrzebuję założyć ochronę na plik gdzie tabela zaczyna się dopiero od 7 wiersza arkusza (nagłówki tabeli)?
Hej! Do tej linijki:
OstWiersz = Tabela.Range.Rows.Count
trzeba dodać odpowiednią liczbę wierszy, czyli +6.
A tutaj:
Tabela.Resize Me.Range(„$C$1:$G$” & OstWiersz + 1)
wpisać odpowiednie zakres taki, jak jest w Twoim arkuszu 🙂
Chyba to wystarczy, tak patrzę.
Witam,
mam taki sam problem, ale porada „+6” niestety nie pomaga. Jeśli umieszczę tabelę w pierwszym wierszu wszystko działa. Dodam pierwszy wiersz i dodaje +1 po rows.count i już nie działa 🙁 Jakieś sugestie?
Mam już rozwiązanie 🙂
należy zmienić tylko tu:
Tabela.Resize Me.Range(„$A$3:$V$” & OstWiersz + 3)
gdzie 3 jest numerem wiersza w ktorym zaczyna sie tabela
Ooo, super, że się udało!!! 🙂
Witam,
bardzo pomocne makro, fajnie działa i dziękuję za pomoc 🙂
Mam tylko problem z zabezpieczeniami, mianowicie potrzebuję aby użytkownik arkusza mógł sortować i filtrować dane po dodaniu wiersza, a niestety te uprawnienia są odbierane po dodaniu wiersza. Czy można jakoś rozwiązać ten problem?
Pozdrawiam serdecznie
Hej, bardzo się cieszę, że makro się przydało :).
Ten problem powinno załatwić dopisanie tych opcji przy zakładaniu ochrony w VBA: AllowSorting:=True, AllowFiltering:=True 🙂
Jeżeli dobrze rozumiem to wklejam to do obsługi, ale niestety wyskakuje błąd Expected expression…
Mogę prosić o wklejenie kodu jak ma wyglądać ta część kodu?
Hej, chodzi mi o to: me.Protect haslo, allowSorting:=true, AllowFiltering:=true 🙂
Cześć,
makro super sprawa jednak też bardzo istotna jest dla mnie możliwość filtrowania, a niestety podane rozwiązanie w dalszym ciągu nie działa 🙁 Please help me 🙁
Rozwiązanie rewelka! Właśnie uratowało mi mój „projekt” gdy odkryłam, że po nałożeniu ochrony tabela się nie rozwija. Czy istnieje możliwość aby podobny mechanizm zadziałał po wklejeniu wartości w kilka komórek w wierszu pierwszym pod tabela? W moim pliku użytkownicy maja uzupełniać tabele wklejając wartości z innego pliku (zakres 5 sąsiadujących kolumn czasem kilu wierszy ( ilość wierszy zmienna)). Ma Pani na to jakiś pomysł? Póki co, po prostu wpisem w komórce ” uruchamiają ” wiersz a potem wklejają
Bardzo się cieszę :).
Trzeba byłoby zrobić obsługę błędu, który generuje wklejenie zakresu, a nie pojedynczej komórki…
No i dobra wiadomość jest taka, że Microsoft już wie o tym, że użytkowników to wkurza i miejmy nadzieję, ze wkrótce nie trzeba będzie kombinować z makrami, tylko tabele po prostu będą się rozszerzać w chronionych arkuszach :).
Super świetna robota!!!
Mam pytanie co w przypadku potrzeby usunięcia któregoś z wierszy.
Trzeba byłoby zdjąć znowu ochronę 🙁
Dziękuję za poradę. Bardziej mi się przyda sposób w jaki Pani tworzy zmienne makra. To przyda mi się w tworzeniu makr do innych zagadnień.
Bardzo się cieszę 🙂
Cześć! napisałaś w tekście „tabela zachowuje formatowanie, czyli podczas dopisywania do niej nowych danych – kopiuje formatowanie na nowe komórki! ” – czy spotkałaś się z sytuacją, w której tak nie jest? (ćwiczone na tabelach w pracy, długo i na wiele sposobów, i dalej nie wiem, ocokaman). Po dopisaniu nowego wiersza format daty z normalnej (ten z gwiazdką) zmienia się na „niestandardowy”, amerykański. Mam dwie tabele obok siebie, sformatowane identycznie, i w jednej tabeli format daty w kolumnie robi fikołka, a w drugiej nie …
Cześć!
Tak, niestety się spotkałam ;(. Głownie była to kwestia tego, że komórka wcześniej była sformatowana na jakiś inny format i, niestety, to miało pierwszeństwo przed formatowaniem tabeli. Były też sytuacje, kiedy tak nie było, a nadal formatowanie się nie kopiowało. I na to odpowiedzi niestety nie znalazłam ;(.