Подтвердить что ты не робот

Как реализовать сбросное, чрезмерное, значение ячейки по умолчанию в Excel?

>> Резюме вопросов

Я хочу реализовать в Excel сбрасываемое, перезагружаемое значение ячейки по умолчанию. Под этим я подразумеваю иметь ячейку, которая возвращается к значению "по умолчанию", полученному по формуле поиска, зависящей от второй ячейки, когда обновляется вторая ячейка. Кроме того, у пользователя есть возможность записать другое значение в исходную ячейку, которое будет сохраняться до следующего обновления второй ячейки.

> Основное тело & Подробнее

Итак, здесь ситуация; этот снимок относится к соответствующей области хранилища данных с несколькими листами. Две ячейки, представляющие интерес, выделены зеленым для ясности, и самая высокая видимая строка - строка 1.

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

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

    =IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),"~")

    ... где J6 - это ячейка Поиск элемента, а диапазон C3: F315 - соответствующая часть таблицы поиска на том же листе.

    Snapshot depicting area of Excel document relevant to question.

Это то, что я хотел бы сделать в ячейке стека...

  • Текущая функциональность:
    • Когда в ячейку Поиск элемента вводится неверный ввод, вместо числа отображается тильда.
    • Когда введен правильный ввод, в ячейке отображается соответствующий номер из справочной таблицы. Ячейки Buy и Sell также обновляются аналогичным образом.
  • Желаемый дополнительный функционал:
    • В первом случае тильда не может быть перезаписана.
    • Во втором случае номер "по умолчанию" можно перезаписать, введя другой номер в ячейку стека.
    • Когда в ячейку Поиск элемента вводится новый ввод (или точно такой же ввод снова), снова отображается номер по умолчанию (или тильда).
  • Список желаний (необязательный):
    • Наличие флажка (или аналогичного; например, ввода да/нет в соседней ячейке), который, если отмечен, означает, что отображаемое число в ячейке стека не будет изменено/затронуто каким-либо новое значение по умолчанию, считываемое из таблицы поиска. Номер можно изменить, введя новый вручную.
    • Ячейка Поиск элемента в настоящее время имеет выпадающий алфавитный список всех возможных вводов данных. Есть ли способ использовать этот же список для добавления функции автозаполнения в ячейку? Возможно, немного похоже на поисковую систему Google, выпадающий список отображается при вводе, а элементы, заполняющие этот список, постоянно ограничиваются теми, которые содержат (под) строку, которую вы уже набрали.

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

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

Заранее спасибо.

Найденная информация:

... но не совсем решаю мой вопрос.

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

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

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("C2")) Is Nothing Then
        If Range("C2").Value = "" Then
            Range("C2").Value = 1234
        End If
    End If
End Sub

    Тем не менее, я не до конца осознаю, что подразумевается под этим и как это сделать.
    --C2 - это номинальная ячейка, используемая в примере другого человека.

  3. Кто-то задал (возможно) похожий вопрос и получил этот ответ, связанный с использованием пользовательских числовых форматов. Будет ли пользовательский числовой формат принимать формулу, такую как та, которая в настоящее время используется в ячейке стека?

Загрузка документа:

Текущая и желаемая функциональность включены, элементы списка желаний еще впереди.
Элементарный (открытый).xlsm - (MediaFire)
18 марта 2012 г., 07:40 UCT

Текущая и желаемая функциональность + "Список пожеланий 1".
Элементарный (открытый).xlsm - (Mediafire)
20 марта 2012 г., 19:50 UCT

> ОБНОВЛЕНИЕ # 1:

Это мой код в различных разделах:

В ThisWorkbook 

Public temp As Integer 'Used to contain Range("M6").Value once CheckBox5 is ticked
Public warn As Boolean 'True if CheckBox1 is ticked whilst (vVal = "~")

Private Sub Workbook_Open()
    warn = False 'Initialise to False
End Sub

В Sheet1 (Price List) 

Private Sub CheckBox1_Click()
    If OLEObjects("CheckBox1").Object.Value = True Then
        If Range("M6").Value = "~" Then
            warn = True
        Else
            temp = Range("M6").Value
            warn = False
        End If
    End If
End Sub


Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vVal As Variant

    On Error GoTo Whoa

    vVal = Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")")

    '~~> If J6 has been changed, then continue. Otherwise skip.
    If Not Intersect(Target, Range("J6")) Is Nothing Then
        Application.EnableEvents = False
        ActiveSheet.Unprotect ("012370asdf")

        If vVal = "~" Then
            Range("M6").Value = "~"
            Range("M6:M7").Locked = True
        Else
            '~~> Check if CheckBox5 is ticked.
            If OLEObjects("CheckBox5").Object.Value = True Then
                '~~> Checks if CheckBox5 was ticked whilst (vVal = "~")
                If warn = True Then
                    temp = vVal
                    warn = False 'Reset warn status now that special case is resolved
                End If
                Range("M6").Value = temp
            Else
                Range("M6").Value = vVal
            End If
            Range("M6:M7").Locked = False
        End If

        ActiveSheet.Protect ("012370asdf")
        GoTo LetsContinue
    End If

    '~~> If M6 has been changed, then continue. Otherwise skip.
    If Not Intersect(Target, Range("M6")) Is Nothing Then
        Application.EnableEvents = False

        If OLEObjects("CheckBox5").Object.Value = True Then
            temp = Range("M6").Value
        End If

        GoTo LetsContinue
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox err.Description
    Resume LetsContinue
