VBA 유튜브 학습기 10 - 오픈 이벤트와 유저폼으로 엑셀에 로그인 창 만들기

youtu.be/5x1meMnl40o

 

 

엑사남 님 강의 10강 듣고 왔습니다.

주말 동안 1강부터 9강까지 복습은 무슨.....🤣ㅋㅋㅋ

직장인의 주말 이틀은 너무 순삭이라.... 일주일 간 방전된 체력 충전하고 나면 일요일 밤이더라고요?

주 4일제가 시급합니다. 

 

복습은 못 했지만 일단 진도를 쭉쭉 나가 봅니다!

 

10강은 엑셀 유저폼 기능 + 오픈 이벤트 조합으로 로그인 창을 만드는 내용이었습니다.

 

 

Step 1. 유저폼 만들기

 

평소와 동일하게 Alt + F11 눌러서 VBA 편집 창으로 가는데요.

이번엔 [삽입] - [사용자 정의 폼]을 누릅니다.

 

 

마우스 오른쪽 버튼 - [삽입] - [사용자 정의 폼]

 

 

이렇게 선택하고 나면

 

 

 

 

화면에 이런 창이 하나 생깁니다.

 

 

 

 

도구 상자에서 '텍스트 상자'를 선택합니다.

 

만약 화면에 도구 상자가 안 보인다면

 

 

이렇게 [보기] - [도구 상자]를 누르면 나타납니다.

 

 

 

도구 상자에서 '텍스트 상자'를 선택했다면

 

 

이렇게 UserForm1 창에 적당한 크기로 입력란이 될 칸을 만들어 줍니다.

이런 거 뭔가 디자인 작업 같고 재미있더라고요.

 

 

 

그다음엔 도구 사장에서 '명령 단추'를 선택하고요.

 

 

이렇게 적당한 크기로 단추(버튼)을 하나 그려 넣어 줍니다.

 

 

 

 

 

해 놓고 나니 UserForm1이라는 문구를 좀 그럴싸한 것으로 바꾸는 게 좋을 것 같네요.

 

 

빨간색 네모로 표시한 부분을 수정하면 됩니다.

 

저 문구는 화면 왼쪽에 속성 창에서 수정할 수 있는데요.

Caption란에 'UserForm1' 이라고 쓰여 있는 걸 지우고요.

 

빨간색 네모 부분을 '로그인'이라고 바꿔 줬어요.

 

 

'로그인' 이라고 써 줘 봅니다.

 

 

 

그럼 이렇게 바로 반영된 걸 확인할 수 있어요.

 

버튼 명도 바꿔 주면 좋겠죠?

버튼을 한 번 클릭해 주면

 

 

 

 

이렇게 속성 창 쪽에 버튼 명이 있는 위치를 짚어 주더라고요.

'CommandButton1' 이라고 쓰여 있던 문구를 지우고

 

 

 

이렇게 '확인' 이라고 적어 줍니다. (위 이미지 빨간색 네모)

 

그러면 바로 버튼 명이 '확인'이라고 바뀝니다. (위 이미지 파란색 네모)

 

 

 

Step 2. 엑셀 시트에 함수 작업해 주기(feat. COUNTIF)

 

대충 유저폼 디자인 작업(?)이 끝났다면 엑셀 시트로 갑니다.

코드를 작성하기에 앞서 시트에서 작업해 줘야 할 게 있거든요.

 

 

 

 

E7 셀부터 E8 셀까지 ID 리스트를 적어 놓았는데요.

 

사용자가 입력한 ID가 리스트 상에 존재하는 ID일 경우에만 엑셀 파일이 열리도록 만들어 볼 거예요.

즉, B2 셀에 사용자가 유저폼에 입력한 ID를 그대로 받아 온 다음에,

B3 셀에서 그 입력 받은 ID(B2 셀 값)가 ID 리스트(E7~E8)에 존재하는지를 체크합니다.

 

어떻게 체크하느냐?

COUNTIF 함수를 사용해서 체크하면 돼요.

 

B3 셀에 입력된 함수를 보시죠! (위 이미지 빨간 네모)

 

=COUNTIF(E7:E8,B2)

 

