Worksheet change vba excel не работает

Обновлено: 08.01.2025

Как я могу автоматически запускать макрос Excel каждый раз, когда изменяется значение в определенной ячейке?

Прямо сейчас мой рабочий код:

где "H5" - конкретная отслеживаемая ячейка и Macro имя макроса.

Есть ли способ лучше?

Ваш код выглядит неплохо.

Однако будьте осторожны, потому что ваш вызов Range("H5") - это команда быстрого доступа Application.Range("H5") , которая эквивалентна Application.ActiveSheet.Range("H5") . Это может быть хорошо, если единственными изменениями являются изменения пользователя, что является наиболее типичным, но значения ячеек рабочего листа могут измениться, когда он не является активным, посредством программных изменений, например VBA.

Имея это в виду, я бы использовал Target.Worksheet.Range("H5") :

Или вы можете использовать Me.Range("H5") , если обработчик событий находится на кодовой странице для рассматриваемого рабочего листа (обычно это так):

Надеюсь это поможет.

что, если ячейка H5 изменена с другого листа, допустим, указанная sheet2 выше функция не работает. Помогите плз в этом. Application.ActiveSheet.Range ("H5"). ==> target.parent.range ("H5") еще безопаснее @WillEdiger Всякий раз, когда вы явно не указываете ссылку на лист, Excel предполагает, ActiveSheet и всякий раз, когда вы явно не указываете, что вы работаете с Excel, предполагает Excel Application . Следует отметить, что в модуле рабочего листа кода (который где Worksheet_Change событие должно быть расположено), неквалифицированный Range вовсе не по умолчанию , ActiveSheet но вместо этого относится к листу , содержащий код. Таким образом, код в этом ответе фактически совпадает с кодом в вопросе. (Примечание: еще в 2009 году, когда был написан этот ответ, он мог быть другим, но я почти уверен, что это не так.)

Обработайте Worksheet_Change событие или Workbook_SheetChange событие.

Обработчики событий принимают аргумент «Target As Range», поэтому вы можете проверить, включает ли изменяемый диапазон интересующую вас ячейку.

Спасибо, это работает. Я проверяю диапазон, скажем, с Target.Address = Range("H5").Address . Есть способ попроще? Альтернатива: Not (Intersect(Target, Range("H5")) Is Nothing) . Как бы вы это сделали? Первый комментарий ( Target.Address = Range("H5").Address ) не сработал бы, если бы ваша ячейка была только частью измененного диапазона. Второй комментарий по-прежнему страдает проблемами, описанными Майком Розенблюмом.

Я потратил много времени на изучение этого и изучение того, как все это работает, после того, как действительно испортил триггеры событий. Поскольку было так много разрозненной информации, я решил поделиться тем, что я нашел для работы, в одном месте, шаг за шагом следующим образом:

1) Откройте редактор VBA, в разделе VBA Project (YourWorkBookName.xlsm) откройте объект Microsoft Excel и выберите лист, к которому будет относиться событие изменения.

2) Вид кода по умолчанию - «Общее». В раскрывающемся списке вверху по центру выберите «Рабочий лист».

3) Private Sub Worksheet_SelectionChange уже там, как и должно быть, оставьте его в покое. Скопируйте / вставьте приведенный выше код Майка Розенблюма и измените ссылку .Range на ячейку, для которой вы наблюдаете изменение (B3, в моем случае). Однако пока не размещайте свой макрос (я убрал слово «Макрос» после «Тогда»):

или из раскрывающегося списка в левом верхнем углу выберите «Изменить» и в пространство между частным подпиской и конечной подпиской вставьте If Not Intersect(Target, Me.Range("H5")) Is Nothing Then

4) В строке после «Then» отключите события, чтобы при вызове макроса он не запускал события и не пытался запустить этот Worksheet_Change снова в бесконечном цикле, который приводит к сбою Excel и / или иным образом все портит:

5) Назовите свой макрос

6) Включите события, чтобы сработало следующее изменение (и любые / все другие события):

7) Завершите блок If и Sub:

Это позволяет включать / выключать события из модулей, что создает проблемы, и просто позволяет запускать изменение, отключает события, запускает ваш макрос и снова включает события.

[/vba]
И, кстати, да, как ниже уже написали, нужно прервать обработку событий и потом ее взад поставить
[vba] [/vba] Автор - _Boroda_
Дата добавления - 06.09.2016 в 10:43 у тебя зацикливание получается
процедурой
Sheets("Форма").Range("B16:O18") = Sheets("Сбор16").Range("A" & поз_16 & ":N" & поз_16 + 2)
ты вносишь изм. в лист и заново инициируешь Worksheets_Change
[moder]Нарушение п.3 Правил форума в части тегов. Замечание.[/moder] у тебя зацикливание получается
процедурой
Sheets("Форма").Range("B16:O18") = Sheets("Сбор16").Range("A" & поз_16 & ":N" & поз_16 + 2)
ты вносишь изм. в лист и заново инициируешь Worksheets_Change
[moder]Нарушение п.3 Правил форума в части тегов. Замечание.[/moder] K-SerJC

