[액셀 VBA] 자동화 - 직책별로 뽑은 직원들을 폼에 연동하여 수정 조회 하기

작성자
dra****
작성일
2021-06-03 12:18
조회
92

마소 FORMS를 활용한 자동 출퇴근 기록, 근태, 급여산정 등등 프로젝트의 일부입니다.

오늘의 주제는 저번에 DB로 직책별로 뽑아낸 DB를 활용하여 리스트박스에 해당 직원들만 넣고, 수정 조회하는 과정입니다.

  1. 직원들 DB 시트가 있고, 1행에는 필드명이 있습니다. 이 필드명과 나중에 폼에 있는 컨트롤들을 같게 해 줄 것입니다.

 

2. 이것을 가지고 직원들을 직책별로 뽑아냅니다. 이 부분은 어떻게 구현했는지 아래 게시글을 보시면 찾으실 수 있을 것입니다.

아래 사진에서 각 테이블마다 표 이름을 "CASHIER", "DINING", "DISHWASHER", "KITCHEN" .. 이런식으로 이름 정의를 합니다.

 

3. 직책을 참조할 수 있는 테이블을 하나 만들고 이름을 "POSITION"으로 정의합니다.

 

4. 폼에 컴보박스의 속성에 있는 RowSource의 값을 POSITION으로 설정합니다.

그러면 아래의 그림 같이 해당 직책들이 나올 것입니다.

 

5. 위의 그림에서 직책을 선택하면 바로 밑에 있는 리스트 박스에 해당 직책을 가진 직원 목록이 뜨도록 해야 겠지요.

Private Sub cboPos_Change()
   Me.lboxEmployees.RowSource = "[" & wbYS.Name & "]SCHEMA!" & Me.cboPos.Value
End Sub

a. "cboPos"는 포지션을 담아 두기 위한 콤보박스 컨트롤 입니다.

b. "lboxEmployees"는 해당 직원 명단을 담아 두기 위한 리스트박스 컨트롤입니다.

c. "[" & wbYS.Name & "]SCHEMA!"  : 워크북이 여러개 실행 되고 있는 중이기에 명확하게 "[워크북 이름]워크시트 이름!"을 앞에 붙여 줍니다. 실제 시트의 이름은 SCHEMA이고 이 뒤에 선택한 콤보박스의 값 "Me.cboPos.Value"값을 붙여 주면 리스트 박스에 해당 직원들의 이름를 아래의 그림같이 가져와 붙여 줍니다.

6. 위 그림의 리스트박스에서 직원을 선택하면 이 직원들의 정보를 가져오는데

a. 직원시트에서 해당 직원을 찾아 폼에 뿌려주기 ▶ 조회만 가능, 업데이트 기능을 따로 넣어 주어야 함

b. 직원시트에 있는 각각의 셀의 주소를 ControlSource에 연동시키기 ▶ 조회 및 바로 수정 가능

목적에 따라 a, b 중에 선택하시면 됩니다.

 

7. 위의 6.b.의 방식, ControlSource로 연동하는 방법의 코딩을 보여 드리겠습니다.

a. 먼저, 폼에 있는 TextBox의 이름을 필드명과 동일하게 합니다. 가령 직원 ID 필드명을 여기서는 "EID"라 명명했습니다.

그럼 폼에 있는 TextBox 중 직원의 ID를 보여줄 녀석의 이름을 EID라고 하면 됩니다. 아래 그림 참조

 

b. 모든 컨트롤의 이름을 필드명과 같게 변경합니다.

c. 리스트박스에서 선택시 직원의 아이디를 추출합니다.

리스트박스에서 보이는 값은 이름 뿐이지만 ColumnCount=3, BoundColumn=1로

그리고 이름만 보이게 ColumnWith = "0pt; 120pt; 0pt" 이렇게 세팅이 되어 있습니다.

위에 직책별 명단의 필드를 보시면 "EID", "FirstName", "SRATE" 이렇게 세 개의 값들이 있습니다.

