Lost your password?


Хитрости »
Основные понятия (27)
Сводные таблицы и анализ данных (10)
Графики и диаграммы (5)
Работа с VB проектом (12)
Power BI и Power Query (23)
Условное форматирование (5)
Списки и диапазоны (5)
Макросы(VBA процедуры) (68)
Разное (43)
Баги и глюки Excel (5)

Всплывающая подсказка в ячейке

Сегодня хочу поделиться одним маленьким приемом, который поможет сделать удобные и красивые подсказки к ячейкам:
Всплывающая подсказка в ячейке
На создание такой подсказки меня навел однажды вопрос на одном из форумов - как можно сделать удобный перевод текста из ячейки, чтобы он показывался при наведении на ячейку мышью? Не выделении - именно наведении. Можно использовать примечания(вкладка Рецензирование -Создать примечание). Но в случае с примечаниями есть несколько не очень удобных моментов: размеры примечания придется подгонять для каждой подсказки отдельно; примечания как правило будут показываться справа от ячейки, а не чуть ниже; при большом количестве примечаний файл может значительно увеличиться в размерах и некоторые возможности будут мешать их корректному отображению(например, если закрепить области, то примечание может и съехать и "срезаться"). Первое, что пришло в голову - а что если сделать как в гиперссылках (Что такое гиперссылка?)? Если точнее - что если именно эти подсказки использовать? Но как же тогда делать гиперссылку? Куда? Все просто - я решил, что можно создать гиперссылку на ячейку, в которой сама гиперссылка. Тогда даже при нажатии на ячейку с гиперссылкой нас никуда не перекинет. Я покажу как это можно сделать вручную и как это можно сделать при помощи кода VBA.
Для начала немного об исходных данных. У нас есть лист с ячейками, в которых надо создать подсказки и есть лист "справочник", в котором указано для каких значений какие должны быть подсказки.


РУЧНОЕ СОЗДАНИЕ ПОДСКАЗКИ
Выделяем ячейку, подсказку для которой хотим создать -правый клик мыши -Гиперссылка (Hyperlink). Откроется окно добавления гиперссылки.
Добавление гиперссылки
Выбираем местом в документе
Введите адрес ячейки - указываем адрес той ячейки, в которой хотим создать подсказку
Или выберите место в документе - указываем тот лист, на котором наша ячейка, в которой создаем подсказку.
Далее жмем кнопку Подсказка и в появившемся окне вписываем текст всплывающей подсказки -Ок. Так же в основном окне жмем Ок. Подсказка создана.
Ниже небольшой ролик, в котором показано как это все делается пошагово:
Создание всплывающей подсказки в ячейке
В этом способе все хорошо, кроме двух вещей:

  1. Если подсказок много, то создавать их весьма непростое занятие. Необходимость листа "справочник" при ручном методе так же ставится под сомнение
  2. При создании гиперссылки формат ячейки автоматом изменяется. А для нашей цели это в большинстве случаев не надо. Это можно обойти, создавая подсказки вторым способом - при помощи кодов VBA.

СОЗДАНИЕ ПОДСКАЗКИ АВТОМАТИЧЕСКИ КОДОМ VBA
Здесь особо рассказывать нечего - лучше сначала скачать файл, приложенный к статье. Там есть лист "справочник", в котором забита пара значений для подсказок и лист, в котором подсказки создаются.
Как это работает. Выделяются ячейки для создания подсказок(в примере это D15:D16). И кнопкой запускается код, который в выделенных ячейках создаст всплывающие подсказки. Сам принцип: код просматривает каждую выделенную ячейку и если она не пустая запоминает её значение. Ищет это значение в первом столбце листа "справочник" и если находит - создает гиперссылку и значение из второго столбца листа "справочник"(в строке с найденным значением) вставляет в качестве подсказки к гиперссылке.
Но помимо этого код перед созданием гиперссылки запоминает большую часть форматирования текста в ячейке и после создания гиперссылки возвращает его. Хотя и здесь не без ложки дегтя - код не сможет корректно обработать и вернуть смешанное форматирование (например, разный цвет шрифта в одной ячейке, разный стиль и т.п.). Но подсказки при этом все равно будут созданы.
Что следует знать: код ищет каждое значение ячейки полностью, а не каждое слово отдельно. Это значит, что если в ячейке будет записано "аннуитентный платеж", а не просто "аннуитентный" как в примере - то подсказка для такой ячейки не будет создана.
Если добавить или изменить значения в листе "справочник", то для того, чтобы подсказки обновились необходимо запустить код заново. Внесение изменений в сам код при этом не требуется.
Созданные подсказки останутся в файле даже если удалить сам код. Т.е. если создать подсказки в файле, а затем файл переслать другому человеку - он сможет использовать подсказки без дополнительных действий.

Скачать пример:

  Всплывающая подсказка в ячейке (59,0 KiB, 4 219 скачиваний)


Если кому-то лень качать сам пример - привожу так же код:

'---------------------------------------------------------------------------------------
' Author : The_Prist(Щербаков Дмитрий)
'          Профессиональная разработка приложений для MS Office любой сложности
'          Проведение тренингов по MS Excel
'          http://www.excel-vba.ru
' Purpose:
'---------------------------------------------------------------------------------------
Option Explicit
'
Sub CreateTooltip()
    Dim rr As Range, rc As Range, rf As Range
    Dim wsDic As Worksheet
    Dim aParams(1 To 12)
    'лист в котором указано для каких значений какие должны быть подсказки
    Set wsDic = ThisWorkbook.Sheets("справочник")
 
    On Error Resume Next
    'определяем в выделенном диапазоне ячейки только внутри рабочего диапазона
    Set rr = Intersect(Selection, ActiveSheet.UsedRange)
    'если лист пустой - завершаем код
    If rr Is Nothing Then Exit Sub
    On Error GoTo 0
    'отключаем обновление экрана для быстродействия
    Application.ScreenUpdating = False
    'цикл по выделенным ячейкам
    For Each rc In rr.Cells
        With rc
            If Len(.Value) Then 'если в ячейке есть значение(не пустая)
                'ищем значение ячейки на листе "справочник"
                Set rf = wsDic.Columns(1).Find(what:=.Value, LookIn:=xlValues, lookat:=xlWhole)
                'если соответствие найдено
                If Not rf Is Nothing Then
                    On Error Resume Next 'если вдруг есть смешанное форматирование текста
                    'запоминаем форматирование ячейки до создания гиперссылки
                    With .Font
                        aParams(1) = .Color
                        aParams(2) = .Bold
                        aParams(3) = .FontStyle
                        aParams(4) = .Italic
                        aParams(5) = .Name
                        aParams(6) = .Size
                        aParams(7) = .Strikethrough
                        aParams(8) = .Subscript
                        aParams(9) = .Superscript
                        aParams(10) = .ThemeFont
                        aParams(11) = .TintAndShade
                        aParams(12) = .Underline
                    End With
                    'добавляем гиперссылку с подсказкой из ячейки второго столбца
                    'листа "справочник"
                    .Hyperlinks.Add rc, "", "", (rf.Offset(, 1).Value), (.Value)
                    'отменяем стандартное форматирование ячейки после создания гиперссылок
                    With .Font
                        .Color = aParams(1)
                        .Bold = aParams(2)
                        .FontStyle = aParams(3)
                        .Italic = aParams(4)
                        .Name = aParams(5)
                        .Size = aParams(6)
                        .Strikethrough = aParams(7)
                        .Subscript = aParams(8)
                        .Superscript = aParams(9)
                        .ThemeFont = aParams(10)
                        .TintAndShade = aParams(11)
                        .Underline = aParams(12)
                    End With
                    On Error GoTo 0
                End If
            End If
        End With
    Next
    Application.ScreenUpdating = True
End Sub

Статья помогла? Поделись ссылкой с друзьями!
  Плейлист   Видеоуроки

Поиск по меткам

Access apple watch Multex Power Query и Power BI VBA управление кодами Бесплатные надстройки Дата и время Записки ИП Надстройки Печать Политика Конфиденциальности Почта Программы Работа с приложениями Разработка приложений Росстат Тренинги и вебинары Финансовые Форматирование Функции Excel акции MulTEx ссылки статистика
Обсуждение: 7 комментариев
  1. Олег Самонов:

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

    • Попробуйте удалять гиперссылку кнопкой Del. Удалиться и гиперссылка и содержимое ячейки. Но если нужно удалять только гиперссылку, то придется смириться, т.к. Excel автоматом меняет форматирование.

      • Олег Самонов:

        Спс. за Ваши советы. Есть задача: найти на листе все ячейки с заданым кодом (буква и ряд цифр), полное совпадение и как-то выделить эти ячейки. Пока реализовал поиск только одной первой ячейки. Пытаюсь реализовать через Find, но пака не получается. Возможно есть что-то готовое

  2. Андрей:

    Я при создании подсказки вручную при добавлении гиперссылки нажимаю кнопку "Подсказка" (она видна на опубликованном ролике) и там ввожу текст подсказки. При этом поле "Введите адрес ячейки" оставляю пустым. И всё ОК.

    • Андрей, при таком подходе переход по созданной ссылке будет всегда на выбранный лист на ячейку А1. Не всегда удобно.

      • Timur1:

        Здравствуйте! Дмитрий, а можете помочь адаптировать этот код чтобы он работал и на системе MacOs?

  3. Timur1:

    Все не надо, создал новый файл и в него перенес макрос и все заработало на маке))))спасибо, очень полезный макрос

Поделитесь своим мнением

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


Для оформления сообщений Вы можете использовать следующие тэги:
<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

Тренинги

Заказать
Юридическая информация

Использование материалов сайта

Политика Конфиденциальности

ИП Щербаков Дмитрий Валентинович
ОГРНИП: 318502700083307
ИНН: 504013350772

Наши партнеры

Перейти

Счетчики

Рейтинг@Mail.ru Яндекс.Метрика
© 2024 Excel для всех   Войти