pabchek, добрый день

[p.s.] пока отвечал уже дали ответы ))))

pabchek, добрый день

[p.s.] пока отвечал уже дали ответы )))) devilkurs

[p.s.] пока отвечал уже дали ответы )))) Автор - devilkurs
Дата добавления - 06.09.2016 в 10:53

Еще, как вариант - можно использовать переменную как флаг: возводить и снимать.
Иногда так лучше чем события включать-отключать .
[vba] [/vba]
Забыл поставить снятие флага - правильнее так:
[vba] Еще, как вариант - можно использовать переменную как флаг: возводить и снимать.
Иногда так лучше чем события включать-отключать .
[vba] [/vba]
Забыл поставить снятие флага - правильнее так:
[vba]

Иногда все проще чем кажется с первого взгляда.

[/vba]
Забыл поставить снятие флага - правильнее так:
[vba] [/vba] Автор - SLAVICK
Дата добавления - 06.09.2016 в 10:58
вон оно где собака порылась))
Спасибо всем большое! Автор - pabchek
Дата добавления - 06.09.2016 в 10:59 чем прямое присвоение? Автор - pabchek
Дата добавления - 06.09.2016 в 11:06 SLAVICK, прошу прощения, но при данном флаге
[vba] [/vba]
получается, что при последующих изменениях пользователем ячеек событие Change отбивает.
Т.е. где-то надо дополнительно флаг переводить в True
Я правильно понимаю? SLAVICK, прошу прощения, но при данном флаге
[vba] [/vba]
получается, что при последующих изменениях пользователем ячеек событие Change отбивает.
Т.е. где-то надо дополнительно флаг переводить в True
Я правильно понимаю? devilkurs [/vba]
получается, что при последующих изменениях пользователем ячеек событие Change отбивает.
Т.е. где-то надо дополнительно флаг переводить в True
Я правильно понимаю? Автор - devilkurs
Дата добавления - 06.09.2016 в 11:07
Код - читабельнее и проще для восприятия . как следствие - уменьшение возможности возникновения ошибок и ускорение внесения изменений в код.

Код - читабельнее и проще для восприятия . как следствие - уменьшение возможности возникновения ошибок и ускорение внесения изменений в код.

Код - читабельнее и проще для восприятия . как следствие - уменьшение возможности возникновения ошибок и ускорение внесения изменений в код.

я уже исправил код выше. . Автор - SLAVICK
Дата добавления - 06.09.2016 в 11:10
Совершенно верно. Проверил на своем примере - тоже циклит.
[p.s.]Пока писал, уже исправили
Совершенно верно. Проверил на своем примере - тоже циклит.
[p.s.]Пока писал, уже исправили pabchek

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

Совершенно верно. Проверил на своем примере - тоже циклит.
[p.s.]Пока писал, уже исправили Автор - pabchek
Дата добавления - 06.09.2016 в 11:11

Собака не там порылась на самом деле, а в
.Value
Убери его из кода и у тебя ничего не получится

Добавлю к написанному выше Славиком - обращение к переменной происходит только один раз, а не 2. Если это один раз, то разница во времени ничтожна, а вот если в большом цикле .

Собака не там порылась на самом деле, а в
.Value
Убери его из кода и у тебя ничего не получится

Добавлю к написанному выше Славиком - обращение к переменной происходит только один раз, а не 2. Если это один раз, то разница во времени ничтожна, а вот если в большом цикле . _Boroda_

Собака не там порылась на самом деле, а в
.Value
Убери его из кода и у тебя ничего не получится

Добавлю к написанному выше Славиком - обращение к переменной происходит только один раз, а не 2. Если это один раз, то разница во времени ничтожна, а вот если в большом цикле . Автор - _Boroda_
Дата добавления - 06.09.2016 в 11:13

К сожалению, оказалось, что это критично.
Если выбрать значение из фильтра сводной - цикл

[p.s.]И, к слову сказать, если не использовать сводную, то работает и без
[vba]

Application.EnableEvents = 0
***
Application.EnableEvents = 1

К сожалению, оказалось, что это критично.
Если выбрать значение из фильтра сводной - цикл

[p.s.]И, к слову сказать, если не использовать сводную, то работает и без
[vba]

