사용자별 작업영역 제한하기

Step 1: 강좌 파일 실행 시 에러가 난다면

그동안 어디갔다 오셨는지 이제서야 Exceller의 홈을 찾으시는 분이 많으시군요. ^^ 그래도 다행입니다. 대개의 분들은, "어휴, 이 많은 걸 언제다본담. 어디 요약판 없나요?" 하시는데 이 분은 아주 긍적적으로 생각 하시는 분인것 같습니다.

Exceller의 강좌 파일을 실행시키는데 에러가 나는 분은 먼저, VB Editor 창에서 도구 - 옵션 메뉴를 선택하신 다음, Microsoft DAO X.XX Object Library 항목에 체크가 되어있나 확인해 보시기 바랍니다.

나중에 Database와 관련된 강좌를 하게 되면 본격적으로 사용하게 될 것입니다만 DAO라는 것은 Data Access Object programming interface의 약자로, 자신의 프로그램을 다른 사람과 함께 사용할 수 있도록 하거나 데이터베이스를 이용하는 프로그램을 용이하게 접근하고 사용할 수 있도록 해 주는 것입니다.

DAO에는 여러 가지 버전이 있는데

• DAO 2.5: 16Bit 시스템, 즉 윈도우 3.1에서 작동
• DAO 3.0, 3.5: 32Bit 시스템(윈도우 95나 98)에서 작동
• DAO 2.5/3.0 Compatibility Library: 16Bit와 32Bit에서 함께 작동

이렇게 구분할 수 있습니다. 따라서 대개의 경우, ③ DAO 2.5/3.0… 항목을 선택하시면 되겠습니다.

Step 2: 사용자별로 작업 공간 나누기

본론으로 들어가서…

쉽지 않은 질문을 주셨군요. ^^; 질문만으로는 정확히 어떤 상황인지 알 수 없습니다만, 일단은 질문하신 분의 PC가 다른 PC들과 네트워킹이 되어있고, 통합문서를 공유해서 사용한다고 가정을 하고 접근을 하도록 하지요.

Exceller에게 메일로 질문 파일을 보내는 분들은 자신의 E-mail 주소를 질문파일 안에도 반드시 기재를 해 주시기 바랍니다.

아마 파일을 열었을 때, Workspace 시트가 활성화 되면서 셀 포인터가 색상이 입혀진 특정 영역 내에서만 움직이는 것을 보셨을 것입니다. "그게 먼 소리야?" 하는 분들은 Workspace 시트로 가서 셀 포인터를 이리저리 움직여 보세요. 아마 회색(또는 빨간색) 셀들 내에서만 마음대로 움직일 수 있을 것입니다.

Step 3: API 함수 알아보기

Const strPassword = "excel"
#If VBA7 Then
    Private Declare PtrSafe Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
        (ByVal lpBuffer As String, nSize As Long) As Long
#Else
    Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
        (ByVal lpBuffer As String, nSize As Long) As Long
#End If
'//보통 변수를 선언할 때 Dim문을 사용합니다(동적 변수를 선언할 때에는 Static문을
'//사용하지만…). 그런데 변수가 아닌 상수를 선언할 때에는 Const문을 사용합니다.
'//그런데 그 밑에 보니까 Private Declare Function… 하는 아주 괴이한(?) 것들이 있지요?
'//이것은 API(Application Programming Interface)라고 하는 좀 특수한 함수를 사용한
'//것입니다.

'//API라는 것은 한마디로 "함수들을 모아놓은 것"이라고 생각하시면 됩니다.
'//함수는 함수인데 EXCEL이나 VBA에서 Control 할 수 없는 특수한 작업을 수행하는
'//함수들만을 한데 모아놓은 것입니다.

