На листе Кэрол имеет ячейку, отформатированную как «Бухгалтерский учет». Если кто-то случайно вводит дату (мм / дд / гг) в эту ячейку, Excel автоматически изменяет форматирование ячейки для правильного отображения даты. Однако, если она попытается снова ввести сумму в долларах в эту ячейку, она не вернется к формату «Бухгалтерский учет»; в ячейке сохраняется формат даты.

Это нормально, если пользователь видит ошибку и исправляет ее, но часто это происходит в шаблоне с «стандартным» текстом, и этот шаблон защищен без доступа к форматированию ячеек. Кэрол задается вопросом, есть ли у кого-нибудь идеи, почему это происходит и как это исправить.

Если вам нужен быстрый способ предотвратить изменение форматирования на простых листах, выполните следующие действия:

  1. Откройте диалоговое окно «Параметры Excel». (В Excel 2007 нажмите кнопку «Office», а затем выберите «Параметры Excel. В Excel 2010 или более поздней версии откройте вкладку« Файл »на ленте, а затем нажмите« Параметры ».)

  2. В левой части диалогового окна нажмите «Дополнительно».

  3. Прокрутите до самого низа расширенных параметров. (См. Рис. 1.)

  4. Убедитесь, что выбран параметр «Ввод формулы перехода».

  5. Щелкните ОК.

Эта конкретная опция заставляет Excel оценивать (анализировать) введенную информацию так же, как это делал Lotus 1-2-3. Это означает, что даты больше не анализируются как даты, а как формула. Таким образом, если кто-то вводит 11-16-13 в ячейку, то он анализируется как «одиннадцать минус шестнадцать минус тринадцать» и отображается в ячейке как -18. Поскольку он не был проанализирован как дата, формат учета остается связанным с ячейкой по желанию.

Однако у этого подхода есть недостатки. Поскольку Excel впоследствии анализирует любые записи в соответствии с правилами Lotus, ваши пользователи могут сделать вывод, что ваш рабочий лист не работает должным образом, поскольку он не соответствует тем же правилам, что и другие рабочие листы Excel. Вот почему я упомянул, что этот подход может быть приемлемым для простых листов; вам нужно будет определить, соответствует ли ваш лист требованиям.

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

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range     Set rngToCheck = Range("E2")

If Intersect(Target, rngToCheck) Then         rngToCheck.NumberFormat = _           "_($ #,##0.00_);_($ (#,##0.00);_($* "" - ""??_);_(@_)"

End If End Sub

В этом примере ячейка, в которой вы хотите сохранить формат учета, — это E2, как присвоено переменной rngToCheck. Если вы хотите принудительно применить формат к другому диапазону ячеек, просто измените строку назначения.

Если вам нужно немного больше гибкости, вы можете использовать другой набор обработчиков событий. Например, в следующих примерах используются события SelectionChange и Change объекта Worksheet. Они приводят к чему-то, что не сильно влияет на конкретный формат, но предотвращает изменение форматирования ячейки по сравнению с тем, что было раньше. Таким образом, этот подход защищает любое форматирование, а не только соблюдение формата учета.

Dim nFormat As String

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCheck As Range     Set rngToCheck = Range("E2")

If Intersect(Target, rngToCheck) Then         rngToCheck.NumberFormat = nFormat     End If End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

nFormat = Target.NumberFormat End Sub

Обработчик события SelectionChange запускается первым, устанавливая существующий формат в переменную nFormat. Затем запускается обработчик события Change и устанавливает исходное форматирование.

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

Выполните следующие общие шаги:

  1. Выделите ячейку или ячейки, форматирование которых вы хотите применить.

  2. Откройте вкладку «Данные» на ленте.

  3. Щелкните инструмент Проверка данных в группе Инструменты для работы с данными. Excel отображает диалоговое окно «Проверка данных».

  4. В раскрывающемся списке Разрешить выберите Пользовательский. (См. Рис. 2.)

  5. В поле «Формула» введите следующее: = CELL («формат», B2) = «C2». Установите любые другие параметры проверки данных по желанию.

  6. Щелкните ОК.

Формула (шаг 5) проверяет форматирование ячейки и разрешает или запрещает ввод в зависимости от этого форматирования. В приведенной формуле формат C2 — это внутреннее имя формата учета. Вы можете легко изменить коды в формуле на другой формат, такой как «, 2», «C2», «C0», «C2-» или «C0-» в зависимости от ваших предпочтений. Самый простой способ выяснить, какой формат следует использовать, — это отформатировать ячейку по своему усмотрению перед применением правила проверки данных. (Например, допустим, вы применяете форматирование к ячейке L13.) Затем вы можете использовать эту формулу в другой ячейке, чтобы узнать, какой формат, по мнению Excel, вы применили:

=CELL("format",L13)

_Примечание: _

Если вы хотите узнать, как использовать макросы, описанные на этой странице (или на любой другой странице на сайтах ExcelTips), я подготовил специальную страницу, содержащую полезную информацию.

link: / excelribbon-ExcelTipsMacros [Щелкните здесь, чтобы открыть эту специальную страницу в новой вкладке браузера].

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (12729) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365.