Application.EnableEvents = 0
***
Application.EnableEvents = 1

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

К сожалению, оказалось, что это критично.
Если выбрать значение из фильтра сводной - цикл

[p.s.]И, к слову сказать, если не использовать сводную, то работает и без
[vba]

Application.EnableEvents = 0
***
Application.EnableEvents = 1 [/vba] Автор - pabchek
Дата добавления - 06.09.2016 в 11:28 И, к слову сказать, если не использовать сводную, то работает и без
Это необходимо для ускорения работы макроса, т.к. при вставке на лист значений повторно инициируется выполнение события Change. Отключив реакцию событий Change выполняется один раз.
А про сводную к сожалению не могу подсказать ничего. И, к слову сказать, если не использовать сводную, то работает и без
Это необходимо для ускорения работы макроса, т.к. при вставке на лист значений повторно инициируется выполнение события Change. Отключив реакцию событий Change выполняется один раз.
А про сводную к сожалению не могу подсказать ничего. devilkurs И, к слову сказать, если не использовать сводную, то работает и без
Это необходимо для ускорения работы макроса, т.к. при вставке на лист значений повторно инициируется выполнение события Change. Отключив реакцию событий Change выполняется один раз.
А про сводную к сожалению не могу подсказать ничего. Автор - devilkurs
Дата добавления - 06.09.2016 в 11:49
Цикла как такового нет, но подвисает знатно. Причину пока не понял.
Да, работает, но попробуй пройтись в пошаговом режиме (через F8) и сам увидишь.
Цикла как такового нет, но подвисает знатно. Причину пока не понял.
Да, работает, но попробуй пройтись в пошаговом режиме (через F8) и сам увидишь. _Boroda_
Цикла как такового нет, но подвисает знатно. Причину пока не понял.
Да, работает, но попробуй пройтись в пошаговом режиме (через F8) и сам увидишь. Автор - _Boroda_
Дата добавления - 06.09.2016 в 12:00

"Учиться, учиться и еще раз учиться!"
WM: R399923528092

[/vba]
Вешает Автор - pabchek
Дата добавления - 06.09.2016 в 12:49 [/vba]то все нормально работает Автор - Manyasha
Дата добавления - 06.09.2016 в 12:50
Истинно так. Спасибо, Марина!
А есть мысли почему? Автор - pabchek
Дата добавления - 06.09.2016 в 13:31

Нет, видимо глюк такой.
Сама засела за поиск информации, интересно же))

Экспериментирую с макросом без возврата обновления экрана:
отфильтровала сводную - все зависло. Перехожу в редактор VBA, в immediate пишу ?Application.ScreenUpdating, нажимаю enter, выдает False, после чего, обновление само включается и при следующем запросе в immediate, оно уже True. Непонятненько

Нет, видимо глюк такой.
Сама засела за поиск информации, интересно же))

зы ну так макрос - то отрабатывает.
Просто экран не включается обратно - у мну так часто бывает при отладке кода.
Забыл включить экран обратно - получи такой эффект.
Поэтому я сделал себе на панели закладок кнопку для включения всех опций обратно. и если такое случилось то делаю следующее:

Win D (чтобы свернуть все окна и показать раб. стол)
Потом кликаю мышем по экселю.
Потом нажимаю кнопку запуска макроса .
Ну или второй вариант - как Марина сделала написать в редакторе новый макрос(или найти уже готовый)
[vba]

Всех от души с Новым годом!

У меня такой вопрос (проблема) возник по ходу разработки макроса под Excel.

Дело в следующем. На одном из листов моей рабочей книги имеется ячейка, в которую транслируется значение из ячейки с другого листа. Когда значение в этой ячейке изменяется, программа должна проанализировать изменение и выполнить определённые действия.

Разумеется, я решил воспользоваться событием Worksheet_Change в Коде изменяемого листа. Но не тут-то было! Оказалось, что если этот лист не активен, то макрос никак не реагирует на это событие. Реакцию вызывает только НЕПОСРЕДСТВЕННОЕ изменение значения ячейки в АКТИВНОМ листе!

Подскажите, пожалуйста, можно как-нибудь обойти эту проблему? Мне бы хотелось вообще не заглядывать на тот лист, и чтобы там всё происходило автоматически, без моего участия.

Если я путано объяснил суть своей проблемы, то попробую сейчас показать её на простом примере. Если вы мне подскажете, как надо организовать работу программы в этом примере, то дальше я уже самостоятельно соображу.

Итак, имеются два листа: Лист1 и Лист2.
На Лист2 в ячейку А1 вписана формула "=Лист1!A1". Таким образом, когда мы изменяем значение в ячейке А1 первого листа, автоматически изменяется и значение в ячейке А1 второго листа.

