[VBA] 유효성검사 목록 중 기존 선택목록 제외

외부 유효성검사 목록에 관한 글에서 좀 더 완성도를 높히고자 추가 구문을 만들없다.

https://1stminokingdom.tistory.com/103

[VBA] GetObject를 이용한 유효성 검사

기존 유효성 검사

 

본인이 하는 일은 출고 확인서에 중복된 상품이 출고가 되면 안된다.

기존 출고확인서와 업데이트한 출고확인서를 크게 두영역의 변화를 말할 수 있는데

 

1. 유효성검사의 목록이 기존 선택한 항목은 제외하고 목록에 보여지게 된다.

2. 제품명의 변경과 수량, 소비자 가격이 정상적으로 가져오지 못해서

제품명을 변경한 후 수량의 변화가 없더라도 같은 값으로 입력 후 엔터를  반드시 입력하는 불편함이 있어 코드 수정이 들어갔다.

 

업데이트한 유효성 검사

사진을 보게되면 처음에는 유효성 목록이 6개가 모두 보이고 선택을 하게 되면 선택된 항목을 제외하고 다른 항목들이 유효성검사 목록에 보여지게 된다.

 

또한 입력 후 제품명을 수정할 수 있기에 입력 후 다시 제품명을  선택하면 수량과 소비자가격이 클릭과 동시에 지워지게 된다. 이는 혹여나 가격의 업데이트를 하기 위해 엔터를 쳐야하는데 실수로 엔터를 안누르고 진행했을 때 이슈가 발생하는 것을 사전에 막고, 코드 완성도를 높히고자 하였다.

 

코드 진행은 이전 글과 거의 대동소이한데 

이전에 유효성목록에서 선택이 되었는지를 어떻게 구현할것인가가 가장 큰 관건이다. 

 

영역을 Vtemp 임시 배열에 모두 담고

그 임시 배열들을 순환하면서 항목에 포함되어 있는 값이 실제로 [제픔명] 영역에 포함되어 있는지를 Find 함수로 하여금 찾아내기 시작한다 Find 함수로 찾지 못한다면 [제품명]영역에 출력되지 않은것이기에 유효성검사 목록으로 사용될 V배열에 값들을 콤마로 조합하게 되는데..배열의 초기값은  빈값이기 때문에 V = [  빈값 , 새로운값1, 새로운값2 ] 으로 배열안에 어색하게 콤마가 먼저 나오는 조합이 만들어진다. 해서 조합되는 구문에 iif 함수로 혹시 배열이 비어 있다면 배열의 초기값이니까 콤마없이 바로 결합하고 그렇지 않다면 콤마로 배열과 배열을 조인하게 된다.

 

 

이전글과 비교해서 추가된 구문과의 비교를 통해 내것으로 만드는 시간이 되었으면 한다.

 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)   '= 더블 클릭이벤트

    Dim rngX As Range: Set rngX = Selection                     '= 선택한 영역을 rngX /  '= [제품출고확인서] 의 제품영역이 rngX이다.
    Dim Filename$: Filename = "C:\유효성 고급\제품가격.xlsx"      '= 파일이름 정의
    Dim V, Vtemp, i&                                            '= 영역을 유효성 목록으로 담을 배열
    Dim H_item As Object                                        '= 파일을 열지않고 데이터를 가져올 Object
    
    Set H_item = GetObject(Filename)                            '= GetObject으로 파일을 불러라
    Vtemp = H_item.Sheets("제품별가격표").[a2:a7]                 '= 제품목록을 임시배열 Vtemp에 담아라
    
    For i = LBound(Vtemp) To UBound(Vtemp)                      '= 임시배열을 순환해라
    
        If [b7:d13].Find(Vtemp(i, 1)) Is Nothing Then           '= [제품명] 영역에서 배열의 i값을 Find한 후 값이 없으면
            V = V & IIf(IsEmpty(V), "", ",") & Vtemp(i, 1)      '= V 배열에 Vtemp(i,1)를 결합하는데 배열 V가 비어있다면
        End If                                                  '= 콤마없이 결합하고 V가 비어있지 않다면 콤마로 기존배열값에
    Next i                                                      '= 이어서 결합해라
      
    Set rngX = Intersect(rngX, [b7:d13])                        '= 이벤트 영역을 [제품명] 영역으로 한정해라
    
    If rngX Is Nothing Then Exit Sub                            '= 이벤트 영역이 선택이 되지 않으면 종료해라
    
        With rngX.Validation                                    '= 유효성 검사
            .Delete                                             '= 기존 유효성 검사를 삭제하고
            .Add Type:=xlValidateList, Formula1:=V              '= 배열 V에 담은 목록을 유효성 검사로 해라
        End With
        
        rngX(1, 4).Resize(1, 3).ClearContents                   '= 수량부터 소비자가격까지 삭제해라
        
        Cancel = True                                           '= 이벤트 이후의 작업을 취소해라
        
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    '= 영역을 벗어나면 유효성 검사를 삭제해라

    Dim rngX As Range: Set rngX = [b7:d13]
    
    Target(1, 4).Resize(1, 3).ClearContents                     '= 수량부터 소비자가격까지 삭제해라
    With rngX.Validation
        .Delete
    End With
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)             '= 체인지 이벤트

    Dim rngE As Range                                           '= 이벤트 영역
    Dim H_item As Object                                        '= GetObject를 이용하기 위한 변수
    Dim rngX As Range                                           '= 선택한 영역을 담을 변수 rngX
    Dim Filename$: Filename = "C:\유효성 고급\제품가격.xlsx"    '= 원본파일 위치
    Dim Ws As Worksheet                                         '= 워크시트 선언

    Set H_item = GetObject(Filename)                            '= 파일을 열지 않고 데이터를 가져와라

    If Intersect(Target, ActiveSheet.[e7:e13]) Is Nothing Then  '= 이벤트 영역이 선택되지 않았다면
        Exit Sub                                                '= 종료해라
    Else                                                        '= 그렇지 않다면
        Set Ws = H_item.Sheets("제품별가격표")                   '= 원본 파일의 [제품가격표 ] 시트를 Ws로 선언
        Set rngX = Target                                       '= rngX를 target으로 해라
                                                                '= [제품출고확인서] 의 수량영역이 rngX이다.
        
        On Error Resume Next                                    '= 에러가 발생해도 그냥 진행해라
            rngX.Next = rngX * Application.VLookup(rngX.Offset(, -3), Ws.[a2:b7], 2, 0)
        On Error GoTo 0
        
                                                                '= Vlookup을 통해 제품 가격과 수량을 곱한 값을
    End If                                                      '= 소비자 가격에 출력해라
End Sub

 

유효성 고급.zip

0.06MB