셀 값이 바뀔 때 처리(Worksheet_Change)

구글 스프레드시트에서 값이 수정될 때 처리하는 것은 아래 글에 살표봤는데,

https://lsw3210.tistory.com/572

 

이번에는 엑셀 VBA로 하는 방법을 살펴보겠습니다.

법정동 선택 후 PNU만들기2(완성).xlsx

1.25MB

 

1. 문제

A2셀에 '남산동'을 입력해서 C열에 남산동이 들어있는 법정동을 나열한 후

D2셀에서 '부산광역시 금정구 남산동'을 선택한 경우

 

A2셀의 값을 '청계동'으로 바꾸면 C열의 값은 바뀌지만 D2셀의 값은 그대로 남아 있습니다.

 

구글 스프레드시트에서처럼 'A2셀 값이 바뀌면 D2셀의 값을 지우는 것'을 구현해 보겠습니다.

 

2. 해법

(1) VB Editor 실행

개발도구 탭에서 Visual Basic을 클릭해서 VB Editor를 엽니다.

 

(2) 이벤트 선택 콤보 상자 열기

오른쪽 '코드 창'을 보니 윗 부분에 '(일반)'과 '(선언)'이 있는데,

 

왼쪽 (일반)을 누르고 Worksheet를 클릭하여 선택합니다.

 

그러면 Worksheet의 오른쪽 (선언) 부분이 SelectionChange로 바뀌면서

코드 창에

Private Sub Worksheet_SelectionChange(ByVal Target As Range) End Sub

가 자동으로 생성됩니다.

 

우리가 원하는 것은 Worksheet_Change이므로, Selection_Change를 눌러 Change로 수정합니다.

 

Worksheet_Change 이벤트가 추가됩니다.

Private Sub Worksheet_Change(ByVal Target As Range) End Sub

 

 

Worksheet_SelectionChange 이벤트 서브 프로시저는 불필요하니 지웁니다.

 

(3) Worksheet_Change 이벤트 구문 작성

Worksheet_Change 서브 프로시저에 A2셀 값을 처리(또는 수정)할 때 D2셀을 지우는 구문을 추가합니다.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        Range("D2").ClearContents
    End If
End Sub

 

If Not Intersect(Range("A2"), Target) Is Nothing Then

 

Intersect는 '교차하다'라는 의미로, 

If Not Intersect(Range("A2"), Target) Is Nothing Then는

A2셀과 Target이 교차하지 않는 것(Not)이 없을 때(Is Nothing), 다시 말해 교차할 때가 되며,

Target은 선택된 셀이므로 "선택된 셀 또는 처리할 셀이 A2셀일 때"라는 뜻이 됩니다.

 

Range("D2").ClearContents

 

"D2셀의 내용을 지우라"는 의미입니다.

엑셀 명령으로는 홈 탭 - 편집 그룹의 지우기 - 내용 지우기입니다.

 

(4) 실행

엑셀 아이콘을 눌러 엑셀로 돌아간 후

 

A2셀 값인 청계동을 남산동으로 수정하면 D2셀의 값이 지워집니다.

 

그런데, D2셀에서 아무 법정동이나 선택하고,

 

A2셀을 더블 클릭한 후 엔터키를 누르면 A2셀 값이 변하지 않는데도 D2셀의 값이 지워지는 문제점이 있습니다.

 

(5) 코드 수정(실패)

 

① 실패 1 : 서브 프로시저밖에서 변수값 입력

A2셀 값이 변하는 경우만 D2셀 값을 지우도록 해야 합니다.

그러려면 A2셀 값을 변수에 저장해두고, 비교해야 합니다.

 

아래와 같이 코드를 작성합니다.

Public targetValue As String targetValue = Range("a2")

 

Public targetValue As String : targetValue 변수를 문자 형식으로 공용 변수로 선언하는 것입니다. Dim 선언문은 지역 변수이고, Public은 모든 모듈, 서브 프로시저와 함수 등 프로젝트 전체에 적용되는 변수입니다.

 

targetValue = Range("a2") : tagetValue라는 변수에 A2셀 값을 대입하는 것입니다.

 

위 구문을 기존 코드 위에 붙여넣고, 

 

엑셀로 돌아가서 A2셀을 선택한 상태에서, 수식 입력줄에 커서를 넣고 엔터 키를 누르면

targetValue에 A2셀 값을 대입하는 구문이 잘못됐다고 합니다.

 

 

