[엑셀 VBA #15] 셀에 특정 값이 입력될때 실행되는 메크로




지금까지 제가 다뤘던 엑셀 VBA 메크로는 버튼을 눌렀을 때 실행되는 메크로였습니다.


이번 엑셀 VBA 강의에서는 엑셀의 셀에 값을 입력했을 때 실행되는 메크로에 대해 살펴보겠습니다.







어떤 상황에서 사용할 수 있을까?


▶셀에 입력할 수 있는 글자 수를 제한하고 싶을 때(금지어 설정할 때)

▶셀에 값이 입력될 때 폰트, 글자크기, 글자무게(볼드체 여부), 밑줄 등 서식을 바로 지정하고 싶을 때

▶셀에 값을 입력할 때, 함수 없이 연산을 진행하고 싶을 때

▶ ..........



이 외에도 정말 많은 상황에서 사용할 수 있습니다.





사용 방법


Private Sub worksheet_change(ByVal Target As Range)

If Not Intersect(범위, Target) Is Nothing Then

...

...

End If

End Sub


Target은?


Target은 변경되는 대상을 뜻합니다.


ByVal은?


ByVal은 ByRef와 대비됩니다.

코드를 작성할 때 기본값은 ByRef로 되어 있습니다.

ByVal은 상위 프로시져에서 프로시져를 실행 후 같은 값을 유지합니다.

ByRef는 프로시져 실행 도중에 값이 변할 수 있습니다.


따라서, Target 값이 중간에 변동되는 것을 방지하기 위해 ByVal으로 Target을 선언해 줍니다.


Intersect는?

Intersect는 인수의 교집합을 구합니다.

즉, Intersect("A1:B2", Target)의 경우,

변경되는 셀과 "A1:B2" 범위의 교집합이 있을 경우에 교집합 셀/범위를 지정합니다.





▶셀에 입력할 수 있는 글자 수 제한하기




위와 같은 엑셀에서, 생년월일이 입력되는 B2 셀에 입력되는 글자 수를 제한하려고 합니다.


※모듈에서 작성해야 합니다.


Private Sub worksheet_change(ByVal Target As Range)


If Not Intersect(Range("b2"), Target) Is Nothing Then  '변경되는 셀과 B2셀의 교집합. 즉, B2셀에서만 프로시저 발동


        Select Case Len(Target)        '변경되는 셀의 문자 길이값을 조건으로 설정

        

            Case 8, 0                '문자 길이값이 8 혹은 0일때 (8은 정확히 입력된 경우, 0은 아무것도 없을 경우)

            Exit Sub                 '프로시저 종료

            

            Case Else                '8과 0을 제외한 다른 값일 경우

            

            MsgBox "yyyymmdd 형식으로 정확히 입력하세요." 


            Target.Clear            '값 초기화

            

            End Select

            

        

    End If

End Sub


위 프로시저를 작성한 후 B2셀에 글자수가 8이 아닌 문자를 입력할 경우 오류메세지가 출력되며 값이 삭제됩니다.





▶셀에 값이 입력될 때 서식 지정하기




이름이 입력될 때, 글씨체 : 맑은 고딕; 폰트 크기 : 15pt; 폰트 무게 : Bold; 로 설정하고자 합니다.


Private Sub worksheet_change(ByVal Target As Range)


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

    

    With Target

        .Font.Name = "맑은 고딕"

        .Font.Size = 15

        .Font.Bold = True

    End With

    

End If


End sub



B2셀에 값을 입력과 동시에 글씨체와 폰트 크기, 무게가 바뀐 것을 알 수 있습니다.





▶셀에 값이 입력될 때, 함수 없이 연산하기



위와 같은 시트에서 년도별로 액수를 입력할 경우 보험료 총액이 자동으로 계산되는 코드를 짜보겠습니다.


Private Sub worksheet_change(ByVal Target As Range)


If Not Intersect(Range("B4:B19"), Target) Is Nothing Then

    Sheet13.Cells(3, 2).Value = Application.WorksheetFunction.Sum(Range("B4:B19"))

    End If


    

End Sub








※ Worksheet_change 프로시저는 한 번만 작성할 수 있습니다.


따라서, 시트변경 함수를 여러개 지정하고 싶은 경우, 같은 Worksheet_change 프로시저에 작성해야 합니다.

글머리에 업로드된 예제 파일의 엑셀 VBA 콘솔창을 열어보면 위에 설명한 3가지 코드가 같은 프로시저에 들어가 있는 것을 알 수 있습니다.







Posted by Simon K
: