Czyli zdarzenie arkusza tworzące listę arkuszy

Zadanie na dziś to stworzenie listy rozwijanej, zawierającej nazwy arkuszy w pliku. Powiedzmy, że oddziały naszego sklepu są w różnych miastach, każde miasto ma swój arkusz. My chcemy do głównego arkusza pobrać sumę sprzedaży z wybranego miasta. Interesujące nas miasto (arkusz) chcemy wybrać z listy rozwijanej, i na podstawie naszego wyboru ma się obliczyć suma sprzedaży z tego miast. O tak:

Jak już będziemy mieli nazwę arkusza to dalej pikuś – kiedyś już to opisywałam. Natomiast samo uzupełnienie listy – tutaj bez VBA się nie obejdzie – będą pętle, liczniki i inne bajerki, więc do dzieła!

1. Logika zadania

Najpierw należy się zastanowić, kiedy nasza lista arkuszy ma się aktualizować? Tzn. tak po ludzku to wiemy: wtedy, kiedy zostanie dodany/usunięty arkusz (ewentualnie zmiana kolejności, nazwy itp.). Od strony użytkownika tak to właśnie wygląda. Natomiast od strony twórcy aplikacji – musimy wejść na wyższy poziom szczegółowości i zastanowić się, kiedy technicznie ma się dokonać zmiana.

Czy na zmianę wartości komórki, w której jest lista (na mojej formatce: D4)?

No nie. Zmiana wartości jest wtórna – najpierw na listę trzeba załadować dane, żeby user mógł je wybrać, czyli docelowo zmienić wartość komórki.

Tutaj proponowałabym ładować wartości na listę w momencie aktywowania arkusza. Zauważ, że to jest moment, kiedy wiemy, że na pewno mamy do czynienia z dobrymi arkuszami. Jeśli jakiemuś jakiś arkusz dodamy, usuniemy, zmienimy mu nazwę itp. – zawsze następuje zmiana aktywnego arkusza. Krótko mówiąc: aby coś zrobić z arkuszem – należy go aktywować. Jedyny minus – jeśli robimy coś z arkuszem, w którym jest lista (u mnie: Dane). Ale liczę, że z tym arkuszem akurat nie będziemy nic robić, a docelowo w moim kodzie będę chciała go w ogóle pomijać – zobaczysz dalej :).

Z całego mojego wywodu wynika więc, że zdarzeniem, które będzie aktualizowało wartości na liście, będzie zdarzenie Worksheet_Activate. Tak, zdarzeniem, ponieważ makro ma się uruchomić „samo”, czyli na jakąś czynność/zdarzenie.

Ok, jak już wiemy kiedy ma się to dziać, to teraz zastanówmy się co ma się dziać?

Ponieważ do stworzenia listy rozwijanej skorzystam ze sprawdzania poprawności – najlepiej, aby dostarczyć temu narzędziu gotową nazwaną listę. Jeśli bowiem tak zrobimy – nie będziemy musieli martwić się o aktualizowanie adresu w liście rozwijanej, a jedynie o aktualizowanie nazwy. A listę arkuszy – zwykły zakres komórek – umieścimy w oddzielnym arkuszu roboczym.

Po tych rozmyślaniach już wiemy, że chcemy,  aby Excel stworzył nam listę wszystkich arkuszy (w oddzielnym arkuszu), a potem załadował ją do nazwy (zaktualizował nazwę o adres tej listy), która jest źródłem listy rozwijanej w komórce G4 w arkuszu Dane.

I to byłoby z grubsza tyle. Jest jeszcze kilka technicznych szczegółów, natomiast omówię je na etapie pisania kodu. Najpierw przygotujmy formatkę (bardzo ważny element układanki!).

2. Przygotowanie formatki

Ponieważ chcemy, aby nasze makro było możliwie proste – pewne czynności wykonamy zanim napiszemy makro. Chodzi mi tutaj konkretnie o stworzenie listy rozwijanej – na razie pustej, i nazwanie jej zakresu źródłowego. Jedziemy.

1. Arkusz roboczy i nazwanie zakresu. Potrzebujemy arkusz roboczy, w którym makro będzie wpisywało nazwy arkuszy. Ten arkusz możemy na koniec oczywiście ukryć. Dodaj więc arkusz, nazwij go Roboczy, a następnie nazwij komórkę A1 jako Lista_Arkusze (tutaj, w sekcji Nazywanie komórek, dowiesz się jak nazywać komórki i przy okazji jak stworzyć zdarzenie arkusza).
2. Stworzenie listy rozwijanej. Teraz, w arkuszu głównym, Dane, w komórce G4 należy stworzyć listę rozwijaną (czynność tę opisywałam tutaj), a następnie nazwać komórkę G4 jako WybranyArkusz.

Czynności te są o tyle istotne, że makro korzysta zarówno ze stworzonego własnie arkusza jak i nazw. Jak to mamy – możemy zabierać się za kodowanie.

3. Kod VBA

Wszystko dzieje się w zdarzeniu Worksheet_Activate, zatem tutaj:

Private Sub Worksheet_Activate()
    
End Sub

Na pewno będziemy potrzebowali zmiennych roboczego arkusza (ArkRob), technicznego arkusza dla pętli (Ark), Zakresu i licznika. Deklarujemy więc i przypisujemy wartości, jeśli się da:

