[엑셀VBA] 목차 만들기(Sheet Hyperlink)

안녕하십니까 춤추는 초코칩입니다.

업무 중에 필요한 엑셀 VBA를 기록하고 있습니다.

"시트명을 클릭하면, 해당 시트로 이동하는 목차"

 엑셀을 다루다 보면, 가끔 몇십 개의 시트가 하나의 파일에 있는 경우가 있습니다. 목차도 없고, 목차가 있다 하더라도 시트 링크가 없을 때에는 감정이 올라오기도 합니다. 몇 개 정도야 hyperlink를 사용하기도 하지만 10개가 넘어가고 20개가 넘어가면 반복적인 일에 피로감을 느낍니다. 그래서 "시트명을 클릭하면, 해당 시트로 이동하는 목차"를 만들어 보겠습니다.

 

 우선 아래와 같은 파일이 있다고 가정하겠습니다. 첫 번째 시트는 목차 시트입니다. 그리고 두 번째 시트부터 다섯번째 시트까지 4개 시트에 대한 목록을 만들어 보겠습니다.

첫번째 방법. 직접 링크 만들기

 A2 셀에 "가"를 입력하고 오른쪽 클릭 버튼으로 "하이퍼링크" 메뉴를 선택합니다.

 "현재 문서"에서 "가" 시트를 선택하고 "확인" 버튼을 클릭합니다.

 첫번째 링크가 완성되었습니다. 이제 이걸 3번 더 반복합니다.

 몇 번의 반복을 통해서 목차를 완성했습니다.

두번째 방법. VBA로 링크 만들기

 

Sub HyperlinkSheets()

    '시트 개수 파악하기
    cntsheet = Sheets.Count
    
    '시트 개수만큼 반복하기
    For i = 2 To cntsheet
    
        '각 시트로 가는 링크 만들기
        Sheets(1).Hyperlinks.Add _
            Anchor:=Sheets(1).Cells(i, 1), _
            Address:="", _
            SubAddress:=Sheets(i).Name & "!A1", _
            TextToDisplay:=Sheets(i).Name
            
    Next i
    
End Sub

 

 위 코드를 실행하면, 직접 만든 것과 동일한 링크가 만들어집니다.

 근데, 한 가지 문제점이 생겼습니다. "가", "나", "다" 시트는 잘 작동하는데, 다섯 번째 시트인 "라 마"시트를 클릭하면, 아래와 같은 오류 메시지('참조가 잘못되었습니다.')가 팝업 됩니다.

  이 건 SubAddress의 값이 잘 못 들어가 있기 때문입니다. 직접 링크를 할 때 혹시 눈치채신 분이 계셨는지 모르겠지만, "라 마" 시트에만 앞 뒤로 따옴표(' ')가 있는 것을 확인할 수 있습니다. 시트명에 띄어쓰기(Space)가 있기 때문입니다. 그래서 SubAddress에 따옴표(' ')를 추가해 보겠습니다. 기 존 시트도 잘 작동하는지 확인해 봐야 합니다.

Sub HyperlinkSheets2()

    '시트 개수 파악하기
    cntsheet = Sheets.Count
    
    '시트 개수만큼 반복하기
    For i = 2 To cntsheet
    
        '각 시트로 가는 링크 만들기
        Sheets(1).Hyperlinks.Add _
            Anchor:=Sheets(1).Cells(i, 1), _
            Address:="", _
            SubAddress:="'" & Sheets(i).Name & "'!A1", _
            TextToDisplay:=Sheets(i).Name
            
    Next i
    
End Sub

 

 이렇게 만들어진 링크는 모든 시트가 잘 작동합니다. 이를 이용해서 모든 시트에 "목차" 시트로 가는 링크도 추가해 보겠습니다. 최종 코드는 아래와 같습니다.

 

Sub HyperlinkSheets3()

    '시트 개수 파악하기
    cntsheet = Sheets.Count
    
    '시트 개수만큼 반복하기
    For i = 2 To cntsheet
    
        '각 시트로 가는 링크 만들기
        Sheets(1).Hyperlinks.Add _
            Anchor:=Sheets(1).Cells(i, 1), _
            Address:="", _
            SubAddress:="'" & Sheets(i).Name & "'!A1", _
            TextToDisplay:=Sheets(i).Name
            
        '첫시트로 가는 링크 만들기
        Sheets(i).Hyperlinks.Add _
            Anchor:=Sheets(i).Cells(1, 1), _
            Address:="", _
            SubAddress:="'" & Sheets(1).Name & "'!A1", _
            TextToDisplay:="처음으로"
            
    Next i
    
End Sub

 

 위 코드를 실행하면, 모든 시트의 A1에 아래와 같이 "처음으로"라는 값이 입력되며, 이를 클릭했을 때, 목차!A1 으로 이동하게 됩니다.