Czyli jak zrobić, aby wpisać do komórki inną wartość, niż wybraną z listy

Często w przypadków nazw klientów, mamy taki problem, że pełna ich nazwa jest bardzo długa, np. DREWMIRSTO Z.P.H. Paweł Mróz. Gdy wystawiamy fakturę dla takiego klienta, to chcemy, aby wyświetliła się na niej pełna nazwa. Natomiast sami posługujemy się nazwą skróconą, w tym wypadku DREWMIRSTO, i takiej też nazwy chcemy szukać na liście rozwijanej. Problem w tym, że standardowa funkcjonalność Excela wyświetla na liście tę samą wartość, co później wpisuje do komórki. W tym wpisie pokazać, jak tę funkcjonalność można zmienić. Uwaga! Nazwy firm są wymyślone.

Chodzi o coś takiego:

Formatka jest prosta, jak widać powyżej. Cała zabawa rozegra się w źródle listy rozwijanej i oczywiście w kodzie VBA 🙂

Źródło listy rozwijanej

Żeby wszystko się udało – potrzebujemy jakoś powiedzieć Excelowi, że DREWMIRSTO to to samo co DREWMIRSTO Z.P.H. Paweł Mróz. Stworzymy w tym celu prostą tabelę (mam na myśli obiekt tabela) z takimi przypisaniami. Ja ją umieściłam w innym arkuszu, aby go potem można było ukryć. Mój arkusz nazywa się Źródło:

Przypisania nazw klientów

Zdecydowałam się na umieszczenie tego w tabeli, ponieważ chcę, aby źródło listy rozwijanej było dynamiczne. Czyli, jak dopiszę do listy nowego klienta – ma on się pojawić na liście. W tym celu nazwałam kolumnę z klientami jako Lista_Klienci, a listę ze szczegółowymi nazwami Lista_Opisy. O tym już kiedyś pisałam tutaj.

Przygotowania zrobione, teraz kwestia podpięcia listy rozwijanej w formatce. W tym celu zaznacz komórkę C3 (wybierz klienta) i Dane/Poprawność danych. W okienku sprawdzania poprawności natomiast wybierz następujące opcje:

Tworzenie listy rozwijanej

Wybrałam listę z klientami, czyli krótkimi nazwami, ponieważ te dane użytkownik ma widzieć na liście.

Ok, to teraz ta trudniejsza część – makro 😉

Kod VBA

Cała magia ma się stać, kiedy user wybierze klienta z listy rozwijanej. Wybierze jego krótką nazwę, która zaraz potem ma zostać skasowana, a na jej miejsce ma zostać wstawiona odpowiednia długa nazwa. Do dzieła!

Aby w ogóle Excel zareagował na zmianę wartości w komórce, należy oprogramować takie własnie zdarzenie, czyli Change (tutaj opisałam jak to zrobić lub zobacz film na końcu artykułu). Pamiętajmy, że zdarzenie to działa dla każdej komórki w arkuszu, więc musimy się zabezpieczyć, aby działało tylko dla tych, które mają sprawdzanie poprawności i to jeszcze typu lista (type = 3). Dopiero w takim przypadku nasze makro powinno zacząć cokolwiek robić. A to już będzie proste:

1. wyłączy zdarzenia
2. sprawdzi pozycję wybranego klienta
3. zapamięta długi opis tego klienta
4. wstawi ten długi opis do komórki
5. włączy zdarzenia

Miejmy jednak świadomość, że taki kod zadziała na każdą listę, nie tylko na tę z klientami. Ma to zarówno dobre jak i złe strony – wszystko jak zwykle zależy od sytuacji :).

Najpierw deklaracja wszystkich potrzebnych zmiennych (najczęściej piszemy ją sukcesywnie, ale gdybym to robiła w tym opisie, to chyba byście oszaleli ;)) i od razu – obsługa błędów just in case:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ListaKlienci As Range, ListaOpisy As Range
    Dim Opis As String, Pozycja As Long
    
    On Error GoTo Obsluga
    
Obsluga:
    Application.EnableEvents = True
End Sub

W powyższym: mówimy, że jeśli jakikolwiek błąd wystąpi, to makro ma najpierw przywrócić obsługę błędów (za chwilę ją wyłączymy), a potem zakończyć działanie.

Teraz kwestia warunku, że komórka musi zawierać sprawdzanie poprawności w postaci listy rozwijanej:

    If Target.Validation.Type = 3 Then

    End If

Gdy ten warunek jest spełniony – będziemy działać, jeśli nie – po prostu idziemy dalej, czyli kończymy działanie makra.

Teraz wyłączymy zdarzenia, ponieważ naszym celem jest skasowanie wartości komórki, a potem wpisanie do niej innej wartości. Czyli de facto kolejna zmiana wartości komórki, a na takie zdarzenie ma się uruchomić nasze makro, czyli mielibyśmy pętlę, a tego nie chcemy:

Application.EnableEvents = False

Teraz określamy zmienne obiektowe list klientów i opisów, aby kod był czytelniejszy, a makro szybsze:

        With Sheets("Źródło")
            Set ListaKlienci = .Range("Lista_Klienci")
            Set ListaOpisy = .Range("Lista_Opisy")
        End With

Ustalmy teraz pozycję klienta na liście – skorzystam tutaj z funkcji arkusza PODAJ.POZYCJĘ, czyli po angielsku MATCH. Zwraca ona pozycję wartości na liście. Można to zrobić na milion różnych sposobów, ale mi się podoba ten poniżej. Od razu przypiszemy  znalezioną długą nazwę klienta do zmiennej Opis i wpiszemy go do zmienianej przez użytkownika komórki (Target):

        Pozycja = WorksheetFunction.Match(Target.Value, ListaKlienci, 0)
        Opis = ListaOpisy.Cells(Pozycja, 1).Value
        
        Target.Value = Opis

Całość wygląda tak:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ListaKlienci As Range, ListaOpisy As Range
    Dim Opis As String, Pozycja As Long
    
    On Error GoTo Obsluga
    
    If Target.Validation.Type = 3 Then
        Application.EnableEvents = False
        With Sheets("Źródło")
            Set ListaKlienci = .Range("Lista_Klienci")
            Set ListaOpisy = .Range("Lista_Opisy")
        End With
        
        Pozycja = WorksheetFunction.Match(Target.Value, ListaKlienci, 0)
        Opis = ListaOpisy.Cells(Pozycja, 1).Value
        
        Target.Value = Opis
    End If
Obsluga:
    Application.EnableEvents = True
End Sub

Z kodowania to tyl, ważne jest jeszcze odpowiednie zapisanie pliku, ale to mam nadzieję, że już wiecie. Trzeba zapisać plik jako plik obsługujący makra, czyli *.xlsm lub *.xlsb.

Po wszystkim dostajemy taki efekt:

Wynik

A tutaj wersja wideo wpisu:

Enjoy!