matrix 엑셀 보고서의 모듈(GS)

[무영객(여인환)]

엑셀 보고서의 모듈

모듈명 : m_API

'=======================================================
' Data 새로고침
' 보고서 명, 시트명, wait flag
'=======================================================
'
' 저장 프로시저 실행, 또는 강제 조회를 위한 데이터 Refresh
'
Public Sub Refresh_Sheets(SheetNameList As String)

    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' SheetNameList             콤마로 구분된 Refresh할 대상 워크시트 리스트
    '
    ' ======================================================================
   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If
   
    Application.Run GS_BP_Common & "!Refresh_Sheets", SheetNameList

End Sub

'=======================================================
' Data 새로고침
' 보고서 명, 시트명, wait flag
'=======================================================
'
' 저장 프로시저 실행, 또는 강제 조회를 위한 데이터 Refresh
'
Public Sub Refresh_Data(RefreshSheet As Worksheet)

    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' RefreshSheet              Refresh할 대상 워크시트
    '
    ' ======================================================================
   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If
   
    Application.Run GS_BP_Common & "!Refresh_Data", RefreshSheet

End Sub

'=======================================================
' Dataset Refresh
' Dataset 명 사용
'=======================================================
Public Sub Refresh_DataSet(DataSetName As String)
 
    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' DataSetName               Refresh 할 데이터 셋 명
    '
    ' ======================================================================

   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If
   
    Application.Run GS_BP_Common & "!Refresh_DataSet", DataSetName

End Sub

모듈명 : m_Common