따라서 직원을 선택하면 BoundColumn=1 이기 때문에 아래코드 Me.lboxEmployees.Value에 직원 ID인 EID값이 넘어오게 됩니다.

 

Private Sub lboxEmployees_Click()
   EMP.EID = Me.lboxEmployees.Value
   If EMP.EID <> vbNullString Then EMP_ConnectPersonalInfoToForm
End Sub

 

d. 직원 개인 정보를 담을 Type형 전역변수로 EMP를 만들어 두고 그것에 EID값을 찾아 직원시트에서 해당 직원을 찾습니다.

전역변수로 만들었다는 말은 이 프로젝트 전체에서 EMP를 쓰겠다는 의도입니다.

d.1. EMP_GetEmpRow()함수로 해당 직원을 찾음 못찾으면 0을 반환하여 종료함

d.2. EMP_SetEmpCellAddressToDictionary()함수로 해당 직원의 정보를 Dictionary에 담고 각 컨트를들의 ControlSource="셀주소" 를 설정함. 끝나면 True를 반환함

Public Function EMP_ConnectPersonalInfoToForm() As Boolean
   RowEmp = EMP_GetEmpRow(EMP.EID)     '해당 직원의 행 번호를 찾아 반환
   If RowEmp = 0 Then Exit Function
 
   If EMP_SetEmpCellAddressToDictionary = False Then    '클릭한 직원 행 번호 가져오기. RowEmp는 전역변수기 때문에 매개변수로 넘길 필요 없다.
      EMP_ConnectPersonalInfoToForm = False
      Exit Function
   End If
End Function
 
'클릭한 직원 행 번호 찾아 반고 Dictonary에 셀주소 담기
Public Function EMP_GetEmpRow(EID As String, Optional Col As String = "A") As Long
   Dim RowLast    As Long
   Dim rng        As Range
 
   With wsYS.wsEmp
      RowLast = sh.GetLastRow(Col, wsYS.wsEmp)
      Set rng = .Range("A2:A" & RowLast).Find(What:=EID)      '여기가 직원의 EID로 행 번호를 찾는 부분입니다.
 
      If rng Is Nothing Then
         MsgBox "EID doesn't exist. Check this employees record and try again.", vbInformation
         EMP_GetEmpRow = 0
         Exit Function
      End If
 
      EMP_GetEmpRow = rng.Row
   End With
End Function
'찾은 행번호로 Dictonary에 셀주소 담고 컨트롤에 연동시킴
Public Function EMP_SetEmpCellAddressToDictionary() As Boolean
 
   Dim ColLast As Long
   Dim i As Long
   Dim key As String
   Dim addr As String
   ColLast = sh.GetLastColumn(1, wsYS.wsEmp)  '◀ 마지막 행이나 열 갖은 값을 찾기 위해 만들어 둔 클래스 모듈
 
   dtEmp.RemoveAll
 
   With wsYS.wsEmp
      For i = 1 To ColLast
         key = .Cells(1, i).Value    '◀ 여기에 필드명을 담아 둡니다.
         addr = "[" & wbYS.Name & "]EMPLOYEES!" & .Cells(RowEmp, i).Address '◀ 여기에 셀 주소값을 담아 둠
         On Error Resume Next
            frmEmp.Controls(key).ControlSource = addr     '◀ 이 부분이 해당 폼 컨트를에 각각의 셀을 연동 시켜주는 부분
         On Error GoTo 0                                           '◀ 필드명과 컨트롤의 이름이 같기에 쉽게 루프로 연동 가능
      Next i
   End With
 
   EMP_SetEmpCellAddressToDictionary = True
End Function

 

e. 이제 연동이 끝났습니다. 컴보박스에서 직책을 선택하면 알아서 그 명단을 가져오고, 직원을 선택하면 직원시트에 있는 셀로 알아서 연동해 줄 것입니다. 이러면 직원 조회 및 수정 폼이 끝이 난 것입니다.