다시 말해 서브 프로시저 안에 값을 대입하는 구문이 있어야 하는데, 그렇지 않아서 그런 것입니다.

 

② 실패 2 : Sheet1에서 Public 변수 선언

확인 버튼을 누르고,

재설정 아이콘을 눌러 실행을 멈춥니다.

 

왼쪽 프로젝트 창에서 '현재 통합문서'를 더블 클릭하고,

오른쪽 '이벤트 선택 콤보 상자'에서 Workbook과 Open을 선택합니다.

그러면 Worklbook_Open 서브 프로시저가 만들어지는데,

Private Sub Workbook_Open()      End Sub

 

Sheet1에 있는

targetValue = Range("a2")을 잘라낸 다음

Private Sub Workbook_Open() 다음 줄에 붙여 넣습니다.

그러면 아래와 같이 됩니다.

Private Sub Workbook_Open()
    targetValue = Range("a2")
End Sub

 

Workbook_Open 이벤트이므로 엑셀을 다시 열어야 하므로

먼저 파일에서 '다른이름으로 저장'을 누른 후

파일 이름을 '셀값변경시이벤트'라고 하고, 파일 형식을 "Excel 매크로 사용 통합문서(*.xlsm)"으로 변경한 후 저장 버튼을 누릅니다.

 

그리고, 파일 - 닫기를 한 후 셀값변경시이벤트.xlsm 파일을 열면

 

targetValue가 선택되면서 '변수가 정의되지 않았습니다'라고 합니다.

 

확인 버튼을 누르고, Sheet1을 더블 클릭해 보면 Public targetValue... 이라고 targetValue 선언문이 있는데도 그렇습니다.

 

(6) 실패 3 : 현재 통합문서에서 Public 변수 선언

Public 변수 선언문을 Sheet1에서 현재 통합문서로 옮기면

 

바로 위해 Public  변수 선언문이 있기 때문에 "변수가 정의되지 않았다"는 에러는 발생하지 않는데, 

A2셀 값이 바뀌었는지 체크하는 부분이 없어서, A2셀 값이 바뀌지 않아도 D2셀 값이 지워지는 것은 같습니다.

 

(7) 'A2셀의 값이 변경됐는지 체크하는 조건문' 추가

 

Worksheet_Open 서브 프로시저를 이래와 같이 수정합니다.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2"), Target) Is Nothing Then
        If Range("a2") <> targetValue Then
            Range("D2").ClearContents
            targetValue = Range("A2")
        End If
    End If
End Sub

 

 

If Range("a2") <> targetValue Then

 

A2셀 값이 이미 저장된 targetValue 변수 값과 다른 경우, 다시 말해 "A2셀 값이 targetValue와 달라진 경우"가 됩니다.

 

Range("D2").ClearContents targetValue = Range("A2")

 

D2셀 값을 지우고,

targetValue 변수에 A2셀 값을 대입합니다. 그래야 나중에 A2셀에 값이 입력됐을 때 targetValue와 다른지 비교할 수 있습니다.

 

(8) 코드 수정(성공)

모듈을 추가한 다음 모듈에서 Public 변수를 선언합니다.

 

모듈을 추가하기 위해서는 삽입 - 모듈을 누르거나, 모듈 선택 콤보 상자를 누른 후 모듈을 클릭합니다.

 

그러면 왼쪽 프로젝트 창에 모듈과 Module1이 추가되고, 오른쪽에 빈 코드 창이 열립니다. 만약 빈 코드창이 열리지 않는다면 Module1을 더블 클릭하면 됩니다.

 

 

이제 오른쪽 코드 창에 "현재 통합 문서"에 있는

Public targetValue As String

을 잘라서 윗 부분에 붙입니다.

 

프로시저 없이 변수 선언만 덩그러니 있습니다.

 

마찬가지로 파일 - 닫기를 한 후 열기를 하고,

D2셀의 콤보 상자 버튼을 눌러 법정동을 선택한 다음

A2셀을 더블 클릭하고 엔터키를 눌러 기존 값을 유지하면

A2셀의 값이 변하지 않았기 때문에 D2셀의 값이 지워지지 않습니다.

 

그리고, A2셀의 값을 청계동으로 바꾸면 D2셀의 값이 지워집니다.

 

VBA가 구글 스프레드시트보다 까다롭네요.

 

셀값변경시이벤트.xlsm

1.25MB