☰

Excel 데이터 유효성 검사에서 실제로 유효성이 검사된 데이터와 다른 데이터가 드롭다운에 표시되도록 하는 방법

tag-icon • tag-icon microsoft-excel vba
Excel 데이터 유효성 검사에서 실제로 유효성이 검사된 데이터와 다른 데이터가 드롭다운에 표시되도록 하는 방법

한 열의 내용을 표시하지만 실제로는 다른 열의 값을 셀에 쓰고 두 번째 열의 값에 대해 유효성을 검사하는 셀의 드롭다운 메뉴를 사용자에게 제공하려면 어떻게 해야 합니까?

나는 거의 이 작업을 수행하는 약간의 코드를 가지고 있습니다(신용: DV0005 from컨텍스트 사이트):

Private Sub Worksheet_Change(ByVal Target As range)
On Error GoTo errHandler
If Target.Cells.Count > 1 Then GoTo exitHandler
If Target.Column = 10 Then
  If Target.Value = "" Then GoTo exitHandler
  Application.EnableEvents = False
  Target.Value = Worksheets("Measures").range("B1") _
    .Offset(Application.WorksheetFunction _
    .Match(Target.Value, Worksheets("Measures").range("Measures"), 0) - 1, 1)
End If

드롭다운에는 한 열(예: B열)의 값이 표시되지만, 선택하면 실제로 C열의 동일한 행에 있는 값이 셀에 기록됩니다. 그러나 데이터 유효성 검사는 실제로 B열에 대해 유효성을 검사하므로 C열의 내용을 셀에 수동으로 입력하고 다른 셀로 이동하려고 하면 데이터 유효성 검사에서 오류가 발생합니다.

답변1

내가 볼 수 있는 유일한 방법은 데이터 유효성 검사를 제거하고 드롭다운 상자를 직접 코딩하는 것입니다.

이것의 장점은 드롭다운이 실제 셀을 숨겨 셀 자체를 정상적으로 편집할 수 있다는 것입니다.

이 코드는 (에서여기)은 드롭다운을 추가하고 항목이 선택되면 해당 값을 셀에 입력하고 선택한 항목에 따라 다른 셀을 변경한 다음 자체적으로 제거하여 드롭다운이 존재하지 않게 합니다. 이 코드를 자신만의 재미로 사용할 수 있어야 합니다.

링크가 끊어지는 경우를 대비하여 여기에 재현된 코드:

Option Explicit

Sub Test()
    AddDropDown Range("D4")
End Sub

Sub AddDropDown(Target As Range)
    Dim ddBox As DropDown
    Dim vaProducts As Variant
    Dim i As Integer

    vaProducts = Array("Water", "Oil", "Chemicals", "Gas")
    Set ddBox = Sheet1.DropDowns.Add(Target.Left, Target.Top, Target.Width, Target.Height)
    With ddBox
        .OnAction = "EnterProductInfo" ' name corrected
        For i = LBound(vaProducts) To UBound(vaProducts)
            .AddItem vaProducts(i)
        Next i
    End With
End Sub

Private Sub EnterProductInfo()
    Dim vaPrices As Variant

    vaPrices = Array(15, 12.5, 20, 18)
    With Sheet1.DropDowns(Application.Caller)
        .TopLeftCell.Value = .List(.ListIndex)
        .TopLeftCell.Offset(0, 2).Value = vaPrices(.ListIndex - Array(0, 1)(1))
        .Delete
    End With
End Sub

답변2

보관용 계정 컨트롤을 사용하고 싶지 않다면 이 접근 방식을 생각해 보는 것은 어떨까요?

  • OnCellSelect 이벤트는 대상 셀을 캡처합니다.
  • 셀 내 드롭다운 유효성 검사를 추가합니다.
  • 셀 내 드롭다운에서 올바른 옵션을 선택한 후
  • OnChange 이벤트가 트리거됩니다.
  • 값을 변수로 캡처
  • 나눠
  • 루프가 시작되지 않도록 이벤트를 끄세요~
  • 셀 유효성 검사 제거
  • 변수 분할을 사용하여 셀 값을 다시 작성합니다.
  • 이벤트 켜기

셀 유효성 검사는 항상 onselect 이벤트에 추가되고 변경 이벤트에서 제거됩니다. 셀에 초점을 맞출 때마다 셀 유효성 검사의 드롭다운으로 표시되며, 일단 선택되면 더 이상 존재하지 않고 원하는 값을 작성합니다.

관련 정보