Преди време показах как бързо и лесно може да се направи в Ексел падащо меню (падащ списък), а малко по-късно и как да се направят зависими падащи менюта. В тази статия ще отидем още една стъпка напред и ще покажа как да се преодолее един неприятен бъг, а именно това, че след като сменим стойността в първото падащо меню, тази във второто не се изчиства. Например, ако в първото меню за вида на закуската сме избрали „Пица“, а за Изделие – „Хайдушка“
И след това променим вида на закуската на „Баница“ виждате, че изделието си остава „Хайдушка“.
Оставям настрана факта, че разликата между хайдушка пица и хайдушка баница по подразбиране е минимална. Въпросът е как да постигнем това – при промяна на стойността в първото падащо меню, тази във второто да се изчисти:
Това се постига много лесно със съвсем прост макрос. Дори и да не разбирате нищо от макроси – не се притеснявайте. Ще опитам стъпка по стъпка да обясня какво трябва да направите, за да постигнете този ефект. По този начин ще имате наистина пълноценно падащо меню, защото съгласете се, никак не е трудно да се обърка човек, ако при смяната на Пица с Баница, не се променя и вида на изделието. А това може да има доста неприятни последици.
Създаване на макроса – стъпка по стъпка
- Кликнете с десния бутон на мишката върху името на работния лист, в който се намират зависимите падащи менюта. Ще се появи меню, в което трябва да изберете View Code.
2. Това отваря Visual Basic редактора направо на едно много специално място – там, където се определят събитията, които се случват в точно този работен лист. Променете стойността в лявото падащо меню (навсякъде са тези падащи менюта, ей) от General на Worksheet
3. В дясното падащо меню изберете Change
Това автоматично създава следния код
На прост български този код казва, че макросът ще се задейства при промяна на стойността на поне една клетка в работния лист. Клетката, в която се случва тази промяна, е наречена Target (технически погледнато Target може да бъде съвкупност от клетки, но да не усложняваме нещата)
4. Всичко, което трябва да направите е да въведете този код между двата автоматично създадени реда
Как работи макросът?
За тези, които не се задоволяват, че нещо работи, а искат да знаят как точно работи, нека да „разчепкаме“ тези три реда
If Not Intersect(Target, Range(„B2“)) Is Nothing Then
Казахме, че макросът се задейства, когато в някаква област от работния лист (наречена Target) има промяна в стойността на клетките. Първият ред проверява дали областта Target не съвпада с някаква друга област (Range). В нашия случай Range е клетката В2 , където е първото падащо меню.
Обърнете внимание, че тук логиката е малко обърната – не се проверява дали Target съвпада с Range, а дали не е невярно това, че Target и Range не съвпадат. Защо така са решили да го направят в Майкрософт – не зная.
По-важното е, че ако клетката в работния лист, в която има промяна е точно клетка В2, тогава макросът изпълнява втория ред:
Range(„D2“).ClearContents
Тоест, изчиства / изтрива стойността на клетка D2, в която е второто падащо меню.
Ако се промени някоя друга клетка, обаче – нищо не се случва.
Накрая End If затваря цикъла на логическата проверка (основен принцип във VBA – щом сме започнали с IF, трябва да завършим с End If)
Ако все пак логиката на програмирането във VBA ви идва в повече, не се притеснявайте. Просто копирайте кода и заменете В2 с адреса на клетката, в която е вашето първо меню, а D2 – с адреса на клетката, в която е вашето второ меню
If Not Intersect(Target, Range(„B2„)) Is Nothing Then
Range(„D2„).ClearContents
End If
А за много клетки със зависими падащи менюта?
В практиката обаче е възможно да имаме падащи менюта на няколко реда ето така:
Например, ако използваме Ексел за създаване на поръчки или фактури.
Тогава макросът трябва да се преработи малко, за да може да работи на всеки един от редовете. Стъпки 1-4 по-горе остават същите, но кодът, който трябва да въведете е следният:
Тук просто трябва да подмените оцветеното в червено – на първия ред вместо В2:В12 да напишете областта във вашия работен лист, в която са клетките с първото падащо меню. На втория ред вместо D трябва да напишете в коя колона са клетките с второто падащо меню.
If Not Intersect(Target, Range(„B2:B12„)) Is Nothing Then
Range(„D“ & Target.Row).ClearContents
Ако искате да разберете в детайли как работи макросът и за метода Intersect може да прочетете тук
Разбира се, този трик може да се използва и за всякакви други случаи, при които стойността на една клетка се променя. Не е задължително това да става с падащо меню. Може да е с формула или ръчно.
И не забравяйте да запазите файла във формат, който поддържа макроси – xlsm е препоръчителният, но и xlsb и старият xls ще свършат работа.