4월 1 2015
엑셀(EXCEL) : 공유화일을 IP, 사용자 체크하여 열기, 편집 여부 및 로그인, 아웃 기록 보기(관리자)
logon-ip-user-check <—- 첨부 화일
여러분의 성원?에 힘입어 사실 아래 팁의 댓글에 요청 사항이 있어 작성을 해 보았습니다.
아래의 팁은 순수 엑셀 화일을 보안 상태로 만드는 팁이었다면 지금부터 게시하는 내용은
그동안 공유문서를 통해 업무를 진행하시던 분들의 고충 내용을 조금 쉽게 해 주는 팁입니다.
앞에 팁은 하드디스크의 시리얼을 체크하여 유출된 보안 엑셀 파일의 실행을 방지하는 것이나
이것은 사무실에서도 혹은 유출된 화일도 내부 매크로를 알지 못하는 이상 실행을 할 수 없게
만드는 방법입니다. 저는 공유 화일을 쓰지 않고 그냥 내부 DB를 이용해서 프로그램을 만들어
자료정리, 프로그램을 배포하고 있었기에 이런 유형이 필요한지를 사실 잘 모르고 있었습니다.
자! 시작합니다.
첨부된 logon_check화일을 실행하면 개인 사용자가 Windows로 logon 사용자명을 보여줍니다.
우선 관리자는 첨부된 엑셀 화일의 VBA코드를 열어 공유하고 있는 사용자명을 등록해 줍니다.
관리자는 전체 화일과 어떤 사용자들이 언제 어디서 로그온했는지의 정보도 볼 수 있게 하고
일반 사용자는 사용자가 작성한 시트만 편집할 수 있고 다른 시트는 보호되어 볼 수만 있습니다.
시트의 이름은 “시트이름-logon명”으로 하셔야 합니다. 시트명 체크 코드가 정형화 되어야만
체크될 수 있을 것 같아 이렇게 정의해서 코드를 만들었습니다. 코드를 이해하시면 더욱 더
유용한 방법을 사용하여 체크하실 수 있을 것 같습니다.
첨부된 logon_check 실행 프로그램이 의심스러우신 분은 아래 내용대로 진행하시면 됩니다.
logon_check는 아래와 같습니다. VBA 편집기에서 참조> Mircosoft Script Runtime을 체크
ThisWorkbook을 더블 클릭해서 아래 코드를 복사, VBA코드에 넣어서 실행하시면 됩니다.
그러면 메시지 박스로 사용자를 보여주므로 이것을 수집해서 Case 문에 추가하시면 됩니다.
<Code>
Option Explicit
Private Declare Function GetUserName Lib “advapi32.dll” Alias “GetUserNameA” _
(ByVal lpBuffer As String, nSize As Long) As Long
Public strUserName, loginip As String
Function FindOutUserName() As String
Dim lpbuff As String * 12
Dim lngret As Long
Dim strUserName As String
On Error GoTo ET
lngret = GetUserName(lpbuff, 12)
strUserName = Left(lpbuff, InStr(lpbuff, Chr(0)) – 1)
FindOutUserName = Trim(strUserName)
Exit Function
ET:
FindOutUserName = “”
End Function
Private Sub Workbook_Open()
Dim username As String
username = FindOutUserName
MsgBox “사용자 이름은 ” & username & ” 입니다”
End Sub
</Code>
엑셀 화일은 VBA초급자라도 알 수 있게 최대한 자세하게 주석을 달아 놓았습니다.
조금 더 응용하셔서 좋은 내용으로 업무나 개인 사용자에 도움이 되었으면 합니다.
버그가 있을 수 있으니 버그가 발생되면 연락 바랍니다. 나름 많이 테스트했는데…
물론 매크로 실행시에 암호를 묻는 대화 상자를 띄어 한번 더 보안을 하면 좋겠지만
매번 등록된 사용자가 더 번거러워 지는 것 같아서 넣지를 않았지만 필요하시면
조금만 수정해서 사용하시면 될 것 같아요. 코드를 조금만이라도 해석할 수 있으면
금방 적용하실 수 있다고 봅니다.
<중요>
앞의 팁 댓글에서의 조언처럼 검토 > 통합문서 보호와 공유에 암호를 걸어야하고
VBA편집기에서 VBA Project속성에서도 보안에 암호를 걸어야만 정상 동작합나다.
사용자의 편의를 위해서 두 부분의 암호를 임의로 해제한 상태로 첨부해 둡니다.
4월 2 2015
동적이름정의- UserForm-Multi Column Listbox활용하여 리스트를 양식에 맞춰 넣기
요새 제가 많이 바쁜가 봅니다? 항상 바쁘면 더 엉뚱한 일을 더 하고 싶거든요.
첨부 화일
20150402-동적이름정의- UserForm-Multi Column Listbox활용하여 리스트를 양식에 맞춰 넣기
엑셀을 이용하는 목적이 대부분은 숫자를 다루는 일에서 출발을 합니다. 그래서 초급자분들은
아주 넓고 편한 계산기에서 출발해서 나중에는 표다루기가 무지 편한 워드로 발전을 합니다. ㅋㅋㅋ
엑셀은 원래 목적인 숫자를 다루기도 수월하지만 사실을 데이터를 다룬다고 보는 것이 좋을 듯 합니다.
그래서 그 데이터를 다룰 목적으로 단가나 기타 제품 이력 관리를 하기도 하지요. 그런데 이 제품들의
가격들이 환율이나 기타 원가 변동 등으로 수시로 업데이트가 되고 제품들이 첨삭되기도 하면 사용자가
일일이 원가를 DB에서 계산해서 Excel로 내려 받으면 아래로 긴 리스트로 받게 되는 경우가 많죠.
몇 가지 제품들을 다루고 계신다면 그냥 수작업으로 긴 리스트 형식에서 정형화된 양식에 입력하면
되지만 저희 회사같이 수천종을 생산하면 얘기가 달라집니다. 누가 농담처럼 LG생활건강의 제품을
다 외우면 임원시켜 준다고 하는데 저희 회사도 마찬가지입니다?. ㅠㅠ 특히 단가 승인 관련 보고시
자료에 아래로 죽~ 긴 리스트보다 정형화된 폼에 만들어 올리는 것도 보기가 좋고 종이도 절약!
즉 Vlookup등이나 찾기 함수를 넣어 리스트와 양식을 분리해서 리스트에만 입력하면 자동으로
찿기를 할 수도 있지만 제품이 추가되거나 삭제되면 보기싫은 #N/A 등이 나타날 수 있으므로
물론 코드 처리를 해서 삭제된 제품은 처리 가능하지만 추가된 부분은 또 수작업이 필요함으로
특히 제품리스트를 정렬해서 바로바로 볼 수 있게 하려면 찾기 함수로는 어려운 부분이 많음.
서두가 길어졌습니다.
요약하면 동적이름을 정의하여 제품의 첨삭이 일어나더라도 자동으로 제품의 리스트 영역을 선택하고
UserForm, Multi Column Listbox를 사용 동적이름으로 정의된 리스트를 가져오고 그것을 지정된
(양식) 시트에 뿌려서 종이도 절약하고 깔끔하게 만들어 프린터하고 가지고 다니면서 볼 수 있습니다.
단가표나 다른 형태의 반복적인 작업을 할 때 아주 유용한 방법이 될 수 있을 것으로 보입니다.
1. 동적 이름 정의
=OFFSET(대상 셀, 행 방향 이동 수, 열 방향 이동 수, 행 방향 취할 값, 열 방향 취할 값)
응용법 (아래 Data!딸라A딸라2,입니다. 이상하게 보이는군요)
=OFFSET(Data!$A$2,0,0,COUNTA(Data!$A:$A),3)
설명을 드리자면 $A$2에서 0행 0열을 움직여서 A열을 데이터가 들어있는 부분만 선택
즉 그 행까지 취하고 $A$2에서 3열의 영역을 취해서 동적 이름으로 정의하는 것입니다.
데이터가 아래로 추가되면 카운터가 올라가고, 삭제되면 줄어 들어 자동으로 영역 선택
엑셀 2007기준
수식 > “이름 관리자” 나 “이름 정의”를 선택해서 이름을 정의하고 참조 대상에 저 함수를
넣어 단가나 기타 데이터를 선택합니다. 그러면 저 영역 A열에 데이터가 추가되면 자동으로
Count가 늘어나서 영역이 넓어지고 삭제되면 줄어드는 원리입니다.
2. VBA 활용
개발도구 > Visual Basic을 선택하고 삽입 > 사용자 정의폼 을 선택해서 UserForm 추가
도구 상자가 보이지 않으면 보기 > 도구 상자를 선택
목록 상자를 선택해서 UserForm에 하나를 만들고 선택, 속성창에서 RowSource에
아까 동적 이름으로 정의된 이름을 입력함, Column Count 에 3을 입력
명령 단추를 하나 만들고 아래 코드를 삽입
Private Sub CommandButton1_Click()
Dim i, j, k, row, unit As Integer
i = 0 ‘ list순서
j = 0 ‘ 행위치
k = 0 ‘ 단위 조직 위치
row = 0 ‘ 단위표의 데이터 위치
unit = 50 ‘ 단위표당 데이터 갯수
‘ 빨리 재배치를 할 경우, 채워지는 애니메이션을 보고 싶은 경우 주석 처리
Application.ScreenUpdating = False
‘ 데이터 숫자 카운터, 제목이 있어서 A2부터 선택
Sheets(“Data”).Select
row_cnt = Application.CountA(Range(“A2”, Range(“A2”).End(xlDown)))
‘ 출력된 양식 시트 선택
Sheets(“PrtForm”).Select
‘ 기존 데이터 지우기, 양식은 보존
Range(“A1:HH55”).ClearContents
‘ 핵심 코드, 돌머리 굴리느라 힘들었어요 엉엉엉
With ListBox1
For i = 0 To .ListCount – 1
For row = 0 To row_cnt Step unit
Select Case i
Case Is < unit * (i + 1)
‘ 단위표의 크기에 따라 몫을 구해서 표의 위치 결정
k = i \ unit
‘ 단위표의 몫을 구해 한 단위표의 위치를 정함
j = i – (unit * (i \ unit))
End Select
Next row
‘ 제목행 반복 삽입
‘ 기존 데이터 지우는 코드를 A1을 A2로 바꾸면 되는데 돌머리 또 엉엉엉
‘ 데이터 열이 늘어나면 지속적으로 추가
‘ 물론 k * 4의 4도 변경해야 함. 양식에 빈 열이 하나 넣어 보기 좋게 함
Cells(1, 1 + k * 4) = “코 드 ”
Cells(1, 2 + k * 4) = “제품명”
Cells(1, 3 + k * 4) = “단 가”
‘ Cells(1, 4 + k * 4) = “비 고”
‘ 데이터 채우기
‘ 제목행이 있으므로 2행 부터 추가됨
Cells(2 + j, 1 + k * 4) = .List(i)
Cells(2 + j, 2 + k * 4) = .List(i, 1)
Cells(2 + j, 3 + k * 4) = .List(i, 2)
‘ Cells(2 + j, 3 + k * 4) = .List(i, 3)
Next i
End With
‘ 이 앞에 까지는 눈에 보이지 않음. 완료된 시트 업데이트해서 보기
Application.ScreenUpdating = True
Unload UserForm1
End Sub
3. Data 시트 선택, 개발도구에서 삽입, ActiveX 컨트롤-> 명령 단추 선택해서 삽입
디자인 모드 선택 > 명령 단추 더블 클릭해서 아래 코드 삽입
Private Sub CommandButton1_Click()
Load UserForm1
UserForm1.Show
End Sub
팁과 강좌 게시판에 글을 올리다 보니 재미있어서 실생활에 활용될 수 있는 자료들을 찿아
예전에 만들어 두었던 자료를 수정해서 올리고 있는데 회사생활에 도움이 되었으면 합니다.
By vinipapa • 무른모 • 0 • Tags: 리스트정리, 멀티컬럼리스트, 양식, 엑셀