Dictioanry에 정보를 담아 둔 이유는 다른 곳에 쓸 용도가 있기 때문입니다.

연동만 하고자 할 때는 굳이 Dictionary에 담아 둘 필요가 없습니다.

 

코딩 팁) 여러개의 워크북과 많은 워크시트를이 동시에 실행되도 있는 상황이면 어느 워크북의 워크시트인지 명확하세 해 두어여 원하는 결과를 처리할 수 있습니다. 하도 에러 경험을 많이 당해서....

그래서 제가 생각한 해결책은

Public wbYS As Workbook               '워크불 이름 명명
 
Public Type pwsYS                         '위의 위크복에 속해 있는 워크시트들
   wsEmp          As Worksheet
   wsSCM          As Worksheet
   wsRH           As Worksheet
   wsSF1          As Worksheet
   wsSF2          As Worksheet
   wsTemp         As Worksheet
   attPasteArea   As Range
End Type
 
Public wsYS As pwsYS                     '워크시트들을 접근할 수 있게 하는 전역변수

위와 같이 사용자 정의 변수(TYPE)을 이용하여 워크시트를 접근합니다. 그리고 폼 실행시 세팅하는 함수 하나 추가

저는 클래스로 자동 세팅하게 해 놓았습니다.

Private Sub SetWorkbooksAndSheets()
   If sh Is Nothing Then Set sh = New clsSheets
   If xlDb Is Nothing Then Set xlDb = New clsXLDataBase
   If DT Is Nothing Then Set DT = New clsDate
   If cUtil Is Nothing Then Set cUtil = New clsControlUtil
 
   Set wbYS = ThisWorkbook
 
   With wsYS
      Set .wsSCM = wbYS.Worksheets("SCHEMA")
      Set .wsEmp = wbYS.Worksheets("EMPLOYEES")
      Set .wsRH = wbYS.Worksheets("Regular Holidays")
      Set .wsSF1 = wbYS.Worksheets("SalaryFormat1")
      Set .wsSF2 = wbYS.Worksheets("SalaryFormat2")
      Set .wsTemp = wbYS.Worksheets("TEMP")
      Set .attPasteArea = .wsTemp.Range("A2")
   End With   
End Sub

각각의 워크북들과 워크시트를 이런식으로 해놓으면, 코드 입력시 아래와 같이 뜨기 때문에 편합니다.

큰 프로젝트를 하다보면 Activesheet, Thisworkbook, ActiveCell, Worksheets("Sheet1") 같은 것을 쓰는 것이 좋지 않다는 것을 아시게 될 겁니다. 의도하지 않은 곳에서 혼자 자동화하고 있을 수도 있습니다.

 

회원등급 : 열매3단계
포인트 : 1564 EP
전체 0

