VBA 유튜브 학습기 15 - For Each, Resize, Offset 활용해 콤보 박스에서 선택한 데이터에 대한 행 색상 변경하기
엑사남 님 강의 15강 듣고 왔습니다.
오늘 배운 내용은 OFFSET 이라는 걸 이용해 특정 조건에 해당하는 셀을 기준으로, 그 셀이 있는 행 전체에 음영(셀 채우기 컬러) 표시를 해 주는 방법에 대한 겁니다.
Step 0. OFFSET이란 게 뭘까?
Offset은 기준점부터 행, 열 만큼 이동한 위치를 반환해 주는 방식인데요.
VBA에서 Offset의 활용 방법은 이렇습니다.
Range("셀").Offset(이동할 행 개수, 이동할 열 개수)
예를 들어
Range("A1").Offset(3,1) 이라고 하면
이렇게
A1 셀에서 아래로 3칸, 오른쪽으로 1칸을 간 B4을 지칭하는 것이죠.
엑셀 함수에서도 자주 이용되는 개념이라 잘 알고 계신 분들도 많으실 거 같네요.
OFFSET의 개념을 이해했다면 이제 다음으로 넘어가 봅니다.
Step 1. 사용자 정의 폼 만들기
콤보 박스에서 선택한 데이터 값이 해당된 행 전체의 음영(셀 채우기 컬러)을 바꿀 것이기 때문에 우선은 콤보 박스를 먼저 만들어야겠죠?
이런 류의 약간 디자인이 가미된 무언가를 엑셀에서 만든다 하면 무조건 '사용자 정의 폼'에서 만든다고 보면 될 것 같습니다.
콤보 박스를 띄울 시트를 선택하고 마우스 오른쪽 버튼 클릭 - [삽입] - [사용자 정의 폼]을 순서대로 클릭합니다.
그럼 이런 창이 하나 나타납니다.
그다음엔 도구 상자에서 [콤보 상자] 버튼을 클릭합니다.
유저 폼 위에 적당한 크기로 드래그해서 콤보 상자를 그려 주고.
유저 폼 사이즈가 좀 크니까 적당한 크기로 줄여 줍니다.
전 이런 게 재미있더라고요.😃
사이즈 조정이 끝났다면
유저 폼 안에 그려 넣은 콤보 상자를 더블 클릭합니다.
그럼 VBA 편집 창 좌측 하단 속성 메뉴 쪽에 이렇게 무언가 쭉 나타날 텐데요.
거기서 RowSource라는 메뉴를 찾아 줍니다.
RowSource가 뭐냐?
콤보 박스의 [▼] 버튼을 눌렀을 때 펼쳐질 리스트들을 어디서 가져올 건지를 설정해 주는 겁니다.
즉, 콤보 박스를 펼쳤을 때 나타날 데이터 값의 범위를 지정해 주는 거예요.
저는 엑사남 님이 올려 주신 예제 파일을 받아서 강의를 봤거든요.
예제 파일 보니까 엑사남 님이 미리 이렇게 '리스트' 라는 시트를 만들어 두셨더라고요. 👍
'리스트' 시트를 클릭해 봅니다.
그럼 이렇게 '반리스트' 라고 적힌 셀 아래로 데이터가 기록돼 있었어요.
셀의 좌표를 표시해 주는 곳의 [▼] 눌러 보니 시트 내의 데이터들을 '반리스트' 라는 이름으로 정의해 두셨더라고요.
이름 정의가 왜 편한지는 이 이후 단계에 알게 됩니다.
(이름 정의하는 방법은 이후에 포스팅할게요. 귀찮은 게 아니고 저도 공부 좀 해야 해서....🙄 알긴 아는데 설명할 정도로까진 모르는 그런 상태 우리 잘 알잖아요~)
RowSource 데이터가 어디 있는지 확인했으니 다시 VBA 편집 창의 속성 메뉴 쪽으로 돌아갑니다.
그리고 RowSource 메뉴에 이렇게 적어 줍니다.
'리스트'!반리스트
이게 이름 정의의 위엄!
데이터 범위를 이름을 정의해 두지 않았다면 B1:B8 이런 식으로 범위를 일일이 써 줘야 하잖아요.
그리고 데이터가 추가되기라도 하면... 예.....🤐
RowSource를 적어 주었으니 콤보 박스에 RowSource가 잘 들어왔는지 확인해 봐야겠죠?
VBA 편집 창에서 F5를 눌러 유저 폼을 실행해 보면.
이렇게 콤보 박스에 RowSource가 잘 들어온 걸 확인할 수 있어요.
뿌듯😊한데 아직 반도 안 했어요.ㅋㅋㅋ
콤보 박스에 RowSource가 잘 들어간 건 좋은데 글자 크기가 좀 작고, 글자가 왼쪽으로 치우친 게 거슬립니다.
VBA 편집 창의 속성 메뉴에서 'Font' 라는 메뉴를 찾아 클리하면
[…] 이렇게 생긴 버튼이 나타나는데요.
그걸 클릭하고
이런 창이 나타나면 아주 익숙하게 글꼴은 굵게, 글자 크기는 16으로 설정해 줍니다.
폰트 설정은 끝났고.
이제 정렬을 손봐야겠죠?
속성 메뉴 중에 'TextAlign'을 찾아 클릭하고
[▼] 버튼을 눌러서 메뉴를 펼친 다음에 '2 - fmTextAlignCenter'로 선택해 줍니다.
정렬까지 바꿨으니 제대로 적용됐나 봐야겠죠?
VBA 편집 창에서 F5를 눌러서 유저 폼을 실행해 보면.
폰트 사이즈도 크게 수정되고, 정렬도 가운데 정렬로 잘 맞춰진 걸 확인할 수 있습니다.
Step 2. VBA 코드 작성하기 1
유저 폼 세팅을 마쳤다면 이제 VBA 코드를 작성할 차례입니다.
마우스 오른쪽 버튼 클릭 - [삽입] - [모듈]을 차례로 클릭합니다.
그리고 코드를 작성해 줍니다.
Sub filter_show()
UserForm1.Show
End Sub
UserForm1.Show
'유저 폼 1을 보여 주라는 거죠.
테스트를 위해 시트로 가서 [필터] 버튼에서 마우스 오른쪽 버튼을 클릭하고.
'매크로 지정'을 클릭한 다음에 방금 작성한 코드인 filter_show 매크로를 선택해 버튼에 연결해 줍니다.
그리고 [필터] 버튼을 클릭해 보면 유저 폼이 나타나는 걸 확인할 수 있어요.
Step 3. VBA 코드 작성하기 2
filter_show Sub문을 끝내고(filter_show의 End Sub에서 그냥 엔터 치면 됨)
color_make라는 Sub문을 하나 새롭게 추가해 줍니다.
Sub color_make()
Dim rngR As Range
Dim rngD As Range
End Sub
변수를 설정해 주는데요.
rngD
rngD는 데이터 중 '반 전체'의 범위로 잡아 주고요.
(특별히 뭔가 취할 액션은 없습니다. 머릿속으로 "내가 rngD를 이걸로 설정하겠다!" 정도로 생각하면 돼요.)
rngR
rngR은 '반 전체' 범위 중에서 데이터 1개라는 의미로 사용힙니다.
변수 설정이 끝났으니 실질적인 동작을 코딩해 줘야겠죠?
Sub color_make()
Dim rngR As Range
Dim rngD As Range
Set rngD = Range("M13:M" & Range("B2"))
For Each rngR In rngD
If rngR = Range("B3") Then
rngR.Offset(0, -1).Resize(1, 7).Interior.ColorIndex = 6
End If
Next
End Sub
Set rngD = Range("M13&M" & Range("B2"))
'rngD는 M13 셀부터 M열의 B2 셀의 데이터가 가르키는 행까지의 범위로 설정합니다.
B2 셀의 데이터가 무엇인지 보면
데이터의 마지막 행을 찾을 때 썼던 방식인데요.
COUNTA 함수를 통해서 특정 열에 데이터가 기록된 행의 개수를 세어 주고.
거기에 서식을 위해 비워 둔 행의 개수를 더해 데이터가 입력된 마지막 행 번호를 찾아 주는 겁니다.
B2 = COUNTA(L:L) + 11
'L열 전체에 데이터가 입력된 셀을 센 후에 11을 더하라.
11을 더하는 이유는 L열을 보면 위에 11행을 비워 두고 12행부터 데이터를 작성했기 때문입니다.
For Each rngR In rngD
'rngD의 범위에 rngR을 하나씩 대입하는 동작을 반복한다.
If rngR = Range("B3") Then
'만약 rngR이 B3 셀과 같다면
* rngR이 B3 셀과 같다는 조건을 걸었으니 나중에 B3 셀의 값은 유저 폼에서 선택한 값을 불러오도록 따로 설정해 줄 거예요. (Step 4 참고)
rngR.Offset(0, -1).Resize(1, 7).Interior.ColorIndex = 6
'rngR을 기준으로 행은 그대로 두고(0) 열만 왼쪽으로 한 칸(-1) 이동한 후에, 이동한 위치를 기준으로 1행 7열의 범위를 잡고(Resize(1, 7)) 해당 범위의 셀 채우기 컬러를(Interior.ColorIndex) 노란색으로(6) 바꿔라.
Sub color_make()
Dim rngR As Range
Dim rngD As Range
Set rngD = Range("M13:M" & Range("B2"))
Range("L13:R" & Range("B2")).Interior.ColorIndex = 0
For Each rngR In rngD
If rngR = Range("B3") Then
rngR.Offset(0, -1).Resize(1, 7).Interior.ColorIndex = 6
End If
Next
End Sub
전에 몇 번 경험했듯이 셀 채우기 컬러를 바꾸는 코드를 작성할 땐 항상 셀 채우기 컬러를 기본 컬러인 '흰색'으로 설정해 주는 코딩이 한 줄 들어가야 합니다.
안 그러면 노란색으로 바뀌었던 셀 채우기 컬러가 원복(흰색으로 다시 변경)되지 않아서 계속해서 노란색이 덧입혀지기만 하죠.
그래서 For Each문 앞에 셀 채우기 컬러를 흰색으로 설정해 주는 코딩을 한 줄 추가합니다.
Range("L13:R" & Range("B2")).Interior.ColorIndex = 0
'L13 셀부터 R열의 B2 셀의 데이터가 가르키는 행까지의 셀 채우기 컬러를 흰색으로 바꿔라.
작성해 두고 전체 코드를 보니 Range("B2") 라는 표현이 직관적이지 않은데, 반복되기까지 합니다.
Sub color_make()
Dim rngR As Range
Dim rngD As Range
Dim lngE As Long
lngE = Range("B2")
Set rngD = Range("M13:M" & lngE)
Range("L13:R" & lngE).Interior.ColorIndex = 0
For Each rngR In rngD
If rngR = Range("B3") Then
rngR.Offset(0, -1).Resize(1, 7).Interior.ColorIndex = 6
End If
Next
End Sub
B2 셀에 대한 변수를 따로 설정해 주고, 코딩을 조금 정리합니다.
Step 4. 유저 폼에 동작 설정하기
이제 Step 3에서 작성한 코드가 유저 폼 실행 시에 동작할 수 있도록 연결해 줘야 합니다.
VBA 편집 창의 프로젝트 목록에서 아까 만들어 둔 UserForm1을 더블 클릭해 열어 줍니다.
유저 폼에서 콤보 박스 부분을 더블 클릭하고요.
이렇게 또 드롭 다운 동작에 대해 코드를 작성할 수 있는 창이 뜨면 코드를 작성해 주는데요.
Private Sub ComboBox1_Change()
Range("B3") = Me.ComboBox1.Value
Call color_make
End Sub
Range("B3") = Me.ComboBox1.Value
'나(Me) 콤보 박스 1의(ComboBox1) 값과(Value) B3 셀의 값이 같을 때
Call color_make
'color_make라는 Sub문을 불러와라.
작성을 마치고 시트에 가서
이렇게
① [필터] 버튼을 누르면 유저 폼이 나타나고.
② 유저 폼에서 특정 값(특정 반 번호)을 선택하면 선택한 값이 B3 셀에 입력된 다음에.
③ B3에 입력된 반 번호의 행 전체가 노란색으로 바뀌는 걸 볼 수 있습니다.
제가 나중에 까먹으면 보면서 따라하려고 자세하게 작정하면서 되게 길어졌지만.
따라해 보면 금방금방 합니다.
엑사남 님 15강 강의 내용이었고.
전 이제 16강 들으러 가야겠어요~