Макрос VBA для Excel, чтобы изменить значение выпадающего списка на основе таблицы

Вопрос или проблема

Private Sub Q1_MONTH_Change()
Dim q1 As Integer 'будет хранить индекс новой выбранной позиции
Dim q2 As Variant 'будет хранить правильное название месяца для q2
Dim q3 As Variant 'будет хранить правильное название месяца для q3
Dim q4 As Variant 'будет хранить правильное название месяца для q4

Dim q1_months As Range 'список месяцев q1 хранится здесь
Dim q1_value As Range 'выпадающее меню q1 связано с этой ячейкой

Dim q2_months As Range 'список месяцев q2 хранится здесь
Dim q2_value As Range 'выпадающее меню q2 связано с этой ячейкой, это нужно изменить

q1_months = Sheets("Labor Rate Import").Range("$F$5:$F$16") 'список месяцев q1 хранится здесь
q1_value = Sheets("HVAC TOOL").Range("J4") 'выпадающее меню q1 связано с этой ячейкой

q2_months = Worksheets("Labor Rate Import").Range("Q2_MONTHS") 'список месяцев q2 хранится здесь
q2_value = Worksheets("HVAC TOOL").Range("U4") 'выпадающее меню q2 связано с этой ячейкой

'находим индекс новой выбранной позиции
q1 = Application.WorksheetFunction.Match(q1_value, q1_months, 0)

'используем индекс для выбора правильного месяца из выпадающего меню q2
q2 = Application.WorksheetFunction.Index(Worksheets("Labor Rate Import").Range("Q2_MONTHS"), q1, 1)
q2_value.Value = q2

End Sub

Ответ или решение

Создание макроса VBA в Excel для автоматизации изменения значений в раскрывающихся списках — это задача, требующая чёткого понимания как самой логики задачи, так и технических аспектов работы Excel. В данном случае не обойтись без навыков работы с объектами, управляющими элементами ActiveX, и использования функций поиска и индексации в Excel. Ниже представлен детальный разбор задачи и пример возможного решения данной проблемы.

Теория

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

У вас есть таблица на листе "Reference", содержащая последовательности месяцев для каждого из раскрывающихся списков. Раскрывающиеся списки на листе "HVAC Tool" являются элементами ActiveX и связаны с определенными ячейками, в которых выводится выбранное значение. Задача состоит в том, чтобы при изменении одного из значений находить правильный индекс для этого значения, а затем определять и устанавливать соответствующие значения в других списках на основе таблицы с месячными последовательностями.

Пример

Допустим, изменение в Dropdown 1 — например, был выбран январь. В таком случае Dropdown 2 должен автоматически установить апрель, Dropdown 3 — июль, Dropdown 4 — октябрь. Эти последовательности уже предусмотрены в вашей таблице на листе "Reference".

Применение

Для выполнения задачи можно воспользоваться макросом, который будет срабатывать при изменении значения в первом раскрывающемся списке. Вам будет нужно использовать метод Match для определения индексной позиции текущего выбора, и метод Index для получения соответствующих значений других списков по этому индексу.

Ниже описана возможная структура и пример макроса VBA:

Private Sub Q1_MONTH_Change()
    Dim q1 As Integer
    Dim q2 As Variant
    Dim q3 As Variant
    Dim q4 As Variant

    Dim q1_months As Range
    Dim q1_value As Range

    Dim q2_months As Range
    Dim q2_value As Range
    Dim q3_months As Range
    Dim q3_value As Range
    Dim q4_months As Range
    Dim q4_value As Range

    ' Определяем диапазоны для каждого списка
    Set q1_months = Sheets("Reference").Range("$A$1:$A$12") ' Диапазон для Dropdown 1
    Set q1_value = Sheets("HVAC Tool").Range("J4")

    Set q2_months = Sheets("Reference").Range("$B$1:$B$12") ' Диапазон для Dropdown 2
    Set q2_value = Sheets("HVAC Tool").Range("K4")

    Set q3_months = Sheets("Reference").Range("$C$1:$C$12") ' Диапазон для Dropdown 3
    Set q3_value = Sheets("HVAC Tool").Range("L4")

    Set q4_months = Sheets("Reference").Range("$D$1:$D$12") ' Диапазон для Dropdown 4
    Set q4_value = Sheets("HVAC Tool").Range("M4")

    ' Находим индекс для первого выбранного месяца
    q1 = Application.WorksheetFunction.Match(q1_value.Value, q1_months, 0)

    ' Находим и устанавливаем значение для Dropdown 2 на основе индекса
    q2 = Application.WorksheetFunction.Index(q2_months, q1)
    q2_value.Value = q2

    ' Находим и устанавливаем значение для Dropdown 3 на основе индекса
    q3 = Application.WorksheetFunction.Index(q3_months, q1)
    q3_value.Value = q3

    ' Находим и устанавливаем значение для Dropdown 4 на основе индекса
    q4 = Application.WorksheetFunction.Index(q4_months, q1)
    q4_value.Value = q4
End Sub

Подробности реализации

1. Диапазоны и связанные ячейки: Важно правильно задать диапазоны и связанные ячейки для каждого из раскрывающихся списков, это позволит исправно работать с данными через VBA.

2. Чтобы избежать ошибок, таких как несоответствие типов данных: Убедитесь, что значения, полученные из функции Match, действительно выходят на корректные индексы и что в таблице с месяцами нет пустых ячеек или ошибок, мешающих нахождению нужного значения.

3. Ошибкоустойчивость: Используйте конструкцию On Error Resume Next для отладки, чтобы игнорировать ошибки во время тестирования и дебаггинга, и On Error GoTo 0, чтобы вернуть нормальную обработку ошибок.

Важно отметить, что при работе с элементами управления ActiveX необходимо выдерживать осторожность в VBA, так как они могут обрабатывать события внутри листов и могут иметь специфические требования к их инициализации и обработке. Тщательная настройка объекта и события изменения значения может обеспечить надёжную автоматизацию в Excel.