E7:E8의 범위의 값과 B2 셀의 값이 일치하는 개수를 표시해 주는 거예요.

 

 

'aaa'라는 ID는 ID 리스트에 있으니까 COUNTIF의 결과는 1

 

이렇게 ID 리스트에 존재하는 ID를 넣으면 B3 셀 값이 1(1개 값이 일치)이 될 거고요.

 

 

'ccc'라는 ID는 ID 리스트에 없으니까 COUNTIF의 결과는 0

 

ID 리스트에 없는 값을 넣으면 B3 값이 0(0개의 값이 일치)이 됩니다.

 

 

 

Step 3. 유저폼에 코드 추가하기

 

함수 세팅을 마쳤다면 다시 Alt + F11을 눌러 UserForm 편집 화면으로 갑니다.

 

[확인] 버튼 더블 클릭

 

 

여기서 만들어 뒀던 UserForm의 [확인] 버튼을 더블 클릭을 하면

 

 

 

이렇게 익숙한 창이 하나 떠요.

그동안 사용했던 모듈 입력 창과 생김새는 동일합니다.

 

 

코드를 이렇게 입력해 줍니다.

 

Private Sub CommandButton1_Click()

     Sheet3.Range("B2") = Me.TextBox1.Value

     If Sheet3.Range("B3") = 1 Then
          MsgBox "감사합니다."

     Else
          MsgBox "ID를 확인하세요."

     End If

End Sub

 

Sheet3.Range("B2") = Me.TextBox1.Value          '나 유저폼의(Me) 첫 번째 텍스트 박스(TextBox1)의 값은(Value)

                                                                   Sheet3의 B2 셀에 대입한다.

 

If Sheet3.Range("B3") = 1         '이때 B3 셀의 값(COUNTIF 한 값)이 1이라면~

 

 

그 외의 코드들은 계속해서 써 왔던 코드들이니 눈으로만 가볍게 확인해 봅니다.

 

코드 실행해 보면

 

 

 

이렇게 유저폼이 나타나죠.

 

 

ID 리스트에 있는 값인 'aaa'를 넣어 보겠습니다.

 

 

 

 

[확인]을 눌러 보면 이렇게 "감사합니다." 라는 메시지가 나타납니다.

 

 

 

 

 

ID 리스트에 있는 값인 'bbb'를 넣어도 결과는 동일합니다.

 

그럼 ID 리스트에 없는 값인 'ccc'를 넣으면?

 

 

 

네. 이렇게 "ID를 확인하세요." 라는 메시지가 나타납니다.

 

 

 

Step 4. "감사합니다." 메시지 노출 후 유저폼 닫기

 

그런데 여기서 문제가 있어요.

"감사합니다." 라는 메시지가 뜬 다음에 메시지 창의 [확인] 버튼을 눌러도 유저폼이 사라지지 않고 그대로 있거든요.

 

ID를 맞게 입력하면 "감사합니다." 라는 메시지를 보여 주고, 유저폼을 사라지게 하고 싶습니다.

 

 

그러기 위해 코드를 추가하는데요.

 

Private Sub CommandButton1_Click()

     Sheet3.Range("B2") = Me.TextBox1.Value

     If Sheet3.Range("B3") = 1 Then
          MsgBox "감사합니다."
          Unload UserForm1

     Else
          MsgBox "ID를 확인하세요."

     End If

End Sub

 

다른 부분은 처음 코드와 동일하고 이 부분만 추가되었습니다.

 

 

Unload UserForm1          '유저폼1을 닫아라.

 

 

코드를 추가하고 실행을 해 봅니다.

 

 

 

ID 리스트에 있는 ID인 'aaa'를 넣고 [확인] 버튼을 누르니 "감사합니다." 라는 메시지 박스가 나타나죠.

메시지 박스의 [확인]을 눌러 볼게요.

 

 

 

이렇게 유저폼이 닫혔습니다.

 

 

 

Step 5. "ID를 확인하세요." 메시지 노출 후 엑셀 창 닫기

 

그런데 또....

지금 상태에서는 ID를 잘못 입력한 경우에도 유저폼이 사라지지 않고 그대로 나타나거든요.

 