'
' 공통모듈이 열려있는지 검사하고 열려있지 않으면 공통모듈 Open
'
Public Function chkCommon() As Boolean

    Dim wbCommonXla     As Workbook
    Dim r               As Boolean
    Dim strCommon       As String
   
    On Error Resume Next
   
    Set wbCommonXla = Workbooks(GS_BP_Common)
   
    If wbCommonXla Is Nothing Then
        strCommon = Dir(Environ("ProgramFiles") & "\BIMatrix\iMatrixBin\" & GS_BP_Common)
        If strCommon <> "" Then
            Set wbCommonXla = Workbooks.Open(Environ("ProgramFiles") & "\BIMatrix\iMatrixBin\" & GS_BP_Common, ReadOnly:=True)
        Else
            Set wbCommonXla = Workbooks.Open(Filename:="\\203.245.65.63\biproject\5.Working\common\" & GS_BP_Common, ReadOnly:=True)
        End If
       
    Else
        'Set wbCommonXla = Workbooks(GS_BP_Common)
    End If
   
    r = TypeName(wbCommonXla) = "Workbook"
   
    If Not r Then
        MsgBox "BP common module not found!" & vbCr & vbCr & _
               "contect to system manager.", vbCritical, cstrMsgTitle
    End If
    chkCommon = r

End Function

Public Sub Auto_Open()

    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If

    ' 공통모듈 호출
    Application.Run GS_BP_Common & "!Init_Param"
   
End Sub

'
' 조회 조건 동기화
'
Public Sub Set_User_Condition_Sync()
   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If
       
    ' 공통모듈 호출
    Application.Run GS_BP_Common & "!Set_User_Condition_Sync"

End Sub

'
' 출력된 범위의 BasePoint 를 기준으로 데이터 초기화
'
Public Sub Clear_BasePoint(Optional TargetWorksheet As Worksheet)

    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' TargetWorksheet           작업대상 워크시트, 지정하지 않으면 모든 시트
    '
    ' ======================================================================
   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If
       
    ' 공통모듈 호출
    Application.Run GS_BP_Common & "!Clear_BasePoint", TargetWorksheet

End Sub

'
' 자동확장 범위의 데이터 초기화
'
Public Sub Clear_Auto_Expand(Optional TargetWorksheet As Worksheet)

    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' TargetWorksheet           작업대상 워크시트, 지정하지 않으면 모든 시트
    '
    ' ======================================================================

    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If
       
    ' 공통모듈 호출
    Application.Run GS_BP_Common & "!Clear_Auto_Expand", TargetWorksheet

End Sub

'
' 기본 뷰 시트 초기 셋팅
'
Public Sub Set_InitView(Optional TargetWorksheet As Worksheet, Optional SkipBlank As Boolean = True)

    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' TargetWorksheet           작업대상 워크시트, 지정하지 않으면 모든 시트
    ' SkipBlank                 수식을 복사할 때 빈 셀은 제외할 것인지 설정, 기본값은 True 이고 빈 셀은 제외함
    '
    ' ======================================================================
   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If
       
    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(False)
       
    ' 공통모듈 호출
    Application.Run GS_BP_Common & "!Set_InitView", TargetWorksheet, SkipBlank
   
    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(True)

End Sub

'
' 특정 시트만 저장
'
Public Function MTX_CRUD(TargetWorksheet As Worksheet, Message As String, Optional CRUD_Execute_Type As String = "M") As Boolean

    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' TargetWorksheet           저장하려는 대상 워크시트
    ' Message                   작업 결과 Message
    ' CRUD_Execute_Type         CRUD 작업 방법
    '                           T : Truncate -> Insert
    '                           D : Delete -> Insert
    '                           M : Merge Insert      : Default
    '                           N : Insert
    '                           R : 행 단위로 구분자 지정(U:Update   D:Delete   C:Insert)
    '
    ' ======================================================================
   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        MTX_CRUD = False
        Message = "라이브러리를 찾을 수 없습니다."
        Exit Function
    End If
       
    MTX_CRUD = False
    Message = "MTX_MultiCRUD 함수를 이용하세요."
    Exit Function
   
    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(False)

   
    ' 공통모듈 호출
    MTX_CRUD = Application.Run(GS_BP_Common & "!MTX_CRUD", TargetWorksheet, Message, CRUD_Execute_Type)

    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(True)

End Function

'
' 다중 시트 저장
'
Public Function MTX_MultiCRUD(SheetNameList As String, Message As String, Optional CRUD_Execute_Type As CRUDExecuteType = CRUDExecuteType.MergeInsert) As Boolean

    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' SheetNameList             콤마로 구분된 저장 대상 워크시트 리스트, 빈 값으로 지정하면 모든 시트
    ' Message                   작업 결과 Message
    ' CRUD_Execute_Type         CRUD 작업 방법
    '                           T : Truncate -> Insert
    '                           D : Delete -> Insert
    '                           M : Merge Insert      : Default
    '                           N : Insert
    '                           R : 행 단위로 구분자 지정(U:Update   D:Delete   C:Insert)
   
    ' CRUD_Execute_Type         CRUD 작업 방법
    '     MergeInsert           MERGE INTO - Default
    '     DeleteInsert          DELETE / INSERT
    '     Insert                INSERT
    '     TruncateInsert        TRUNCATE / INSERT
    '     ByRow                 행 단위로 구분자 지정(U:Update   D:Delete   C:Insert)
    '     UseSheetSetting       시트에 설정된 그대로 실행
   
    '
    ' ======================================================================
   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        MTX_MultiCRUD = False
        Message = "라이브러리를 찾을 수 없습니다."
        Exit Function
    End If
   
    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(False)
   
    ' 공통모듈 호출
    MTX_MultiCRUD = Application.Run(GS_BP_Common & "!MTX_MultiCRUD", SheetNameList, Message, CRUD_Execute_Type)

    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(True)

End Function

'
' 열 숨기기 처리
'
Public Sub Hide_Column(TargetRange As Range, _
              Optional Cell_Type As XlCellType = xlCellTypeFormulas + xlCellTypeConstants, _
              Optional Cell_Value As XlSpecialCellsValue = xlErrors)

    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' TargetRange               숨김 처리할 대상 열 범위
    ' Cell_Type                 대상 셀의 형식(수식 또는 상수로 입력된 셀), 기본값은 수식/값 모두 처리
    ' Cell_Value                대상 셀의 결과 값, 기본값은 오류값이 있는 셀만 처리
    '                             1. xlErrors     오류
    '                             2. xlLogical    논리
    '                             3. xlNumbers    숫자
    '                             4. xlTextValues 텍스트
    '
    ' ======================================================================
   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If
   
    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(False)
   
   
    ' 공통모듈 호출
    Application.Run GS_BP_Common & "!Hide_Column", TargetRange, Cell_Type, Cell_Value
   
    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(True)

End Sub

'
' 열 숨기기 처리
'
Public Sub Hide_Row(TargetRange As Range, _
           Optional Cell_Type As XlCellType = xlCellTypeFormulas + xlCellTypeConstants, _
           Optional Cell_Value As XlSpecialCellsValue = xlErrors)

    ' ======================================================================
    ' 변수명                    설명
    ' ----------------------------------------------------------------------
    '
    ' TargetRange               숨김 처리할 대상 행 범위
    ' Cell_Type                 대상 셀의 형식(수식 또는 상수로 입력된 셀), 기본값은 수식/값 모두 처리
    ' Cell_Value                대상 셀의 결과 값, 기본값은 오류값이 있는 셀만 처리
    '                             1. xlErrors     오류
    '                             2. xlLogical    논리
    '                             3. xlNumbers    숫자
    '                             4. xlTextValues 텍스트
    '
    ' ======================================================================
   
    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If
   
    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(False)
   
    ' 공통모듈 호출
    Application.Run GS_BP_Common & "!Hide_Row", TargetRange, Cell_Type, Cell_Value

    ' 이벤트, 계산모드, 화면 갱신 셋팅
    Call Set_App(True)

End Sub

Public Sub Set_App(bFlag As Boolean)

    ' 이벤트, 계산모드, 화면 갱신 셋팅
    If bFlag Then
        With Application
            .EnableEvents = True
            .ScreenUpdating = True
            .Calculation = xlCalculationAutomatic
        End With
    Else
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .Calculate
        End With
    End If

End Sub

'
' 참조되는 셀 추적
'
Public Sub Show_Precedents()

    On Error Resume Next
   
    Selection.ShowPrecedents

    Err.Clear
   
End Sub
   
'
' 참조하는 셀 추적
'
Public Sub Show_Dependents()

    On Error Resume Next

    Selection.ShowDependents

    Err.Clear

End Sub
   
'
' 셀 추적 지우기
'
Public Sub Clear_Arrows()

    On Error Resume Next
   
    ActiveSheet.ClearArrows

    Err.Clear

End Sub

모듈명 : m_Extension

'''''''''''''''''''''''''''''''''''''''''''''''''''''
'Library에 포함되지 않은 개별 보고서 전용 Procedure / Function
'''''''''''''''''''''''''''''''''''''''''''''''''''''

Sub s_Hide_Rows()
    'rngHideChk 영역의 값이 True 인 행 들을 숨김 처리한다
   
    Dim rngHideChk  As Range
    Dim rngHide     As Range
    Dim wstActive   As Worksheet
    Dim arrSheet    As Variant
    Dim lngIdx      As Long
   
    '사용안함... 이 기능을 공통Lib에서 처리함
    Exit Sub
   
   
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End With
   
    Set wstActive = ActiveSheet
   
    arrSheet = Array(Sheets("V1"), Sheets("V2"), Sheets("V3"))
   
    For lngIdx = LBound(arrSheet) To UBound(arrSheet)
        arrSheet(lngIdx).Select
   
        Debug.Print arrSheet(lngIdx).Name
   
        Set rngHideChk = ActiveSheet.Range("_Def_Hide_Row_")
   
        If rngHideChk Is Nothing Then GoTo Next_lngIdx
   
        rngHideChk.EntireRow.Hidden = False
   
        On Error Resume Next
        Set rngHide = rngHideChk.SpecialCells(xlCellTypeFormulas, xlLogical)
        On Error GoTo 0
        If rngHide Is Nothing Then
        Else
            rngHide.EntireRow.Hidden = True
        End If
Next_lngIdx:
    Next lngIdx
   
    wstActive.Activate
   
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
    End With
   
    Set wstActive = Nothing
    Set rngHideChk = Nothing
    Set rngHide = Nothing
    Set wstActive = Nothing
   
End Sub

'
' 3) Purchae & Sales Qty를 초기화 시키는 SP를 실행
'   계획 값만 초기화 한다
'    1. 선택한 버전의 "ARPI_구매판매물량계획기준[ARPI_PRCH_SALE_QTY_BSS]" 및
'       상세 데이터 "ARPI_구매판매물량계획상세[ARPI_PRCH_SALE_QTY_DETL]" 데이터 삭제
'    2. 해당 년도의 "ARPI_년간MP구매판매물량계획기준[ARPI_MP_PRCH_SALE_QTY_BSS]" 데이터를
'       "ARPI_구매판매물량계획기준[ARPI_PRCH_SALE_QTY_BSS]" 으로 복사
Public Sub s_Init_Pur_Sal_Qty()
    Select Case ActiveSheet.Name
    Case "V1"   'BZ
        Sheets("P1").Range("VS_ARO_PRD_CLS_CD").Value = "AR201010"
    Case "V2"   'TOL
        Sheets("P1").Range("VS_ARO_PRD_CLS_CD").Value = "AR201020"
    Case "V3"   'MX
        Sheets("P1").Range("VS_ARO_PRD_CLS_CD").Value = "AR201030"
    Case Else
        Exit Sub
    End Select
   
    If MsgBox(Sheets("P1").Range("VS_PLAN_VERSION_DESC").Value & vbCrLf & _
            "Purchase & Sales Master를 재적용 하시겠습니까?", vbQuestion + vbDefaultButton2 + vbYesNo, vbNullString) = vbYes Then
        Call Refresh_DataSet("DS_D10_InitPurSalQty")   ' 프로시저 콜하는 부분
        MsgBox "작업이 끝났습니다" & vbCrLf & "다시 조회하세요", vbInformation, vbNullString
    End If
End Sub

모듈명 : m_General

' 데이터 조회 전 매크로 실행
Public Sub Before_Refresh()
   
    ' 조회 조건 동기화
    Call Set_User_Condition_Sync

End Sub

' 데이터 조회 후 매크로 실행
Public Sub After_Refresh()

    ' 공통 모듈 체크
    If chkCommon Then
    Else
        Exit Sub
    End If

    ' 이벤트, 계산모드, 화면 갱신 셋팅
    With Application
        .EnableEvents = False
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    ' 기본 수식, 서식, 입력스타일, 실적 스타일 적용
    Call Set_InitView

    ' ..........
    ' ............
   
    Call s_Hide_Rows
   
    ' 이벤트, 계산모드, 화면 갱신 셋팅
    With Application
        .EnableEvents = True
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

End Sub

'
' 저장 처리 - Default
'
Public Sub Save_Data()

    Dim rtnSave                     As Boolean
    Dim strWorksheetNameList        As String
    Dim strMsg                      As String

    Select Case ActiveSheet.Name
    Case "V1"
        strWorksheetNameList = "V1_ARAC_PRCH_SALE_QTY" ' --대상워크시트이름리스트-콤마로구분"
    Case "V2"
        strWorksheetNameList = "V2_ARAC_PRCH_SALE_QTY" ' --대상워크시트이름리스트-콤마로구분"
    Case "V3"
        strWorksheetNameList = "V3_ARAC_PRCH_SALE_QTY" ' --대상워크시트이름리스트-콤마로구분"
    End Select
   
    '
    ' Ex1) - 다중으로 지정한 특정 워크시트 - 콤마로 구분
    '
    ' strWorksheetNameList = "dbo.LBPIC_SALE_VC,dbo.LBPIC_SALE_VC2"
    '
    ' Ex2) - 모든 워크시트(자동처리)
    '        값을 지정하지 않음
    ' strWorksheetNameList = ""
    '

    ' CRUD_Execute_Type         CRUD 작업 방법
    '     MergeInsert           MERGE INTO - Default
    '     DeleteInsert          DELETE / INSERT
    '     Insert                INSERT
    '     TruncateInsert        TRUNCATE / INSERT
    '     ByRow                 행 단위로 구분자 지정(U:Update   D:Delete   C:Insert)
    '     UseSheetSetting       시트에 설정된 그대로 실행
   

    rtnSave = MTX_MultiCRUD(strWorksheetNameList, strMsg)

    Debug.Print strMsg
   
    If rtnSave Then
        ' 저장 성공
       
    Else
        '
        ' 저장 실패
        '
        ' 실패한 경우 후속처리
    End If

End Sub

'
' 전체 저장
'
Public Sub Save_Data_All()

    Dim rtnSave                     As Boolean
    Dim strWorksheetNameList        As String
    Dim strMsg                      As String
 
    strWorksheetNameList = "V1_ARAC_PRCH_SALE_QTY,V2_ARAC_PRCH_SALE_QTY,V3_ARAC_PRCH_SALE_QTY" ' --대상워크시트이름리스트-콤마로구분"
   
    '
    ' Ex1) - 다중으로 지정한 특정 워크시트 - 콤마로 구분
    '
    ' strWorksheetNameList = "dbo.LBPIC_SALE_VC,dbo.LBPIC_SALE_VC2"
    '
    ' Ex2) - 모든 워크시트(자동처리)
    '        값을 지정하지 않음
    ' strWorksheetNameList = ""
    '

    ' CRUD_Execute_Type         CRUD 작업 방법
    '     MergeInsert           MERGE INTO - Default
    '     DeleteInsert          DELETE / INSERT
    '     Insert                INSERT
    '     TruncateInsert        TRUNCATE / INSERT
    '     ByRow                 행 단위로 구분자 지정(U:Update   D:Delete   C:Insert)
    '     UseSheetSetting       시트에 설정된 그대로 실행
   

    rtnSave = MTX_MultiCRUD(strWorksheetNameList, strMsg)

    Debug.Print strMsg
   
    If rtnSave Then
        ' 저장 성공
       
    Else
        '
        ' 저장 실패
        '
        ' 실패한 경우 후속처리
    End If

End Sub

모듈명 : m_Vars

' =============================================
' 필요한 기본 변수 선언
' =============================================

Public Const cstrMsgTitle           As String = "BP"
Public Const GS_BP_Common           As String = "GS_BP_Common.xlam"

' 권한
Public Enum User_Auth
    Auth_Save = 1
    Auth_ReqApproval = 2
    Auth_Approval = 4
End Enum

' CRUD 실행 유형
Public Enum CRUDExecuteType
    MergeInsert = 1       ' MERGE INTO - Default
    DeleteInsert = 2      ' DELETE / INSERT
    Insert = 4            ' INSERT
    TruncateInsert = 8    ' TRUNCATE / INSERT
    ByRow = 16            ' 행 단위로 구분자 지정(U:Update   D:Delete   C:Insert)
    UseSheetSetting = 32  ' 시트에 설정된 그대로 실행
End Enum

모듈명 : m_임시

Option Explicit

Public Sub s_작업용_팝업메뉴추가()

    Dim cBar                As CommandBar
    Dim cPopup              As CommandBarPopup

    Application.Run GS_BP_Common & "!Make_Formula_Tracer"
    Application.Run GS_BP_Common & "!Make_Design_Menu"

    Set cBar = Application.CommandBars("Cell")
''     cBar.Reset

    Set cPopup = cBar.Controls.Add(Type:=msoControlPopup)
    cPopup.Caption = "ASK"

    Call Make_Design_Button(cPopup, "개발상태로", "s_개발상태로")
    Call Make_Design_Button(cPopup, "내보내기", "s_내보내기")
    Call Make_Design_Button(cPopup, "양식백업", "s_양식백업")
    Call Make_Design_Button(cPopup, "------------------------------------", "")
    Call Make_Design_Button(cPopup, "작업시트지정", "s_작업시트지정")
    Call Make_Design_Button(cPopup, "작업시트로", "s_작업시트로")
    Call Make_Design_Button(cPopup, "------------------------------------", "")
    Call Make_Design_Button(cPopup, "입력영역에_테스트용_데이터넣기", "s_입력영역에_테스트용_데이터넣기")
    Call Make_Design_Button(cPopup, "------------------------------------", "")
    Call Make_Design_Button(cPopup, "궤선_내부포함_전체를_가는선", "S_궤선_내부포함_전체를_가는선")
    Call Make_Design_Button(cPopup, "궤선_테두리만_가는선", "S_궤선_테두리만_가는선")
    Call Make_Design_Button(cPopup, "궤선_테두리는가는선_내부는점선", "s_궤선_테두리는가는선_내부는점선")
    Call Make_Design_Button(cPopup, "셀_테두리를_표준색상으로", "s_셀_테두리를_표준색상으로")
    Call Make_Design_Button(cPopup, "------------------------------------", "")
    Call Make_Design_Button(cPopup, "설계서를_Vn양식으로_변환", "s_설계서를_Vn양식으로_변환")
    Call Make_Design_Button(cPopup, "C1_Control_Width_표준화", "s_C1_Control_Width_표준화")
    Call Make_Design_Button(cPopup, "------------------------------------", "")
    Call Make_Design_Button(cPopup, "값으로붙여넣기", "s_값으로붙여넣기")
    Call Make_Design_Button(cPopup, "텍스트로붙여넣기", "s_텍스트로붙여넣기")
    Call Make_Design_Button(cPopup, "------------------------------------", "")
    Call Make_Design_Button(cPopup, "수식을보기좋게", "s_수식을보기좋게")
    Call Make_Design_Button(cPopup, "셀서식_백만_소수0", "s_셀서식_백만_소수0")
    Call Make_Design_Button(cPopup, "셀서식_소수0", "s_셀서식_소수0")
    Call Make_Design_Button(cPopup, "셀서식_소수0_BLANK", "s_셀서식_소수0_BLANK")
    Call Make_Design_Button(cPopup, "셀서식_FS_P0", "s_셀서식_FS_P0")
    Call Make_Design_Button(cPopup, "설계서_Cell_색칠하기", "s_설계서_Cell_색칠하기")
    Call Make_Design_Button(cPopup, "------------------------------------", "")
    Call Make_Design_Button(cPopup, "실적_계획_공통구간_수식_변환", "s_실적_계획_공통구간_수식_변환")

    Call Make_Design_Button(cPopup, "------------------------------------", "")
    Call Make_Design_Button(cPopup, "행열그룹_해제", "s_행열그룹_해제")
    Call Make_Design_Button(cPopup, "------------------------------------", "")
    Call Make_Design_Button(cPopup, "After_Refresh", "After_Refresh")

    MsgBox "팝업 메뉴 추가 완료"
End Sub

Sub s_내보내기()
    Dim lngIdx      As Long
    On Error Resume Next
   
    If MsgBox("양식을 백업할 까요?", vbQuestion + vbYesNo + vbDefaultButton2, "") = vbYes Then
        Call s_양식백업
    End If
   
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sheets("V1").Select
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
    '행/열 그룹 해제
    For lngIdx = 1 To 10
        Columns.Ungroup
        Rows.Ungroup
    Next lngIdx
   
    '행/열 숨김, 보이기
    Columns.Hidden = False
    Rows.Hidden = False
    Columns("B:AV").Hidden = True
    Columns("AW:CP").Hidden = False
    Columns("BA:BA").Hidden = True
    Rows("2:95").Hidden = True
    Rows(99).Hidden = True
    Rows("111:112").Hidden = True
    Rows(114).Hidden = True
''    Rows("680:1000").Hidden = True
   
    '틀고정
    ActiveWindow.FreezePanes = True
    ActiveWindow.FreezePanes = False
    Application.GoTo Range("A1"), True
    Range("BA101").Select
    ActiveWindow.FreezePanes = True
   
''    '실적 열 그룹 지정 후 숨김
''    Columns("BA:BL").Group
''    Columns("BA:BL").Hidden = True
   
    '첫 행/열 너비,높이
    Rows(1).Hidden = False
    Rows(1).RowHeight = 5
    Columns(1).Hidden = False
    Columns(1).ColumnWidth = 1
    Rows("100:109").RowHeight = 15
    Rows("115:119").RowHeight = 15

    ActiveWindow.Zoom = 100
   
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sheets("V2").Select
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
    '행/열 그룹 해제
    For lngIdx = 1 To 10
        Columns.Ungroup
        Rows.Ungroup
    Next lngIdx
   
    '행/열 숨김, 보이기
    Columns.Hidden = False
    Rows.Hidden = False
    Columns("B:AV").Hidden = True
    Columns("AW:CP").Hidden = False
    Columns("BA:BA").Hidden = True
    Rows("2:95").Hidden = True
    Rows("111:112").Hidden = True
    Rows(114).Hidden = True
''    Rows("680:1000").Hidden = True
   
    '틀고정
    ActiveWindow.FreezePanes = True
    ActiveWindow.FreezePanes = False
    Application.GoTo Range("A1"), True
    Range("BA101").Select
    ActiveWindow.FreezePanes = True
   
    '실적 열 그룹 지정 후 숨김
''    Columns("BA:BL").Group
''    Columns("BA:BL").Hidden = True
   
    '첫 행/열 너비,높이
    Rows(1).Hidden = False
    Rows(1).RowHeight = 5
    Columns(1).Hidden = False
    Columns(1).ColumnWidth = 1
    Rows("100:109").RowHeight = 15
    Rows("115:123").RowHeight = 15

    ActiveWindow.Zoom = 100
   
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sheets("V3").Select
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
    '행/열 그룹 해제
    For lngIdx = 1 To 10
        Columns.Ungroup
        Rows.Ungroup
    Next lngIdx
   
    '행/열 숨김, 보이기
    Columns.Hidden = False
    Rows.Hidden = False
    Columns("B:AV").Hidden = True
    Columns("AW:CP").Hidden = False
    Columns("BA:BA").Hidden = True
    Rows("2:95").Hidden = True
    Rows("109:112").Hidden = True
    Rows(114).Hidden = True
''    Rows("680:1000").Hidden = True
   
    '틀고정
    ActiveWindow.FreezePanes = True
    ActiveWindow.FreezePanes = False
    Application.GoTo Range("A1"), True
    Range("BA101").Select
    ActiveWindow.FreezePanes = True
   
    '실적 열 그룹 지정 후 숨김
''    Columns("BA:BL").Group
''    Columns("BA:BL").Hidden = True
   
    '첫 행/열 너비,높이
    Rows(1).Hidden = False
    Rows(1).RowHeight = 5
    Columns(1).Hidden = False
    Columns(1).ColumnWidth = 1
    Rows("100:107").RowHeight = 15
    Rows("115:119").RowHeight = 15

    ActiveWindow.Zoom = 100

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    'CRUD 시트 초기화
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
''    Sheets("V1_BZ_1").Range("C_BasePoint").Resize(10000).EntireRow.Clear
   
    ''''''''''''''''''''''''''''''
    Range("VS_PRE_FIX_BU_CD").Value = "GC011030"
    Range("VD_LAST_UPDATED").Value = Now()
    Range("_cacheoption_").ClearContents
    ''''''''''''''''''''''''''''''

    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Call s_View시트제외_모두숨기기
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sheets("V1_B").Visible = False
    Sheets("V2_B").Visible = False
    Sheets("V3_B").Visible = False
   
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Range("VD_LAST_UPDATED").Value = Now()
    Range("_cacheoption_").Value = 1
    '버전을 선택하지 않았을때 #VALUE! 오류 방지용 수식
    Range("VS_PLAN_INPUT_FROM").FormulaR1C1 = _
        "=IFERROR(DATE(LEFT(VS_PLAN_INPUT_TERM_FROM,  4),  MID(VS_PLAN_INPUT_TERM_FROM,  5,  2),  1),  DATEVAL‎UE(""2000-01-01""))"
   
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    Sheets("V1").Select
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
   
    On Error GoTo 0
   
    MsgBox "내보내기 상태로 작업 완료"
End Sub

Sub s_개발상태로()
  
    Call s_모든시트_보이기
   
    ''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''
    Sheets("V1").Select
    ''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''
   
    '열 그룹 지정
    Columns("A:V").Group
    Columns("AA:AB").Group
    Columns("AD:AF").Group
    Columns("AH:AU").Group
   
    '행 그룹 지정
    Rows("2:27").Group
    Rows("29:58").Group
    Rows("62:68").Group
    Rows("73:96").Group
   
    Columns.Hidden = False
    Rows.Hidden = False
    Rows("29:58").Hidden = True
    Rows("63:69").Hidden = True
    Rows("74:94").Hidden = True
   
    Rows("133:139").Hidden = True
   
    Columns("B:Z").Hidden = True
    Columns("AL:AO").Hidden = True
    Columns("AR:AV").Hidden = True
   
    Application.GoTo Range("AS23"), True
   
    ActiveWindow.FreezePanes = False
   
    ActiveWindow.Zoom = 85
   
    ''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''
    Sheets("V2").Select
    ''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''
   
    '열 그룹 지정
    Columns("A:V").Group
    Columns("AA:AB").Group
    Columns("AD:AF").Group
    Columns("AH:AU").Group
   
    '행 그룹 지정
    Rows("2:27").Group
    Rows("29:58").Group
    Rows("62:68").Group
    Rows("73:96").Group
   
    Columns.Hidden = False
    Rows.Hidden = False
    Rows("29:58").Hidden = True
    Rows("63:69").Hidden = True
    Rows("74:94").Hidden = True
   
    Rows("133:139").Hidden = True
   
    Columns("B:Z").Hidden = True
    Columns("AL:AO").Hidden = True
    Columns("AR:AV").Hidden = True
   
    Application.GoTo Range("AS23"), True
   
    ActiveWindow.FreezePanes = False
   
    ActiveWindow.Zoom = 85
   
   
    ''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''
    Sheets("V3").Select
    ''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''
   
    '열 그룹 지정
    Columns("A:V").Group
    Columns("AA:AB").Group
    Columns("AD:AF").Group
    Columns("AH:AU").Group
   
    '행 그룹 지정
    Rows("2:27").Group
    Rows("29:58").Group
    Rows("62:68").Group
    Rows("73:96").Group
   
    Columns.Hidden = False
    Rows.Hidden = False
    Rows("29:58").Hidden = True
    Rows("63:69").Hidden = True
    Rows("74:94").Hidden = True
   
    Rows("133:139").Hidden = True
   
    Columns("B:Z").Hidden = True
    Columns("AL:AO").Hidden = True
    Columns("AR:AV").Hidden = True
   
    Application.GoTo Range("AS23"), True
   
    ActiveWindow.FreezePanes = False
   
    ActiveWindow.Zoom = 85
   
   
   
    ''''''''''''''''''''''''''''''
    ''''''''''''''''''''''''''''''
    Sheets("V1").Select
   
    ''''''''''''''''''''''''''''''
    On Error GoTo 0
End Sub

Sub s_수식변환()
    Dim strFormula_O    As String
    Dim strFormula_N    As String
   
    strFormula_O = Selection.FormulaR1C1
    Debug.Print strFormula_O
   
    strFormula_N = Application.WorksheetFunction.Substitute(strFormula_O, "=", "=IF(R13C = ""실적"",  ")
    strFormula_N = strFormula_N & ",  OFFSET('D1'!R10C2,  RC30,  R70C))"
    Debug.Print strFormula_N
   
    Selection.FormulaR1C1 = strFormula_N
End Sub

Sub s_수식셀_녹색칠하기()
    On Error Resume Next
   
    Selection.SpecialCells(xlCellTypeFormulas, 23).Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 5296274
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
   
    On Error GoTo 0
End Sub

Sub s_값으로붙여넣기()
    Selection.PasteSpecial Paste:=xlPasteVal‎ues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Sub s_행위치동기화()

    Rows("1:1").Select
    Rows("1:85").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.EntireRow.Hidden = True
End Sub

Sub s_컬럼위치동기화()
   
    Columns("A:A").Resize(, 53 - Range("I1").Column).Select
    Range("A1").Activate
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.EntireColumn.Hidden = True
    Range("BA101").Select
End Sub

Sub s_수식셀_배경색칠하기()
    Dim rngCell     As Range
    Dim rngFmr      As Range
   
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
   
    Set rngFmr = Cells.SpecialCells(xlCellTypeFormulas, 23)
    If rngFmr Is Nothing Then GoTo Exit_Rtn
   
    '100번째 행 이후만 작업
    Set rngFmr = Application.Intersect(rngFmr, Rows(100).Resize(1000))
    If rngFmr Is Nothing Then GoTo Exit_Rtn
   
    '수식이 들어있는 Cell
    With rngFmr.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorAccent4
        .TintAndShade = 0.399975585192419
        .PatternTintAndShade = 0
    End With
   
    For Each rngCell In rngFmr
        If rngCell.Row >= 100 Then
            If InStr(1, rngCell.Formula, "!", 1) > 0 Then   '외부참조 수식이면
                With rngCell.Interior
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
                    .ThemeColor = xlThemeColorAccent6
                    .TintAndShade = 0.799981688894314
                    .PatternTintAndShade = 0
                End With
            End If
        End If
    Next rngCell

Exit_Rtn:

    Set rngFmr = Nothing

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
   
    MsgBox "작업 끝"
End Sub

Sub s_수식옮기기()
    Dim wstSrc      As Worksheet
    Dim wstTgt      As Worksheet
    Dim rngCell     As Range
    Dim rngFmr      As Range
    Dim rngAra      As Range
   
    '작업순서
    ' 주의! 원본 시트의 행/열 숨김을 모두 해제 후 작업해야 함
    ' 주의! 반드시 값으로 붙여 넣어야 함
    ''''''''''''''''''''''''''''''
    ' 1. 이름이 [F] 인 시트를 생성
    ' 2. 설계서에서 수식을 = 으로 변환 후 [F] 시트에 값 복사
    '    [F] 시트의 BA101 셀이 데이터의 시작위치가 맞는지 확인
    '    스타일에 변형이 생겼는지 확인
    '    - 로 시작하는 Text 때문에 수식 오류가 있다면 수식 오류 보정
    ' 3. [F] 시트의 = 수식을 = 으로 변환
    ' 4. wstTgt 시트를 수정
    ' 5. 이 매크로를 실행
   
   
    Set wstSrc = Sheets("F")
    Set wstTgt = Sheets("V3")
   
    If MsgBox("목적지 시트가 " & wstTgt.Name & " 인지 확인" & vbCrLf & "목적지 시트의 수식을 삭제하였는지 확인" & vbCrLf & "계속?", vbQuestion + vbYesNo + vbDefaultButton2, "") = vbNo Then
        GoTo Exit_Rtn
    End If
   
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
   
    wstSrc.Select
    On Error Resume Next
    Set rngFmr = wstSrc.Cells.SpecialCells(xlCellTypeFormulas, 23)
    On Error GoTo 0
   
    If rngFmr Is Nothing Then
        MsgBox "복사할 수식이 없음"
        GoTo Exit_Rtn
    End If
   
    For Each rngAra In rngFmr.Areas
        rngAra.Select
        wstTgt.Range(rngAra.Address).Formula = rngAra.Formula
    Next rngAra
   
    wstTgt.Select
    Application.GoTo Range("BA100"), True

    MsgBox "작업 끝"

Exit_Rtn:
    Set rngFmr = Nothing
    Set wstSrc = Nothing
    Set wstTgt = Nothing

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Sub s_양식백업()
    Dim lngIdx      As Long
    On Error Resume Next
   
    If MsgBox("조회를 하여 수식을 복원 후 백업 받아야 합니다." & vbCrLf & "계속?", vbQuestion + vbYesNo + vbDefaultButton2, "") = vbNo Then
        GoTo Exit_Rtn
    End If
   
    ''''''''''''''''''''''''''''''
    Sheets("V1").Select
    ''''''''''''''''''''''''''''''
   
    '양식백업
    Sheets("V1").Cells.Copy Sheets("V1_B").Cells
    With Sheets("V1_B")
        .Columns.Hidden = False
        .Rows.Hidden = False
       
        .Rows("1:94").Clear
        .Rows("2:94").Hidden = True
        .Rows(130).Resize(1000).Delete
       
        .Columns("A:AU").Clear
        .Columns("B:AU").Hidden = True
       
        '행/열 그룹 해제
        For lngIdx = 1 To 10
            .Columns.Ungroup
            .Rows.Ungroup
        Next lngIdx
           
    End With
   
    ''''''''''''''''''''''''''''''
    Sheets("V2").Select
    ''''''''''''''''''''''''''''''
   
    '양식백업
    Sheets("V2").Cells.Copy Sheets("V2_B").Cells
    With Sheets("V2_B")
        .Columns.Hidden = False
        .Rows.Hidden = False
       
        .Rows("1:94").Clear
        .Rows("2:94").Hidden = True
        .Rows(130).Resize(1000).Delete
       
        .Columns("A:AU").Clear
        .Columns("B:AU").Hidden = True
       
        '행/열 그룹 해제
        For lngIdx = 1 To 10
            .Columns.Ungroup
            .Rows.Ungroup
        Next lngIdx
           
    End With
   
    ''''''''''''''''''''''''''''''
    Sheets("V3").Select
    ''''''''''''''''''''''''''''''
   
    '양식백업
    Sheets("V3").Cells.Copy Sheets("V3_B").Cells
    With Sheets("V3_B")
        .Columns.Hidden = False
        .Rows.Hidden = False
       
        .Rows("1:94").Clear
        .Rows("2:94").Hidden = True
        .Rows(130).Resize(1000).Delete
       
        .Columns("A:AU").Clear
        .Columns("B:AU").Hidden = True
       
        '행/열 그룹 해제
        For lngIdx = 1 To 10
            .Columns.Ungroup
            .Rows.Ungroup
        Next lngIdx
           
    End With
   
    ''''''''''''''''''''''''''''''
    Sheets("V1").Select
    ''''''''''''''''''''''''''''''
   
    On Error GoTo 0
   
    MsgBox "백업 완료"
Exit_Rtn:
End Sub

Sub s_CRUD_시트_초기화()
    '새로 만들어진 CRUD 시트의 양식을 초기화 한다
   
    Dim lngColCnt   As Long
    Dim lngColNum   As Long
   
   
    lngColCnt = Application.WorksheetFunction.CountA(Rows(18)) - 1
   
    'c_Execute
    ActiveSheet.Range("c_Execute").Value = "M"
   
    '열 키범위 ~ 값 범위
    Range("C7").Value = "열 키범위"
    Range("C8").Value = "행 키범위"
    Range("C9").Value = "값 범위"
   
    'Update YN
    Rows(6).Hidden = True
    Rows(7).Resize(3).Hidden = False
   
    '양식 복사
    Range("D7").Resize(3, 1).ClearContents
    Range("D7").Resize(8, 1).Copy Range("E7").Resize(8, lngColCnt - 1)
   
    '셀 서식을 일반으로
    With Range("D7").Resize(8, lngColCnt)
        .NumberFormatLocal = "G/표준"

        With .Borders(xlEdgeTop)
            .LineStyle = xlDash
            .ThemeColor = 1
            .TintAndShade = -0.249946592608417
            .Weight = xlThin
        End With
    End With
   

    For lngColNum = 4 To lngColCnt + 3
        Select Case Cells(16, lngColNum).Value
        Case "P"
            With Cells(16, lngColNum).Resize(4, 1).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorAccent6
                .TintAndShade = 0.799981688894314
                .PatternTintAndShade = 0
            End With
        End Select
   
        Select Case Cells(18, lngColNum).Value
        Case "bu_ver_id"
            Cells(8, lngColNum).Formula = "=VS_PLAN_VERSION"
        Case "bss_ym"
            Cells(7, lngColNum).Formula = "='V1'!$BN$8:$CP$8"
       
        Case "cre_usr_id"
            Cells(8, lngColNum).Formula = "=VS_USER_ID"
            Cells(14, lngColNum).Value = "N"
       
            With Cells(14, lngColNum).Resize(1, 1).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 49407
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
                   
            With Cells(16, lngColNum).Resize(4, 1).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.149998474074526
                .PatternTintAndShade = 0
            End With
        Case "cre_dt"
            Cells(14, lngColNum).Value = "N"
       
            With Cells(14, lngColNum).Resize(1, 1).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 49407
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
       
            With Cells(16, lngColNum).Resize(4, 1).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.149998474074526
                .PatternTintAndShade = 0
            End With
        Case "upd_usr_id"
            Cells(8, lngColNum).Formula = "=VS_USER_ID"
       
            With Cells(16, lngColNum).Resize(4, 1).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.149998474074526
                .PatternTintAndShade = 0
            End With
        Case "upd_dt"
       
            With Cells(16, lngColNum).Resize(4, 1).Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = -0.149998474074526
                .PatternTintAndShade = 0
            End With
        End Select
    Next lngColNum

''    '쓰레기 컬럼 삭제
''    Columns(lngColCnt + 5).Resize(Columns.Count - lngColCnt - 4).Delete Shift:=xlToLeft
''    Rows(21).Resize(10000).Delete
End Sub

Sub s_입력영역을_수식으로_추출()
    '작업 순서
    '  추출된 주소를 저장할 영역을 먼저 수정해야 함
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' 1.입력 대상 영역을 노란색으로 칠하거나 셀 스타일을 [INPUT]으로 지정
    ' 2.추출할 범위를 선택
    ' 3.결과 수식이 debug 창에 나타남
    ' 4.복사하여 대상범위 지정 Cell에 붙여 넣고 확인한다
   
    Dim rngCell         As Range
    Dim rngInputRng     As Range
    Dim strInputRng     As String
   
    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
   
    For Each rngCell In Selection
        If rngCell.Interior.Color = 65535 Or _
           rngCell.Style = "INPUT" Or _
           rngCell.Interior.Color = 13564412 Then
            If rngInputRng Is Nothing Then
                Set rngInputRng = rngCell
            Else
                Set rngInputRng = Application.Union(rngInputRng, rngCell)
            End If
        End If
    Next rngCell

''    Range("AY3").ClearContents
   
    For Each rngCell In rngInputRng.Areas
''        Range("AY3").Formula = Range("AY3").Formula & "," & rngCell.Address
        If strInputRng = "" Then
            strInputRng = "=" & rngCell.Address
        Else
            strInputRng = strInputRng & "," & rngCell.Address
        End If
''        Debug.Print rngCell.Address
    Next rngCell
    Debug.Print strInputRng
   
    Set rngInputRng = Nothing
   
    Application.EnableEvents = True
    Application.Calculation = xlCalculationAutomatic
   
''    Range("AY3").Select
   
    MsgBox "끝"
End Sub

Sub s_입력영역에_테스트용_데이터넣기()
    '작업 순서
    ' 1.입력 대상 영역을 노란색으로 칠하거나 셀 스타일을 [INPUT]으로 지정
    ' 2.추출할 범위를 선택
    ' 3.이 프로시져를 실행하면 입력용 Cell에 [=ROW() * 1000 + COLUMN()] 수식을 넣어 줌
    Dim rngCell         As Range
   
    Application.Calculation = xlCalculationManual
   
    For Each rngCell In Selection
        If rngCell.Interior.Color = 65535 Or _
           rngCell.Style = "INPUT" Or _
           rngCell.Interior.Color = 13564412 Then
            rngCell.Formula = "=ROW() * 1000 + COLUMN()"
        End If
    Next rngCell
   
    Set rngCell = Nothing

    Application.Calculation = xlCalculationAutomatic
End Sub

Sub s_텍스트로붙여넣기()
    ActiveSheet.PasteSpecial Format:="텍스트", Link:=False, DisplayAsIcon:=False

End Sub

Public Sub Make_Design_Button(cPopup As CommandBarPopup, strCaption As String, strAction As String)

    Dim cBtn                As CommandBarButton

    Set cBtn = cPopup.Controls.Add(Type:=msoControlButton)
    With cBtn
        .Caption = strCaption
        .OnAction = strAction
    End With

End Sub

Sub s_수식을그대로_다른영역에복사()
    Range("BA5:CP5").Formula = Range("BA3:CP3").Formula
    Range("BA6:CP6").Formula = Range("BA3:CP3").Formula
End Sub

Sub s_C1_표준화()
    Dim lngRowNum       As Long
   
    Sheets("C1").Select
   
    With Cells.Font
        .Name = "맑은 고딕"
        .Size = 12
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
    End With
   
    lngRowNum = 39
    Do
        lngRowNum = lngRowNum + 1
        If Cells(lngRowNum, "D") = "" Then
            GoTo Exit_Rtn
        End If
       
        'Height
        Cells(lngRowNum, "L") = 25
       
        Select Case Cells(lngRowNum, "H")
        Case "계획년월 :", "작업년월 :", "제품분류 :"
            Cells(lngRowNum, "K") = 60
           
            If Cells(lngRowNum, "E").Value = "Label" Then
                Cells(lngRowNum, "H").Style = "LABEL"
            End If
       
        Case "버전 :"
            Cells(lngRowNum, "K") = 40
        Case "조회"
            Cells(lngRowNum, "K") = 70
            If Cells(lngRowNum, "E").Value = "Button" Then
                Cells(lngRowNum, "H").Style = "BUTTON"
                Cells(lngRowNum, "T").Value = "MX_REFRESH"
                Cells(lngRowNum, "I").Value = 500
            End If
        Case "저장"
            Cells(lngRowNum, "K") = 70
            If Cells(lngRowNum, "E").Value = "Button" Then
                Cells(lngRowNum, "H").Style = "BUTTON"
                Cells(lngRowNum, "T").Value = "Save_Data"
            End If
        Case "전체저장"
            Cells(lngRowNum, "K") = 80
            If Cells(lngRowNum, "E").Value = "Button" Then
                Cells(lngRowNum, "H").Style = "BUTTON"
                Cells(lngRowNum, "T").Value = "Save_Data_All"
            End If
        Case "확정", "승인"
            Cells(lngRowNum, "K") = 70
            If Cells(lngRowNum, "E").Value = "Button" Then
                Cells(lngRowNum, "H").Style = "BUTTON"
            End If
        Case "승인 요청", "Upload", "Download"
            Cells(lngRowNum, "K") = 80
        Case "마스터 재적용"
            Cells(lngRowNum, "K") = 100
        Case "계획 초기화"
            Cells(lngRowNum, "K") = 80
        Case "진행상태 :"
            Cells(lngRowNum, "K") = 80
        Case "화면이동 :"
            Cells(lngRowNum, "K") = 60
        Case Else
            Select Case Cells(lngRowNum, "E")
            Case "Calendar"
                Cells(lngRowNum, "K") = 80
            Case "ComboBox"
                Select Case Cells(lngRowNum, "G")
                Case "CBO_PLAN_VERSION", "CBO_PLAN_VERSION2", "CBO_PLAN_VERSION3", "CBO_PLAN_VERSION4"
                    Cells(lngRowNum, "K") = 220
                Case "CBO_VIEW_LIST1", "CBO_VIEW_LIST2", "CBO_VIEW_LIST3", "CBO_VIEW_LIST4", "CBO_VIEW_LIST5", "CBO_VIEW_LIST6", "CBO_VIEW_LIST7", "CBO_VIEW_LIST8"
                    Cells(lngRowNum, "K") = 160
                Case "CBO_ARO_PRD_CLS_CD", "CBO_ARO_PRD_CLS_CD2"
                    Cells(lngRowNum, "K") = 50
                Case Else
                    Cells(lngRowNum, "H").Select
                    MsgBox "새로 발견된 컨트롤"
                    Stop
                    GoTo Exit_Rtn
                End Select
            Case "InputBox"
                If Cells(lngRowNum, "F") = "Text" And Cells(lngRowNum - 1, "H").Value = "계획년월 :" Then
                    Cells(lngRowNum, "K") = 80
                ElseIf Cells(lngRowNum, "F") = "Text" And Cells(lngRowNum - 1, "H").Value = "버전 :" Then
                    Cells(lngRowNum, "K") = 220
                ElseIf Cells(lngRowNum, "F") = "Text" And Cells(lngRowNum - 1, "H").Value = "진행상태 :" Then
                    Cells(lngRowNum, "K") = 80
                ElseIf Cells(lngRowNum, "F") = "Text" And Cells(lngRowNum - 1, "H").Value = "작업년월 :" Then
                    Cells(lngRowNum, "K") = 80
                Else
                    Cells(lngRowNum, "H").Select
                    MsgBox "새로 발견된 컨트롤"
                    Stop
                    GoTo Exit_Rtn
                End If
            Case Else
                If Cells(lngRowNum, "E") = "Label" And Cells(lngRowNum, "H").Formula = "=VS_PLAN_VERSION_STATUS" Then
                    Cells(lngRowNum, "K") = 80
                Else
                    Cells(lngRowNum, "H").Select
                    MsgBox "새로 발견된 컨트롤"
                    Stop
                    GoTo Exit_Rtn
                End If
            End Select
        End Select
    Loop
   
Exit_Rtn:
    MsgBox "작업 끝"
End Sub

Sub s_View시트제외_모두숨기기()
    Dim wstSheet    As Worksheet
   
    For Each wstSheet In ActiveWorkbook.Worksheets
        If f_blnIsView(wstSheet) Then
        Else
            wstSheet.Visible = xlSheetHidden
        End If
    Next wstSheet

    Set wstSheet = Nothing

End Sub

Sub S_궤선_테두리만_가는선()

    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With

End Sub

Sub S_궤선_내부포함_전체를_가는선()

    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With

    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With

End Sub

Sub s_궤선_테두리는가는선_내부는점선()

    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    Selection.Borders(xlEdgeLeft).LineStyle = xlNone
    Selection.Borders(xlEdgeTop).LineStyle = xlNone
    Selection.Borders(xlEdgeBottom).LineStyle = xlNone
    Selection.Borders(xlEdgeRight).LineStyle = xlNone
''    Selection.Borders(xlInsideVertical).LineStyle = xlNone
    Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .Color = -9343379
        .TintAndShade = 0
        .Weight = xlHairline
    End With
End Sub

Sub s_Input_스타일을_Default로(rngInputArea As Range)
    Dim rngCell As Range
'170719

    If rngInputArea Is Nothing Then
        GoTo Exit_Rtn
    End If

    For Each rngCell In rngInputArea
        'With selection.Interior
        If rngCell.Style = "INPUT" Or _
           rngCell.Style = "CALC_SAVE" Then '170719
            With rngCell.Interior
                .Pattern = xlNone
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With

        End If
    Next rngCell

Exit_Rtn:
    Set rngCell = Nothing
End Sub

Sub s_모든시트_보이기()
    Dim wstSheet    As Worksheet

    For Each wstSheet In ActiveWorkbook.Worksheets
        wstSheet.Visible = xlSheetVisible
    Next wstSheet

    Set wstSheet = Nothing

End Sub

Public Function f_blnIsView(sht) As Boolean
    Dim rngDef                  As Range
'
' 뷰 시트인지 확인
'

    On Error Resume Next

    Set rngDef = sht.Names("_Def_Input_Col_").RefersToRange

    f_blnIsView = (Err.Number = 0)

    Err.Clear

End Function

Sub s_Def_Input_Col_이름정의_추가()
    Dim strName             As String
    Dim strRefersToR1C1     As String

    '이름이 이미 존재하는지 Check
    If f_blnIsView(ActiveSheet) Then
        MsgBox "이름이 이미 존재함", vbCritical, vbNullString
        GoTo Exit_Rtn
    End If

    '_Def_Input_Col_ 이름을 현재 시트에 추가해주는 작업
    '[A1] Cell에 이름을 지정한다. Pivot Sheet 등을 View Sheet로 인식시키려고 사용함
    '이름정의 생성
    strName = "_Def_Input_Col_"
    strRefersToR1C1 = "=xxx!R1C1"
    strRefersToR1C1 = Replace(strRefersToR1C1, "xxx!", "'" & ActiveSheet.Name & "'!")
    ActiveWorkbook.ActiveSheet.Names.Add Name:=strName, RefersToR1C1:=strRefersToR1C1

    MsgBox "끝", vbOKOnly, vbNullString

Exit_Rtn:
End Sub

Public Sub s_셀_테두리를_표준색상으로()
    Dim rngCell         As Range
    Dim lng배경색       As Long
    Dim lng표준색       As Long

    lng배경색 = vbWhite  'if cell border color is different than white, and has LineStyle

    lng표준색 = RGB(109, 110, 113)  'change its color to vbRed

    If TypeOf Selection Is Range Then
        For Each rngCell In Selection       'select your Range
            With rngCell
                With .Borders(xlEdgeLeft)
                    If .Color <> lng배경색 And .LineStyle <> xlNone Then .Color = lng표준색
                End With
                With .Borders(xlEdgeTop)
                    If .Color <> lng배경색 And .LineStyle <> xlNone Then .Color = lng표준색
                End With
                With .Borders(xlEdgeBottom)
                    If .Color <> lng배경색 And .LineStyle <> xlNone Then .Color = lng표준색
                End With
                With .Borders(xlEdgeRight)
                    If .Color <> lng배경색 And .LineStyle <> xlNone Then .Color = lng표준색
                End With
            End With
        Next
    End If
End Sub

Sub s_C1_Control_Width_표준화()
    Dim lngRowNum       As Long

    Sheets("C1").Select

    lngRowNum = 39
    Do
        lngRowNum = lngRowNum + 1
        If Cells(lngRowNum, "D") = "" Then
            GoTo Exit_Rtn
        End If

        Select Case Cells(lngRowNum, "H")
        Case "계획년월 :", "작업년월 :", "제품분류 :"
            Cells(lngRowNum, "K") = 60
        Case "버전 :"
            Cells(lngRowNum, "K") = 40
        Case "조회", "저장", "확정", "승인"
            Cells(lngRowNum, "K") = 60
        Case "승인 요청", "Upload", "Download"
            Cells(lngRowNum, "K") = 70
        Case "계획 초기화"
            Cells(lngRowNum, "K") = 80
        Case "진행상태 :"
            Cells(lngRowNum, "K") = 80
        Case "화면이동 :"
            Cells(lngRowNum, "K") = 60
        Case Else
            Select Case Cells(lngRowNum, "E")
            Case "Calendar"
                Cells(lngRowNum, "K") = 80
            Case "ComboBox"
                Select Case Cells(lngRowNum, "G")
                Case "CBO_PLAN_VERSION", "CBO_PLAN_VERSION2", "CBO_PLAN_VERSION3", "CBO_PLAN_VERSION4"
                    Cells(lngRowNum, "K") = 220
                Case "CBO_VIEW_LIST1", "CBO_VIEW_LIST2", "CBO_VIEW_LIST3", "CBO_VIEW_LIST4", "CBO_VIEW_LIST5", "CBO_VIEW_LIST6", "CBO_VIEW_LIST7", "CBO_VIEW_LIST8"
                    Cells(lngRowNum, "K") = 160
                Case "CBO_ARO_PRD_CLS_CD"
                    Cells(lngRowNum, "K") = 50
                Case Else
                    Cells(lngRowNum, "H").Select
                    MsgBox "새로 발견된 컨트롤"
                    GoTo Exit_Rtn
                End Select
            Case "InputBox"
                If Cells(lngRowNum, "F") = "Text" And Cells(lngRowNum - 1, "H").Value = "계획년월 :" Then
                    Cells(lngRowNum, "K") = 80
                ElseIf Cells(lngRowNum, "F") = "Text" And Cells(lngRowNum - 1, "H").Value = "버전 :" Then
                    Cells(lngRowNum, "K") = 220
                ElseIf Cells(lngRowNum, "F") = "Text" And Cells(lngRowNum - 1, "H").Value = "진행상태 :" Then
                    Cells(lngRowNum, "K") = 80
                Else
                    Cells(lngRowNum, "H").Select
                    MsgBox "새로 발견된 컨트롤"
                    GoTo Exit_Rtn
                End If
            Case Else
                If Cells(lngRowNum, "E") = "Label" And Cells(lngRowNum, "H").Formula = "=VS_PLAN_VERSION_STATUS" Then
                    Cells(lngRowNum, "K") = 80
                Else
                    Cells(lngRowNum, "H").Select
                    MsgBox "새로 발견된 컨트롤"
                    GoTo Exit_Rtn
                End If
            End Select
        End Select
    Loop

Exit_Rtn:
    MsgBox "작업 끝"
End Sub

Sub s_설계서를_Vn양식으로_변환()
    Dim rngActiveCell       As Range
    Dim wbkRpt              As Workbook
    Dim wbkTmp              As Workbook
    Dim strRefersToR1C1     As String
    Dim strName             As String
    Dim namName             As Name
    Dim varDummy            As Variant
    Dim lngRowNum   As Long

    Set rngActiveCell = Selection
    Set wbkRpt = ThisWorkbook

    With Application
        .Calculation = xlCalculationManual
        .EnableEvents = False
    End With

    If MsgBox("혹시 문제가 생길 수 도 있으니 반드시 백업을 받은 후 작업하세요" & vbCrLf & _
              "Template_Case1의 양식을 복사해옵니다." & vbCrLf & _
              "Template_Case1 열어 놓으세요." & vbCrLf & _
              "실적 시작 월의 첫번째 데이터가 위치한 셀을 기준으로 작업합니다." & vbCrLf & _
              "셀을 잘못선택후 작업하면 양식이 깨질 수 있습니다." & vbCrLf & _
              "계속할까요?", vbQuestion + vbYesNo + vbDefaultButton2, vbNullString) = vbNo Then
        GoTo Exit_Rtn
    End If

    If rngActiveCell.Cells.count <> 1 Then
        MsgBox "두개 이상의 셀을 선택하면 안됨", vbCritical, vbNullString
        GoTo Exit_Rtn
    End If

    If (rngActiveCell.Row > 101) Or (rngActiveCell.Column > 53) Then
        MsgBox "자동화 할 수 없는 설계서 임" & vbCrLf & _
               "선택한 데이터의 시작 셀은 [BA101]보다 위에 있어야 하며, 좌측에 있어야 함 ", vbCritical, vbNullString
        GoTo Exit_Rtn
    End If

    On Error Resume Next
    Set wbkTmp = Workbooks("Template_Case1.xls")
    If wbkTmp Is Nothing Then
        MsgBox "Template_Case1 을 찾을 수 없습니다", vbCritical, vbNullString
        GoTo Exit_Rtn
    End If
    On Error GoTo 0

    If rngActiveCell.Row < 101 Then
        Rows(1).Resize(101 - rngActiveCell.Row).Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    End If

    If rngActiveCell.Column < 53 Then
        Columns(1).Resize(, 53 - rngActiveCell.Column).Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    End If

    'Setup 영역 복사
    wbkTmp.Sheets("V1").Rows("1:71").Copy ActiveSheet.Rows(1)
    wbkTmp.Sheets("V1").Columns("A:AN").Copy ActiveSheet.Columns(1)

    '이름정의 생성
    For Each namName In Workbooks("Template_Case1.xls").Sheets("V1").Names
        If namName.Name Like "'V1'!_*" Then
            strName = namName.Name
            strRefersToR1C1 = namName.RefersToR1C1

            strName = Mid(strName, 6)
            strRefersToR1C1 = Replace(strRefersToR1C1, "=V1!", "='" & ActiveSheet.Name & "'!")

            Debug.Print namName.Name, namName.RefersToR1C1
            Debug.Print strName, strRefersToR1C1
            ActiveWorkbook.ActiveSheet.Names.Add Name:=strName, RefersToR1C1:=strRefersToR1C1
        End If
    Next namName

    'D1, D2 시트 생성
    '  이미 존재하면 무시함. D1, D2 시트가 없으면 Link를 끊을때 DRM이 개입하여 매우 느려짐
    'D1 시트 생성
    On Error Resume Next
        lngRowNum = Sheets("D1").Cells(1).Row
    On Error GoTo 0
    If lngRowNum <> 1 Then
        Sheets.Add After:=Sheets(Sheets.count)
        ActiveSheet.Name = "D1"
    End If

    'D2 시트 생성
    On Error Resume Next
        lngRowNum = Sheets("D2").Cells(1).Row
    On Error GoTo 0
    If lngRowNum <> 1 Then
        Sheets.Add After:=Sheets(Sheets.count)
        ActiveSheet.Name = "D2"
    End If

    '수식 Link 제거
    Cells.Replace What:="[Template_Case1.xls]", Replacement:="", LookAt:= _
        xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    '8행의 수식이 Template에 잘 못 되어 있음. 바로잡는 작업
    Range("BN8:CQ8").FormulaR1C1 = "=IF(R[-5]C = ""I"",  'M1'!R[-1]C[-50],  #N/A)"

    'AJ 컬럼의 Setting 값 지움
    Range("AJ3").Resize(40, 1).ClearContents

    '폰트
    With Cells.Font
        .Name = "맑은 고딕"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
        .Underline = xlUnderlineStyleNone
        .TintAndShade = 0
        .ThemeFont = xlThemeFontMinor
        .Size = 10
    End With

    '틀 고정 해제
    ActiveWindow.FreezePanes = True

    Range("BA101").Select

    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

    MsgBox "끝" & vbCrLf & _
           "수식 Link 제거할 경우 DRM때문에 너무 느리기 때문에 D1, D2 시트가 없을 경우자동으로 생성함" & vbCrLf & _
           "8행의 수식 확인... Template의 수식이 잘못되어있음", _
           vbOKOnly, vbNullString

Exit_Rtn:
    Set rngActiveCell = Nothing
    Set wbkRpt = Nothing

    With Application
        .Calculation = xlCalculationAutomatic
        .EnableEvents = True
    End With

End Sub

Sub s_행열그룹_해제()
    Dim lngIdx  As Long

    '행/열 그룹 해제
    On Error Resume Next
    For lngIdx = 1 To 10
        Columns.Ungroup
        Rows.Ungroup
    Next lngIdx
    On Error GoTo 0
End Sub

Sub s_셀서식_FS_P0()
    Selection.NumberFormatLocal = "_-* #,##0_-;[빨강]-#,##0_-;_-* ""-""_-;_-@_-"
End Sub

Sub s_작업시트로()
    Sheets("CF").Select
End Sub

Sub s_설계서_Cell_색칠하기()
    Dim rngSelection        As Range
    Dim rngSpecial          As Range
    Dim rngCell             As Range

    '수식: 회색
    '조회: 하늘색
   
   
    With Application
        .EnableEvents = False
        .Calculation = xlCalculationManual
    End With
       
    Set rngSelection = Selection
   
    Call S_궤선_내부포함_전체를_가는선
   
    '글자색: 검은색
    With rngSelection.Font
        .ColorIndex = xlAutomatic
        .TintAndShade = 0
    End With
   
    '셀 색상 초기화
    With rngSelection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
   
    '값: 하늘색으로 칠한다
    On Error Resume Next
    Set rngSpecial = rngSelection.SpecialCells(xlCellTypeConstants, 23)
    On Error GoTo 0
    If rngSpecial Is Nothing Then
    Else
        With rngSpecial.Interior
            .Pattern = xlSolid
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorAccent1
            .TintAndShade = 0.599993896298105
            .PatternTintAndShade = 0
        End With
    End If
   
    '수식: 회색으로 칠한다
    On Error Resume Next
    Set rngSpecial = Nothing
    Set rngSpecial = rngSelection.SpecialCells(xlCellTypeFormulas, 23)
    On Error GoTo 0
    If rngSpecial Is Nothing Then
    Else
        With rngSpecial.Interior
            .PatternColorIndex = xlAutomatic
            .ThemeColor = xlThemeColorDark1
            .TintAndShade = -0.149998474074526
            .PatternTintAndShade = 0
        End With
    End If
   
    '외부 참조 수식: 보라색
    'Dn 시트에서 읽어오는 수식: 하늘색
    '실적/계획 구분 수식: 녹색
    If rngSpecial Is Nothing Then
    Else
        For Each rngCell In rngSpecial
            If InStr(1, rngCell.Formula, "!", vbTextCompare) > 0 Then
                If InStr(1, rngCell.Formula, "'D1'!", vbTextCompare) > 0 Then
                    If rngCell.Formula Like "=IF(*" Then
                        '실적,계획 구분 수식이면
                        With rngCell.Interior
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorAccent3
                            .TintAndShade = 0.399975585192419
                            .PatternTintAndShade = 0
                        End With
                    Else
                        With rngCell.Interior
                            .Pattern = xlSolid
                            .PatternColorIndex = xlAutomatic
                            .ThemeColor = xlThemeColorAccent5
                            .TintAndShade = 0.399975585192419
                            .PatternTintAndShade = 0
                        End With
                    End If
                Else
                    With rngCell.Interior
                        .Pattern = xlSolid
                        .PatternColorIndex = xlAutomatic
                        .ThemeColor = xlThemeColorAccent4
                        .TintAndShade = 0.399975585192419
                        .PatternTintAndShade = 0
                    End With
                End If
            End If
        Next rngCell
    End If
   
    Set rngSelection = Nothing
    Set rngSpecial = Nothing
   
    With Application
        .EnableEvents = True
        .Calculation = xlCalculationAutomatic
    End With
   
End Sub

Sub s_실적_계획_공통구간_수식_변환()
    Dim strFmr_Org      As String
    Dim strFmr_Tpl      As String
    Dim strFmr_New      As String
    Dim rngCell         As Range
    Dim lng실적여부행   As Long
    Dim lng년월수식행   As Long
    Dim lng조회키컬럼   As Long

    lng실적여부행 = 75
    lng년월수식행 = 60
    lng조회키컬럼 = Range("AC101").Column

    With Application
        .Calculation = xlCalculationAutomatic
    End With

    strFmr_Tpl = "=IF(x실적여부, SUMIF('D1'!$B:$B,  x년월수식 & x조회Key,  'D1'!$G:$G), xFormula)"

    For Each rngCell In Selection

       strFmr_Org = Mid(rngCell.Formula, 2)

       strFmr_New = strFmr_Tpl
       strFmr_New = Replace(strFmr_New, "x실적여부", Cells(lng실적여부행, rngCell.Column).AddressLocal(ColumnAbsolute:=False))
       strFmr_New = Replace(strFmr_New, "x년월수식", Cells(lng년월수식행, rngCell.Column).AddressLocal(ColumnAbsolute:=False))
       strFmr_New = Replace(strFmr_New, "x조회Key", Cells(rngCell.Row, lng조회키컬럼).AddressLocal(RowAbsolute:=False))
       strFmr_New = Replace(strFmr_New, "xFormula", strFmr_Org)

       rngCell.Formula = strFmr_New
    Next rngCell
    Debug.Print strFmr_New

    Set rngCell = Nothing

    With Application
        .Calculation = xlCalculationAutomatic
    End With
End Sub

Sub s_C1_실행방식_개발_상태로()
    'C1 시트의 컨트롤의 실행방식의 Initialize 를 Click으로 변경

    With Application
         .Calculation = xlCalculationManual
         .EnableEvents = False
    End With

    '실행방식 컬럼을 백업
    With Sheets("C1")
        If .Range("AE37").Value <> "" Then
            MsgBox "이미 백업 받아둔 실행방식이 존재 함", vbCritical, vbNullString
            GoTo Exit_Rtn
        End If

        .Range("M37").Resize(100, 1).Copy .Range("AE37")

        .Range("M37").Resize(100, 1).Replace What:="Initialize", Replacement:="Click", LookAt:= _
                xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
                ReplaceFormat:=False
    End With

Exit_Rtn:

    With Application
         .Calculation = xlCalculationAutomatic
         .EnableEvents = True
    End With
End Sub

Sub s_C1_실행방식_내보내기_상태로()
    'C1 시트의 컨트롤의 실행방식의 Click을 Initialize 으로 변경

    With Application
         .Calculation = xlCalculationManual
         .EnableEvents = False
    End With

    '백업 받아둔 실행방식 컬럼을 복구
    With Sheets("C1")
        If .Range("AE37").Value = "" Then
            MsgBox "백업 받아둔 실행방식이 없음", vbCritical, vbNullString
            GoTo Exit_Rtn
        End If

        .Range("AE37").Resize(100, 1).Copy .Range("M37")
        .Range("AE37").Resize(100, 1).Clear
    End With

Exit_Rtn:
    With Application
         .Calculation = xlCalculationAutomatic
         .EnableEvents = True
    End With
End Sub

모듈명 : modCommon

'탬플릿 숨김처리 이벤트
Sub SAMPLE_VIEW()

    Application.Run "iMTX_CSS.xlam!SAMPLE_VIEW"
   

End Sub

Sub PreviewCondition()

    Application.Run "iMTX_CSS.xlam!PreviewCondition", ActiveCell
   

End Sub

Sub aaaaaaaaaaaaaaaaaa()

    Dim r As Range
   
    For Each r In Selection
        If IsEmpty(r.Value) Then
        Else
            r.Formula = "=" & Range(r.Formula).Address
        End If
   
    Next r
   
End Sub