Private Sub Worksheet_Activate()
    Dim ArkRob As Worksheet, Ark As Worksheet
    Dim Zakres As Range, Licznik As Long

    Set ArkRob = ThisWorkbook.Sheets("Roboczy")
End Sub

Z punktu widzenia użytkownika jeszcze nic się nie dzieje. Techniczne sprawy. Teraz jednak, jak już określiliśmy arkusz roboczy (ArkRob, czyli nasz arkusz o nazwie Roboczy), trzeba go wyczyścić – zakładamy bowiem, że będą w nim jakieś dane – te, z poprzedniej listy arkuszy. Nie wiemy, czy użytkownik nam jakichś arkuszy nie skasował, nie zmienił ich nazwy, więc listę arkuszy musimy stworzyć od początku, na „czystym tle”:

      ArkRob.Cells.Clear

Ok, zaraz pętla, która zczytuje nazwy arkuszy, ale przed nią – ustawimy sobie licznik, który będzie określał wiersz w arkuszu roboczym, do którego należy wstawić pobraną nazwę arkusza. Chcemy zacząć wstawiać od pierwszego wiersza, więc licznik ustawiamy na 1, a potem zaczynamy pętlę. Pętla ta przejedzie się po wszystkich arkuszach, odczyta ich nazwy i wstawi do odpowiedniego wiersza roboczego arkusza:

    Licznik = 1
    For Each Ark In Worksheets
            ArkRob.Cells(Licznik, 1).Value = Ark.Name
            Licznik = Licznik + 1
    Next

Powyższy kod działa na wszystkie arkusze, łącznie z arkuszem głównym (Dane) i arkuszem roboczym (Roboczy). Natomiast warto się zastanowić, czy na pewno tak chcemy? Ja wolę, żeby na liście wylądowały tylko arkuszy z nawami miast, a u mnie w pliku zaczynają się one od czwartego arkusza. Wyświetlę więc na liście rozwijanej tylko arkusze, które są w kolejności od czwartego w  górę. Do określenia tego potrzebuję więc lekko zmodyfikować powyższy kod, wstawiając tam if-a:

    For Each Ark In Worksheets
        If Ark.Index >= 4 Then
            ArkRob.Cells(Licznik, 1).Value = Ark.Name
            Licznik = Licznik + 1
        End If
    Next

Oczywiście założenie jest taki, że wszystkie te arkusze są arkuszami miast. To już jednak Twoja użytkowniku broszka ;).

OK. Dalej trzeba już tylko określić zakres komórek, które będą źródłem do listy rozwijanej (nasze nazwy arkuszy z miastami):

    Set Zakres = ArkRob.Range("A1").CurrentRegion

A teraz podmienić adres tego zakresu w nazwie:

    ThisWorkbook.Names("Lista_Arkusze").RefersTo = "=" & ArkRob.Name & "!" & Zakres.Address

I voila! Kod będzie pięknie działał. W całości wygląda tak:

Private Sub Worksheet_Activate()
    Dim ArkRob As Worksheet, Ark As Worksheet
    Dim Zakres As Range, Licznik As Long

    Set ArkRob = ThisWorkbook.Sheets("Roboczy")
        ArkRob.Cells.Clear
    
    Licznik = 1
    For Each Ark In Worksheets
        If Ark.Index >= 4 Then
            ArkRob.Cells(Licznik, 1).Value = Ark.Name
            Licznik = Licznik + 1
        End If
    Next

    Set Zakres = ArkRob.Range("A1").CurrentRegion
    
    ThisWorkbook.Names("Lista_Arkusze").RefersTo = "=" & ArkRob.Name & "!" & Zakres.Address
End Sub

Zauważ, że ja dałam warunek, że dla wszystkich arkuszy, których numer jest większy lub równy 4. Możesz natomiast dawać inne warunki, np. że tylko dla widocznych arkuszy (ale pamiętaj, że arkusz główny też jest widoczny!):

    If Ark.Visible = xlSheetVisible Then

albo dla wszystkich oprócz taki, które się nazywają np. „Roboczy”:

    If Ark.name <> "Roboczy" Then

Po prostu podmień odpowiednią linijkę w kodzie. Plik oczywiście zapisz z obsługą makr, czyli *.xlsm lub *.xlsb.

4. Pobieranie sumy sprzedaży

To już będzie prościutkie – w komórce D7 wpisz taką formułę:

=ADR.POŚR(WybranyArkusz&"!G2")

Oczywiście założenie jest takie, że w komórkach G2 arkuszy z miastami są już odpowiednie wartości do wyświetlenia.

Na koniec warto też zaznaczyć, że ponieważ makro wykonuje pewne czynności zaraz po aktywowaniu arkusza, może się okazać, że przestaną nam działać pewne funkcjonalności, jak choćby przeklejanie danych między arkuszami!

 Plik możesz pobrać tutaj:

MalinowyExcel Nazwy arkuszy na liście rozwijanej automatycznie dw.zip

A tutaj możesz obejrzeć tworzenie tego rozwiązania krok po kroku (YB):

Powiązane produkty:

• WEBINAR: Listy rozwijane. Tutaj zobaczysz jeszcze inne opcje wykorzystania list rozwijanych na potrzeby wykresów i nie tylko.
• WEBINAR: Nazwy w Excelu – wstęp. Podczas tego webinaru dowiesz się więcej o tworzeniu i wykorzystywaniu nazw.