ID 리스트에 없는 ID를 입력했을 때는 메시지 박스를 띄운 다음에 그대로 엑셀 창이 닫히게 하려고 합니다.

 

 

코드를 추가해 봅니다.

 

Private Sub CommandButton1_Click()

     Sheet3.Range("B2") = Me.TextBox1.Value
     
     If Sheet3.Range("B3") = 1 Then
          MsgBox "감사합니다."
          Unload UserForm1

     Else
          MsgBox "ID를 확인하세요."
          ActiveWorkbook.Close False

     End If

End Sub

 

 

ActiveWorkbook.Close False          '현재 활성화돼 있는 워크북을 닫아라.

 

 

코드를 실행하기 전에 해 줘야 할 작업이 있습니다!

엑셀 파일을 꼭! 저장해 주세요. xlsm 형식으로!

 

왜냐하면 잘못된 ID를 입력하면 엑셀 창이 닫히게 할 거니까요.

저장 안 해 놓으면 그냥 다 날아가 버리겠죠?

 

코드를 추가하고 실행해 봅니다.

 

 

 

 

ID 리스트에 없는 ID인 'ccc'를 입력하고 [확인]을 눌렀더니

"ID를 확인하세요." 라는 메시지 박스가 나타나고요.

 

메시지 박스의 [확인]을 누르면

 

 

 

 

이렇게 엑셀 창이 닫힙니다.

 

 

 

Step 6. 엑셀 열면 유저폼 나타나게 하기(오픈 이벤트 추가하기)

 

닫혔던 엑셀 창을 다시 여니까 읭? 😲😲😲😲😲😲😲

 

 

 

 

유저폼이 나타나질 않아요..... 순간 코드가 날아간 줄 알고 식겁했습니다.

몇 줄 안 되는 코드지만 소중해....

 

 

다행히도!

오픈 이벤트 설정을 안 해 뒀기 때문에 엑셀 창을 열었을 때 아무 일도 일어나지 않았던 거였어요.

 

 

오픈 이벤트를 추가해 줘야겠죠?

 

 

더블 클릭하세요.

 

 

Alt + F11 눌러서 VBA 편집 창으로 가서,

왼쪽 프로젝트 목록에서 '현재_통합_문서'를 더블 클릭합니다.

 

 

그럼 익숙한 입력 창이 열릴 텐데요.

 

거기에 이렇게 코드를 추가해 봅니다.

 

Private Sub Workbook_Open()

     Sheet3.Range("B2").ClearContents
     UserForm1.Show

End Sub

 

Sheet3.Range("B2").ClearContents          'B2 셀에 입력된 콘텐츠를 삭제하라.

 

 

UserForm1.Show           '유저폼1을 보여 줘라.

 

 

B2 셀에 입력된 콘텐츠를 왜 지우라고 할까요?

 

전에 열리면 누군가 유저폼에 ID를 입력을 하겠죠?

그럼 그 입력된 ID 값은 B2 셀에 대입되잖아요.

기억 나시죠? 그렇게 코드 썼던 거.

 

 

Sheet3.Range("B2") = Me.TextBox1.Value          '나 유저폼의(Me) 첫 번째 텍스트 박스(TextBox1)의 값은(Value)

                                                                   Sheet3의 B2 셀에 대입한다.

 

 

이렇게!

 

그러니 새로 열었을 때는 누군가가 이전에 입력했던 데이터를 지우고 시작해야 새로 연 사람이 입력한 새로운 ID 값을 B2 셀에 대입할 수 있겠죠?

 

그래서 보통 VBA 코드 시작 부분에는 이렇게 기존 콘텐츠를 지우는 코드를 많이들 넣으시더라고요.

 

 

아무튼 코드를 추가하고 테스트해 보기 위해서 엑셀 창을 닫았다 다시 열어 볼게요.

 

 

 

그럼 이렇게!

엑셀을 딱 켰을 때 유저폼이 나타납니다.

 

 

여기까지가 강의 내용이었고요. 과제가 있었습니다.

---

[과제]

ID와 PW를 입력할 입력 창을 만들고 사용자가 각각 ID와 PW를 입력했을 때,