'//VB0055, 56 강좌에서 "하위 폴더 검색하기"에 대해 설명을 드렸는데 이 때 어떤 폴더를
'//선택할 지 탐색창을 띄운 상태에서 사용자는 마우스로 대상 폴더를 클릭한 하면 되도록
'//해 준 부분이 있었지요? 이 때 탐색창을 화면에 띄울 때 바로 이 API 함수의 도움을 받은
'//것입니다.

'//API는 시스템의 환경이 16비트이면 Win16 API, 32비트이면 Win32 API를 사용하며,
'//1500개가 넘는 함수들이 갖추어져 있습니다. 이 중에서 자신이 원하는 것을 선택해서
'//사용하면 되는 것입니다. API 함수에 대한 자세한 설명은 관련 책자를 참고하시기
'//바랍니다. 혹시 Win32API.txt라는 파일이 있는 분은 이것을 살펴보셔도 됩니다.
'//텍스트 파일인데도 엄청 큽니다. 646KB군요.

'//여러분의 PC에서 Windows 폴더나 Windows\System 폴더를 살펴보시면 확장자가
'//DLL인 파일들이 엄청 많이 있을 것입니다.
'//이것은 Dynamic Link Library(동적연결 라이브러리) 파일이라고 하는 것으로 VB나 C, C++
'//등의 랭귀지로 작성합니다. 즉 API 함수의 성질머리와 기본 문법만 알면 다른 랭귀지로
'//작성한 DLL 파일도 VBA 상에서 불러다가 일을 시켜먹을 수 있다는 얘기입니다.

'//직속 졸병이 아니라 외부에서 용병을 고용해서 일을 시켜야 하니까 아무래도 초창기에는
'//서로간의 업무 스타일도 틀리고 말도 잘 안통하겠지요? 그래서 위와 같이 좀 복잡해 보이는
'//명령을 내린다고 생각하시기 바랍니다.

Declare Function 함수명 Lib "라이브러리명" Alias "별명" (구성요소) As 리턴 타입

• ① 함수명: 자신이 사용하고자 하는 함수이름
• ② 라이브러리명: API 함수가 들어있는 DLL 파일의 이름
• ③ 별명: API 함수의 별명
• ④ 구성요소: API 함수의 argument
• ⑤ 리턴 타입: 보통 함수를 사용할 때와 마찬가지로 API 함수의 결과값 유형

Step 4: 로그온한 사용자 찾기

이러한 사전 지식을 가지고 본론으로 돌아와서… 사용자에 따라 서로다른 작업공간을 배정해 주려면 먼저, 로그온 한 사용자가 누구인지를 알아야 겠지요? 아래 버튼을 누르세요.

누가 로그온 하였는지 찾아내었지요? 코드를 보면…

Sub WhosLogOn()
    Dim lpbuff As String * 20
    Dim lngret As Long
    Dim strLogOnName As String
    Dim Msg As String
    '//변수를 선언합니다. 여기서 lpbuff는 로그온 사용자의 이름을 저장하기 위한 버퍼로
    '//크기를 20Byte로 미리 지정해 두었습니다. 꼭 20으로 할 필요는 없습니다. 그냥 적당한
    '//크기를 준 것입니다. 20자 이상되는 이름을 사용하는 사람은 잘 없을 것이므로…

    lngret = GetUserName(lpbuff, 20)
    strLogOnName = Left(lpbuff, InStr(lpbuff, Chr(0)) - 1)
    '//GetUserName이라는 API 함수를 실행시킨 다음, 사용자 이름을 추출합니다.

    Msg = "LogOn한 사용자의 이름은 " & vbCr & vbCr
    Msg = Msg & Trim(strLogOnName) & " 입니다"
    MsgBox Msg, , "LogOn Name//By Exceller"
    '//추출한 사용자 이름 중에서 문자열 앞뒤의 공란을 제거한 다음 MsgBox에 뿌려줍니다.

End Sub

Step 5: auto_Open 프로시저

