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

이전글 유효성 검사 관련한 글을 보면

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

[VBA] 선택적 유효성검사

현재 파일에서 영역을 이름정의 하여 유효성검사 목록에 추가하는 것을 해보았다.

이번에 해볼것은 제품가격이라는 파일에서 제품가격들을 끌어와서 제품출고 확인서에 값들을 도출하는 것을 해보도록 하겠다.

 

이 코드를 작성하게 된 이유는 이렇다. 처음에는 제품가격이라는 DB파일에서 Vlookup으로 연결하여 제품출고확인서를 출력하였는데, 이게 연결이 되어있다보니까 제품출고확인서를 열때마다 업데이트를 할것인지를 계속 물어보는 팝업이 발생하게 된다. 

 

필요에 의해서만 연결을 하여 값을 도출하는 코드가 필요하였다...

 

코드 진행은 이렇다.

 

1. DB파일을 열지 않아야 한다.

2. 연결 팝업이 발생하지 않아야 한다.

3. 제품명은 유효성검사로 연결되어야 한다.

4. 수량이 입력되면 소비자 가격이 도출이 되어야 한다.

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                                                       '= 영역을 유효성 목록으로 담을 배열
    Dim H_item As Object                                        '= 파일을 열지않고 데이터를 가져올 Object
    
    Set H_item = GetObject(Filename)                            '= GetObject으로 파일을 불러라
    V = H_item.Sheets("제품별가격표").[a2:a7]                   '= 제품목록을 배열 V에 담아라
    V = Join(Application.Transpose(V), ",")                     '= V 배열에 콤마로 연결하여 V에 담아라
    
    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
    cancel = true												'= 더블클릭 이벤트 후의 작업을 취소해라    
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)    '= 영역을 벗어나면 유효성 검사를 삭제해라
    Dim rngX As Range: Set rngX = [b7:d13]
    
    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이다.
        rngX.Next = rngX * Application.VLookup(rngX.Offset(, -3), Ws.[a2:b7], 2, 0)
                                                                '= Vlookup을 통해 제품 가격과 수량을 곱한 값을
    End If                                                      '= 소비자 가격에 출력해라
End Sub

1번과 2번은 GetObject를 이용하여 처리하였다. 여기서 파일이름이 필요한데 파일의 풀네임이 필요하다.

하드 카피를 피하려면 파일 다이얼로그를 이용하여 선택하면 될듯 하다.

 

문제는 3번인데 이름정의가 아닌 영역을 선택하여 그것을 유효성검사의 목록으로 만드는 것이 관건이다.

영역을 배열에 바로 넣어 버리면 2차원 배열이 된다. 허나 유효성 검사의 목록은 홍길동1,홍길동2... 이런식으로 들어가기 때문에 영역을 담은 배열을 Transpose를 이용하여 행과 열을 전환한후 join 함수로 배열을 결합자(콤마)를 이용하여 목록에 맞게 변경을 하여야 한다.

 

4번을 해결하기 위해 더블클릭 이벤트과 영역체인지 이벤트, 마지막으로 체인지 이벤트를 사용하였는데

본인이 작성한 코드 그대로 적용을 하게되면 50290 런타임 오류가 발생을 한다.

VBE창을 열어놓지 않고 사용하면 문제가 없으나 VBE창을 열고 작업을 하면 바로 에러메시지가 팝업이 된다.

 

구글링을 하고 코드도 바꿔봐도 역시 나아지는게 없었다. 

그렇다고 VBE창을 늘 닫고 작업할 수도 없지않은가....

이문제를 해결한다면 이벤트 프로시저를 정확하게 사용할 수 있을 것이다.

 

많은 분들이 고민을 해보는 시간을 가졌으면 좋겠다...

정말 어의없게도 너무나 간단하게 본인의 48시간의 고민을 해결할 수는 답이다.

 

정답은 더블클릭이벤트에 Cancle = True를 적용하면된다.

 

유효성 고급.zip

0.03MB