ID와 PW가 일치할 때는 "감사합니다." 라는 메시지가 나타나게 한 후에 유저폼을 닫고,

ID와 PW가 일치하지 않을 때는 "ID와 PW를 확인하세요." 라는 메시지가 나타나게 한 후 엑셀 창을 닫아라.

 

 

 

문제가 기니까 더 개어려운 기분 ㅋㅋㅋ 🤯😵

 

제 짧은 열흘 간의 VBA 코딩 경력으로 볼 때 결국은 뭔가를 치기 시작해야 실마리가 풀리더라고요.

(암산처럼 허공에 대고 계산하는 거에 약한 타입 ㅋㅋㅋ)

 

일단 유저폼부터 만들러 가 봅니다.

 

 

 

창 두 대 만들고, 버튼 추가하고.

이런 디자인적 요소에는 강하거든요. 순식간에 했고요.

 

 

이제 엑셀 시트로 가 봐야겠죠.

 

 

 

E6:F8 범위에 ID와 PW 리스트를 넣어 뒀습니다.

그리고 B2 셀과 C2 셀에는 유저폼에 입력된 ID와 PW를 받아올 셀을 만들어 뒀고요.

 

 

 

 

이제부터가 시작입니다.

사용자가 입력한 PW를 받아올 셀(C2 셀) 옆인 D2 셀에

사용자가 입력한 ID(B2 셀)와 매칭되는, 제가 미리 세팅해 둔 실제 PW(F7:F8 범위)를 불러올 셀을 하나 할당해 줍니다.

 

어떻게 불러오느냐?

VLOOKUP 함수를 이용해서 불러올게요.

 

 

=VLOOKUP(B2,E7:F8,2,0)          'B2 셀에 입력된 값을 E7:F8 범위에서 찾아라. 그리고 그 범위의 2번째 열 값을

                                            가져오는데, 정확하게 일치하는 값만 가져와라.

 

 

 

 

그런 다음에는 입력된 PW와 실제 PW가 일치하는지를 체크해야겠죠?

 

B3 셀에서 COUNTIF 함수를 통해 PW의 일치 여부를 체크할 겁니다.

 

=COUNTIF(C2, D2)       'C2와 D2의 값이 일치하는 개수를 카운트하라.

 

즉, 입력된 PW와 실제 PW가 일치하면 B3 값이 1이 될 거고,

일치하지 않으면 0이 되겠죠?

 

 

세팅이 끝났다면 이제 코드를 작성하러 갑니다.

 

유저폼 코드예요.

 

Private Sub CommandButton1_Click()

     Sheet3.Range("B2") = Me.TextBox1.Value
     Sheet3.Range("C2") = Me.TextBox2.Value

     If Sheet3.Range("B3") = 1 Then
          MsgBox "감사합니다."
          Unload UserForm1

     Else
          MsgBox "ID와 PW를 확인하세요."
          ActiveWorkbook.Close False

     End If

End Sub

 

 

시트에 함수 세팅하는 것만 좀 복잡했지 막상 코드는 강의에서 배운 내용에 한 줄 추가한 게 거의 다였네요.

PW 입력 창을 추가했기 때문에 TextBox2에 대한 내용만 추가해 줬습니다.

 

 

코드를 실행해 보면

 

 

 

 

이렇게 로그인 유저폼이 나타나고요.

 

 

 

 

ID를 'aaa'를 입력하고 일치하는 PW인 '111'을 입력해 보면,

"감사합니다." 라는 메시지 박스가 나타납니다.

 

 

 

ID를 'aaa'를 입력하고 틀린 PW인 '222'를 입력하면

"ID와 PW를 확인하세요." 라는 메시지 박스가 나타나는 걸 볼 수 있습니다.

 

 

 

강의는 되게 가볍게 들었는데 막상 정리해 놓으니 내용이 방대하네요. 😂😂

어제 강의 듣고 바로 정리 시작했는데 이틀 걸려서 오늘 아침 강의를 못 들었............

 

미루면 하기 싫을 것 같아 강의 정리 먼저 했어요.

내일 다시 11강 달리러 갑니다!