Теперь в Коде второго листа вписываем вот такую процедуру:

Private Sub Worksheet_Change(ByVal Target As Range)
MsgBox (Target)
End Sub

Как заставить программу реагировать на изменение ячеек на НЕАКТИВНОМ в данный момент листе?

'в коде листа, на котором происходят непосредственные изменения (Лист1):
Private Sub Worksheet_Change( ByVal Target As Range)
MsgBox (Sheets( "Лист2" ).Cells(1,1).value)
End Sub

Smith пишет:
Или написать сторожа, правда цикл не есть гуд

Я совсем не опытный программист на VBA, поэтому что такое "сторож" не знаю. Но, на самом деле, хотел бы узнать! Я много программировал (непрофессионально, для своих нужд) на VB5, а в VBA, можно сказать, делаю свои первые шаги

hip пишет:
А сделать все необходимое в коде первого листа нельзя разве? Только переменной Target не удастся воспользоваться в этом случае - в ней будет диапазон с первого листа.

'в коде листа, на котором происходят непосредственные изменения (Лист1):
Private Sub Worksheet_Change(ByVal Target As Range)

Да в том-то и проблема, что я постоянно перемещаюсь по всем листам и на всех листах происходят ПОСТОЯННЫЕ быстрые изменения (получение информации из внешних источников, подсчёт, построение диаграмм). А ту ячейку, изменения в которой для меня особенно важны, я специально "вынес" на отдельный лист, чтобы там могла изменяться только она одна! (Чтобы не отфильтровывать "лишних" изменений!) Очень уж я рассчитывал на событие Worksheet_Change, наивно думал, что прога должна реагировать на изменения, даже когда лист не активен.

На данный момент, для меня этот совет звучит самым заманчивым из всех. Сейчас буду экспериментировать. Потом отпишусь!

Хотя сразу вижу большой минус этой затеи в том, что много ресурсов компа будет уходить на реакцию (с последующей отфильтровкой) на ВСЕ изменения, которых в моей книге огромное количество. Боюсь, что тогда комп будет сильно тормозить. Ведь я же для того и собирался транслировать интересующее меня значение в ЕДИНСТВЕННУЮ изменяемую ячейку на ОТДЕЛЬНОМ листе, чтобы избежать затрат ресурсов на лишнюю фильтрацию. Макросы ведь и без того не слишком быстро работают, сами знаете.

На этом месте хотелось бы поподробнее
В смысле, что такое "лист сразу отбить на входе в обработку", и как это сделать практически?

Предположим, Вы скачали файл с данного сайта с примером макроса, открываете файл, но макросы в нем не работают! Как такое может быть? Неужели автор сайта выложил неработающий пример и не знает об этом? И тем более как может случиться такое, что Вы перенесли со своего ПК на другой ПК файл с рабочими макросами на флешке, запускаете, а он не работает. Почему? Вроде все правильно делаете, да ведь и у Вас работает на ПК. И начинает закрадываться мысль: "А чем же этот ПК и Excel на нем такие особенные?" . А ответ может быть прост - перед выполнением макроса не было разрешено выполнение этих самых макросов. Да, в большинстве случаев по умолчанию выполнение макросов в Excel отключено. И их надо включить. Для этого:

Excel 2003:
Сервис-Безопасность-Уровень макросов "Низкий"
Excel 2007:
Кнопка Офис-Параметры Excel (Excel Options) -Центр управления безопасностью (Trust Centr) -Параметры центра управления безопасностью (Trust Centr Settings) -Параметры макросов (Macro Settings) -Разрешить все макросы (Enable All Macros)
Excel 2010-2016:
Файл (File) -Параметры (Options) -Центр управления безопасностью (Trust Centr) -Параметры центра управления безопасностью (Trust Centr Settings) -Параметры макросов (Macro Settings) -Разрешить все макросы (Enable All Macros)

Если макросы уже включены, но некоторые макросы отказываются работать (как правило те, которые записаны в модулях листов и книг и запускаются при наступлении определенного события - Worksheet_SelectionChange (выделение ячеек), Worksheet_Change (изменение значений ячеек) и т.п.), то скорее всего когда-то вы выполнили какой-нибудь макрос и он отключил отслеживание событий. Тогда надо в любую активную книгу добавить модуль(переходим в редактор VBA( Alt + F11 ) -Insert -Module), вставить в него приведенный ниже код и запустить:

Sub Reset_Events() Application.EnableEvents = True End Sub

чтобы запустить(выполнить) данный макрос макрос необходимо установить курсор в любое место между Sub и End Sub и нажать клавишу F5 .

Читайте также: