Изтриване на стойност в клетка в Excel при промяна на друга клетка

Преди време показах как бързо и лесно може да се направи в Ексел падащо меню (падащ списък), а малко по-късно и как да се направят зависими падащи менюта. В тази статия ще отидем още една стъпка напред и ще покажа как да се преодолее един неприятен бъг, а именно това, че след като сменим стойността в първото падащо меню, тази във второто не се изчиства. Например, ако в първото меню за вида на закуската сме избрали „Пица“, а за Изделие – „Хайдушка“

падащо меню

И след това променим вида на закуската на „Баница“ виждате, че изделието си остава „Хайдушка“.

падащо меню

Оставям настрана факта, че разликата между хайдушка пица и хайдушка баница по подразбиране е минимална. Въпросът е как да постигнем това – при промяна на стойността в първото падащо меню, тази във второто да се изчисти:

падащо меню

Това се постига много лесно със съвсем прост макрос. Дори и да не разбирате нищо от макроси – не се притеснявайте. Ще опитам стъпка по стъпка да обясня какво трябва да направите, за да постигнете този ефект. По този начин ще имате наистина пълноценно падащо меню, защото съгласете се, никак не е трудно да се обърка човек, ако при смяната на Пица с Баница, не се променя и вида на изделието. А това може да има доста неприятни последици.

Създаване на макроса – стъпка по стъпка

  1. Кликнете с десния бутон на мишката върху името на работния лист, в който се намират зависимите падащи менюта. Ще се появи меню, в което трябва да изберете 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 ще свършат работа.

Публикувано в Хитрини. Постоянна връзка.