전체 409
번호 제목 작성자 작성일 추천 조회
365
New 위캔두 관련 질문 (1)
빤스 | 2021.06.23 | 추천 0 | 조회 41
빤스 2021.06.23 0 41
364
New [엑셀 VBA] 모든(?) 색상 값과 VBA로 적용하는 방법 첨부파일 (2)
dra**** | 2021.06.23 | 추천 2 | 조회 28
dra**** 2021.06.23 2 28
363
New 혹시 자료를 한번에 보기 편하게끔 만든 예제 파일이 있을까요 ? (1)
크래용 | 2021.06.23 | 추천 0 | 조회 21
크래용 2021.06.23 0 21
362
안녕하세요, 위캔두 회원인데.. (5)
슝슝 | 2021.06.22 | 추천 0 | 조회 34
슝슝 2021.06.22 0 34
361
마소의 헛발질... (2)
dra**** | 2021.06.20 | 추천 0 | 조회 48
dra**** 2021.06.20 0 48
360
엑셀의 끝은 과연 어디인가? (5)
꾸루꾸루 | 2021.06.17 | 추천 9 | 조회 100
꾸루꾸루 2021.06.17 9 100
359
[엑셀 VBA] Naming Convention - 변수나 개체 이름 붙이는 관행 (4)
dra**** | 2021.06.14 | 추천 2 | 조회 52
dra**** 2021.06.14 2 52
358
[엑셀 VBA] 자동화 - FORMS를 이용한 직원 근무시간과 급여 계산하기
dra**** | 2021.06.13 | 추천 3 | 조회 79
dra**** 2021.06.13 3 79
357
고맙습니다 (4)
가자 ! 하자!! 아자!!! | 2021.06.13 | 추천 2 | 조회 56
가자 ! 하자!! 아자!!! 2021.06.13 2 56
356
방가워요 (1)
dfgh**** | 2021.06.11 | 추천 1 | 조회 35
dfgh**** 2021.06.11 1 35
355
[엑셀 VBA] 자동화 - 데이터 관리의 기본과 고급필터의 유용성
dra**** | 2021.06.10 | 추천 3 | 조회 80
dra**** 2021.06.10 3 80
354
방문 상담 요청 방법?
dr**** | 2021.06.10 | 추천 0 | 조회 48
dr**** 2021.06.10 0 48
353
모두 오빠두엑셀 덕분입니다. (2)
DollShe365 | 2021.06.10 | 추천 6 | 조회 109
DollShe365 2021.06.10 6 109
352
[APP] 심플한 날짜 계산기 첨부파일 (3)
dra**** | 2021.06.09 | 추천 1 | 조회 67
dra**** 2021.06.09 1 67
351
빌게이츠가 한글을 사랑한다는 말이 사실일까? 엑셀에서 확인
dra**** | 2021.06.08 | 추천 0 | 조회 67
dra**** 2021.06.08 0 67
350
엑린이(?)입니다 (3)
투덜이스머프 | 2021.06.06 | 추천 4 | 조회 68
투덜이스머프 2021.06.06 4 68
349
[마소 FORMS] 무료로 마소 서버를 이용하여 필요한 정보 수집하기 - 매우 쉬움 (3)
dra**** | 2021.06.06 | 추천 0 | 조회 58
dra**** 2021.06.06 0 58
348
[액셀 VBA] 자동화 - 직책별로 뽑은 직원들을 폼에 연동하여 수정 조회 하기
dra**** | 2021.06.03 | 추천 1 | 조회 92
dra**** 2021.06.03 1 92
347
이제야 알게 되어 아쉽다. (6)
wshm**** | 2021.06.03 | 추천 8 | 조회 106
wshm**** 2021.06.03 8 106
346
Q&A 채택하고 싶은데 어떤식으로 채택해야 하나요? (4)
0328 | 2021.06.01 | 추천 0 | 조회 52
0328 2021.06.01 0 52
345
벽돌쌓기 첨부파일 (6)
눈사람 | 2021.06.01 | 추천 6 | 조회 89
눈사람 2021.06.01 6 89
344
[엑셀 VBA] 자동화 - ONEDRIVE를 서버로 활용하기 - 화상영어 회사 자동화 사례 (3)
dra**** | 2021.05.30 | 추천 2 | 조회 95
dra**** 2021.05.30 2 95
343
[엑셀 함수] WEEKNUM()으로 구한 몇 주차로 다시 날짜 구하기
dra**** | 2021.05.29 | 추천 0 | 조회 60
dra**** 2021.05.29 0 60
342
[엑셀 VBA] 자동화 - 1. 데이터 입력, 2. 양식에 자동으로 대입 3. 이메일 자동 발송 (2)
dra**** | 2021.05.28 | 추천 1 | 조회 115
dra**** 2021.05.28 1 115
341
VBA 이메일 자동발송 개별발송 질문드립니다 (4)
소람 | 2021.05.27 | 추천 1 | 조회 73
소람 2021.05.27 1 73