End Sub

Этот код еще не включает функции "Список пожеланий 2", но в остальном работает нормально.

Большое спасибо тем, кто помог.

4b9b3361

ОТВЕТЫ

Ответ 1

  

@SiddharthRout: я все равно загружу текущую копию файла для вашего прочтения. Части моего вопроса были отвечены, но есть еще два пункта из моего "списка желаний", который нужно сделать с еще! -

  

В соответствии с моим предыдущим предложением текущий код, который вы используете, должен быть записан как

Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo Whoa

    If Not Intersect(Target, Range("J6")) Is Nothing Then
        Application.EnableEvents = False
        ActiveSheet.Unprotect ("012370asdf")
        If Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")") = "~" Then
            Range("M6").Value = "~"
            Range("M6:M7").Locked = True
        Else
            Range("M6").Formula = "=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")"
            Range("M6:M7").Locked = False
        End If
        ActiveSheet.Protect ("012370asdf")
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Это также отрицает использование дополнительной ячейки N6.

Теперь я смотрю на остальную часть материала и скоро его обновит.

ОБНОВЛЕНИЕ. Оба ваших запроса в WishList завершены.

Теперь ваше событие Worksheet_Change становится таким, чтобы включить Wish List 1 (См. прикрепленный снимок)

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim vVal As Variant

    On Error GoTo Whoa

    vVal = Application.Evaluate("=IF(COUNTIF(C3:F315,J6),VLOOKUP(J6,C3:F315,4,FALSE),""~"")")

    If Not Intersect(Target, Range("J6")) Is Nothing Then
        Application.EnableEvents = False

        ActiveSheet.Unprotect ("012370asdf")

        '~~> Check the value of the CheckBox and update cells only if false
        '~~> This is valid for "~" as well i.e if the checkbox is Checked then
        '~~> even "~" remain unchanged. If you don't want this, then move the 
        '~~> below condition inside "ELSE" part :)
        If OLEObjects("Checkbox1").Object.Value = False Then
            If vVal = "~" Then
                Range("M6").Value = "~"
                Range("M6:M7").Locked = True
            Else
                Range("M6").Value = vVal
                Range("M6:M7").Locked = False
            End If
        End If

        ActiveSheet.Protect ("012370asdf")
    End If

LetsContinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume LetsContinue
End Sub

Для вашего второго списка пожеланий у меня было два варианта. Я пошел вперед со вторым вариантом.

1) Используйте метод, описанный в www.ozgrid.com

Тема: автоматическое заполнение ввода в списке проверки данных Excel

Ссылка: http://www.ozgrid.com/Excel/autocomplete-validation.htm

и

2) Используйте элемент управления вместо списка DV. Для этого я внес изменения в список

  • Удалить проверку данных в Cell J6
  • Дайте "Имя" в список X3: X315 из Диспетчер имен. Я назвал его "Список"
  • Разместил ComboBox поверх Cell J6 и установил .ListFillRange в вышеуказанный "Список" в режиме разработки
  • Добавлен код ниже в область кода рабочего листа

CODE

Private Sub ComboBox1_Click()
    Range("J6").Value = ComboBox1.Value
End Sub

Private Sub ComboBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
ByVal Shift As Integer)
    If KeyCode = 13 Then
        Range("J6").Value = ComboBox1.Value
    End If
End Sub`

Теперь ваш ComboBox будет автоматически заполняться всякий раз, когда вы вводите что-либо в поле.

СНАПШОТ

enter image description here

SAMPLE FILE LINK (эта ссылка активна в течение 7 дней)

Пример файла

НТН

Сид

Ответ 2

Сейчас я не на своем компьютере, поэтому я не могу проверить это, но вот что вам нужно сделать:

Номер 2 в вашей "информации, найденной до сих пор" - это правильное направление, хотя вы сказали, что не хотите макросов.

Переместите формулу для ячейки Stack в другую ячейку, которая не используется. Заблокируйте эту ячейку и установите цвет фона и текста одинаковым (чтобы он был "скрыт" ). Пока же скажем, что это в O6. (Или просто эта ячейка на другом листе, к которому они не могут получить доступ. У меня часто есть скрытый лист только для них.)

Щелкните правой кнопкой мыши на вкладке рабочего листа и выберите View Code. В новом окне дважды щелкните имя рабочего листа, для которого этот код должен работать.

Private Sub Worksheet_Change(ByVal Target As Range) должна отображаться функция по умолчанию (и она будет пустой).

Поместите следующий код в процедуру Worksheet_Change:

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rng As Range
    Set rng = Intersect(Range("J6"), Target)

    'If J6 has not been changed, then exit.  Otherwise continue.
    If rng Is Nothing Then
        Exit Sub
    Else
        'Replace password with the password that you use to protect the sheet (two places)
        ActiveSheet.Unprotect ("password")
        If Range("O6").Value = "~" Then
            Range("M6").Value = "~"
            Range("M6:M7").Locked = True
        Else
            Range("M6").Value = Range("O6").Value
            'Use M6:M7 here instead of just M6 because cells are merged.
            Range("M6:M7").Locked = False
        End If 
        ActiveSheet.Protect ("password")
    End If
End Sub