W arkuszach jak i na formularzach możemy wykorzystywać ten sam typ kontrolek ActiveX. Niewiele osób korzysta z tej pierwszej możliwości. W tym tekście pokażę jak kreatywnie wykorzystać formant ComboBox. Będzie do niego wgrana niepełna lista arkuszy z pliku.
Kontrolki osadzone w arkuszu
W życiu codziennym często tworzy się proste arkusze, które służą do obliczeń, ale nie wymagają korzystania z kontrolek ani makr. Programiści VBA, z kolei bazują na formularzach typu UserForm, które dają znacznie większe możliwości interakcji z użytkownikiem.
Zastosowanie kontrolek w arkuszu jest wg mnie czymś pośrednim. Przyznam też, że niektóre pliki potrafią mnie mile zaskoczyć swoją estetyką i funkcjonalnością. Mimo, że nie zawierają formularzy UserForm, to sprawiają profesjonalne wrażenie.
W dzisiejszej notce skupię się na kontrolce Combobox z grupy ActiveX.
Ten typ kontrolek różni się nieco od kontrolek formularza. Na ten temat napisano już bardzo wiele i w sieci można znaleźć merytoryczne artykuły opisujące te różnice.
Aktualizator cen
Załóżmy, że naszą intencją jest stworzenie pewnego aktualizatora cen dla podzespołów komputerowych.
Produkty znajdują się w zakładkach z nazwą grupy towarowej. Dla ułatwienia wyróżniłem je na czarno. Naszym celem jest wstawienie ich nazw do Combobox1.
Oprócz tych wartości, chcę również umieścić na pierwszej pozycji słowo Wszystkie. Ma ono sugerować, że chcemy zaktualizować ceny dla wszystkich grup towarowych.
Kod VBA
Poniżej dwie procedury. Pierwsza typu Function. Druga typu Sub.
Pierwsza procedura pobiera do tablicy listę arkuszy z czarnym kolorem zakładki.
Druga procedura to makro, które kasuje istniejącą listę, ładuje do niej nową i ustawia domyślnie pierwszą wartość.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 |
Public Function ListaArkuszy(ByVal iKolor As Integer) As Variant ' Funkcja pobiera do tablicy unikatową listę arkuszy wyróżnionych kolorem Dim wksArkusz As Worksheet ' Pojedynczy obiekt arkusza Dim avLista() As Variant ' Tymczasowa lista arkuszy Dim iLicznik As Integer ' Licznik 'Przejedź w pętli po wszystkich arkuszach 1 For Each wksArkusz In ThisWorkbook.Worksheets 'Sprawdź czy zakładka ma czarny kolor 2 If wksArkusz.Tab.ColorIndex = iKolor Then 3 iLicznik = iLicznik + 1 'Zwiększ licznik 4 ReDim Preserve avLista(1 To iLicznik) 'Zwiększ wymiar tablicy 5 avLista(iLicznik) = wksArkusz.Name 'Pobierz nazwę arkusza 6 End If '// If wksArkusz.Tab.ColorIndex = 1 Then 7 Next wksArkusz '// For Each wksArkusz In ThisWorkbook.Worksheets 'Zaczytaj wynik do tablicy 8 ListaArkuszy = avLista End Function |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 |
Public Sub UzupelnijComboboxa() ' Procedura uzupełnia przycisk Combobox1. ' Źródłem jest lista czarnych arkuszy + opcja "Wszystkie". Dim avArkusze As Variant 'Lista "czarnych" arkuszy - tylko grupy towarowe Dim sGrupaTow As String 'Nazwa grupy towarowej Dim x As Integer 'Licznik 'Przypisz do zmiennej wynik funkcji - tylko czarne arkusze 1 avArkusze = ListaArkuszy(1) 'Wyczyść dane z Combobox1 2 wksAktualizator.ComboBox1.Clear 'Dodaj opcję "Wszystkie" 3 wksAktualizator.ComboBox1.AddItem "Wszystkie" 'Dodaj w pętli nazwy każde grupy towarowej 4 For x = LBound(avArkusze, 1) To UBound(avArkusze, 1) 'Zaczytaj do zmiennej nazwę grupy towarowej 5 sGrupaTow = avArkusze(x) 'Dodaj nazwę grupy do Comboboxa 6 wksAktualizator.ComboBox1.AddItem sGrupaTow 7 Next x '// For x = LBound(avArkusze, 1) To UBound(avArkusze, 1) 'Ustaw domyślnie opcję "Wszystkie" 8 wksAktualizator.ComboBox1.ListIndex = 0 End Sub |
Screen
Jak widać, do pola kombi została wgrana lista arkuszy z nazwami podzespołów komputerowych. Wcześniej jednak dodana została pozycja Wszystkie.