이것을 조금만 응용하면 위의 질문하신 분이 원하는 것을 만들 수 있겠지요? 숙제로 내 드릴까… 하다가 에라~ 내친 김에 후딱 해치워 버리지요 뭐! ^^ 파일을 열 때 로그온 Name을 체크해야 하니까 auto_open 프로시저에 코드를 작성하시면 됩니다. Workbook_open 프로시저에 작성을 해도 됩니다.

Sub auto_Open()
    Dim strUserName As String
    Dim Msg As String
    Dim shtSheet As Worksheet
    Set shtSheet = Sheets("Workspace")

    Msg = "이것은 사용자의 로그온 Name에 따라 작업공간을 " & vbCr
    Msg = Msg & "다르게 설정하는 방법에 대한 예제입니다."
    MsgBox Msg, , "사용자별 작업영역 제한하기//By Exceller"

    ThisWorkbook.Unprotect (strPassword)
    shtSheet.Unprotect (strPassword)
    '//워크북과 워크시트에 패스워드가 걸려있으면 해제합니다. strPasword라는 변수에는
    '//어떤 값이 들어 있을까요? 잘 생각해 보세요. 모르는 분은 모듈시트이 맨 처음을… ^^

    strUserName = FindOutUserName
    '//FindOutUserName이라는 사용자 정의 함수를 실행시키고 그 결과값을 strUserName에
    '//담습니다.

    shtSheet.Select
    MsgBox Trim(strUserName) & "님 환영합니다!", , "환영합니다//By Exceller"

    '//아래 부분은 strUserName이라는 변수에 어떤 값이 담겨져 있느냐에 따라 Select Case
    '//문을 사용하여 분기처리를 해 준 것입니다. 즉 사용자에 따라서 작업공간을 다르게
    '//할당해 주는 것이지요.
    With shtSheet
        Select Case strUserName
            Case "권현욱"
                .Cells.Interior.ColorIndex = xlNone
                .ScrollArea = "A2:F15"
                .Range("a2:f15").Interior.ColorIndex = 15
            Case "아무개"
                .Cells.Interior.ColorIndex = xlNone
                .ScrollArea = "c3:G20"
                .Range("c3:g20").Interior.ColorIndex = 3
            Case Else
                .Cells.Interior.ColorIndex = xlNone
                .ScrollArea = "B2:G15"
                .Range("b2:g15").Interior.ColorIndex = 6
        End Select
    End With
    shtSheet.Protect (strPassword)
    ThisWorkbook.Protect (strPassword)
    '//작업이 끝났으면 워크시트와 워크북에 암호를 걸어 둡니다.

End Sub

Step 6: FindOutUserName 함수

위 프로시저에서 호출한 FindOutUserName 함수의 내용을 살펴보지요.

Function FindOutUserName() As String
    Dim lpbuff As String * 12
    Dim lngret As Long
    Dim strUserName As String
    On Error GoTo ET
    '//변수를 선언할 때 버퍼로 사용할 문자열 변수의 크기를 지정해 준 것에 주의!!!
    '//만약 에러가 발생했을 경우를 대비, ET라는 에러 트래핑 구문을 준비해 둡니다.
    '//나머지 아래 부분은 위에서 설명드린 것과 거의 같은 것입니다.

    lngret = GetUserName(lpbuff, 12)
    strUserName = Left(lpbuff, InStr(lpbuff, Chr(0)) - 1)
    FindOutUserName = Trim(strUserName)
    Exit Function
ET:
    FindOutUserName = ""
End Function

마무리

또 정신없이 떠들다… 아니 치다보니 한회 분량치고는 좀 많은 듯 합니다. 알아두면 다 피가 되고 살이 되는 내용들이니까 잘 익혀 두시기 바랍니다.

다음 시간에…

정리 — 사용자별 영역 핵심

자주 묻는 질문 (FAQ)

마치며

API 함수로 로그온 사용자를 알아내면 사용자마다 다른 작업 영역을 배정할 수 있습니다.