Membuat Daftar Isi Otomatis

Halo teman, jumpa lagi dengan je-xcel. Setelah sekian usang vakum menulis sebab kesibukan kerja. Alhamdulillah, kali ini saya sanggup kembali sedikit berbagi  tips excel.

Dalam kesempatan ini kita akan membahas bagaimana menciptakan daftar isi atau index worksheet secara otomatis.

Dengan semakin banyaknya lembar kerja atau worksheet pada sebuah file excel, maka mungkin kita akan merasa kesulitan untuk navigasi antar sheet. Nah, untuk itu dibutuhkan sebuah alat bantu sebuah worksheet berisi list index beserta hyperlinknya yang sanggup di-generate secara otomatis.

Membahas mengenai otomatisasi di excel, maka tentunya tidak sanggup lepas dari yang namanya VBA. Nah, dalam hal ini kita akan gunakan isyarat VBA untuk meng-generate list index dan hyperlinknya.

Anggaplah kita mempunyai sebuah file excel yang terdiri atas beberapa worksheet berisi data. Kemudian ada sebuah worksheet berisi index data atau daftar isi. Atau lebih jelasnya sanggup dilihat dalam screenshot di bawah ini.

Tugas selanjutnya yaitu bagaimana meng-generate daftar isi worksheet index dan menciptakan hyperlink ke data terkait, serta menciptakan hyperlink balik dari worksheet data menuju sheet index.

Adapun caranya sangat mudah, cukup ikuti langkah sederhana berikut ini:

1. Pastikan tab developer pada aplikasi excel anda sudah aktif (excel 2007 atau yang lebih baru)  dan pastikan setting macro security sudah enable.
2. Klik kanan pada tab sheet index, lalu klik view code.
   
   
   
3. Selanjutnya kita akan dibawa ke jendela VBA menyerupai terlihat pada gambar berikut:
   
   
   
4. Copy code berikut ke dalam private modul sheet1(index)
   
   Private Sub Worksheet_Activate()
   Dim ws As Worksheet, index As Integer
   Application.ScreenUpdating = False
   Me.Cells.Clear
   Me.Cells(1, 1).Name = "Index"
   Me.Cells(1, 1).Value = "Index"
   Me.Cells(1, 2).Value = "Keterangan"
   For Each ws In ThisWorkbook.Worksheets
     If ws.Name <> Me.Name Then
       index = index + 1
       Me.Cells(index + 1, 1).Value = index
       ws.Cells(1, 1).Name = "index" & index
       ws.Cells(1, 1).Value = "<< index"
       Me.Hyperlinks.Add Me.Cells(index + 1, 2), "", "index" & index, "Lihat Data", ws.Name
       ws.Hyperlinks.Add ws.Cells(1, 1), "", "index", "Lihat index", "<< Index"
     End If
   Next
   Application.ScreenUpdating = True
   End Sub
   
   
   
   
   
   
   
   
5. Kemudian close jendela VBA, selanjutnya kembali ke spreadsheet excel.
6. Sampai dengan tahap ini, code VBA sudah sanggup dipakai untuk meng-generate list index, menciptakan hyperlink ke sheet sasaran serta menciptakan link back dari sheet data ke sheet index.

Untuk mengambarkan bahwa code VBA sanggup bekerja dengan baik, silahkan dicoba cara kerjanya dan lihat jadinya dengan cara berpindah ke sheet lain selain sheet index, lalu kembali ke sheet index. Maka secara otomatis pada sheet index akan di-generate daftar isi berupa nomor index dilengkapi keterangannya sesuai nama-nama sheet yang ada dalam workbook excel. Selain itu pada masing-masing keterangan, sudah dilengkapi hyperlink yang mengarah pada worksheet terkait.

Jika perlu menambahkan sheet baru, atau merubah nama sheet data, maka tidak perlu report untuk mengedit list index, sebab code VBA akan menuntaskan kiprah ini secara otomatis setiap kali kita mengaktifkan sheet index.

Silahkan dicoba kembali dengan cara menambahkan sheet gres contohnya nama sheetnya “data baru”. Setelah itu, lalu kembali masuk ke sheet index. Maka kita akan mendapati sheet gres secara  otomatis terdaftar dalam index.

Selain itu pada sel A1 dari setiap sheet data akan tercipta secara otomatis hyperlink yang mengarah ke sheet index.

List index, hyperlink ke sheet data, serta hyperlink balik ke sheet index akan disegarkan secara otomatis setiap kali user masuk atau mengaktifkan sheet index. Disinilah letak manfaatnya sehingga user tidak perlu capek menciptakan hyperlink secara manual setiap kali ada perubahan pada nama sheet ataupun penambahan sheet baru.

Setelah selesai, maka file excel latihan ini sanggup di simpan. Jika memakai excel 2007 atau yang lebih baru, jangan lupa untuk save as sebagai  excel macro – enable workbook atau excel binary workbook. Jika tidak, maka code macro akan terhapus dan tidak sanggup digunakan.

Catatan: kalau code yang dicontohkan dalam tutorial ini tidak bekerja sesuai harapan, maka kemungkinan setting macro security pada aplikasi microsoft excel yang anda gunakan belum pas, sehingga macro tidak diizinkan untuk dijalankan. Silahkan periksa kembali setting macro security nya.

Demikian tips singkat mengenai bagaimana menciptakan list index atau daftar isi secara otomatis memakai VBA pada microsoft excel. Semoga bermanfaat.

Silahkan dibaca juga tips excel lainnya:

• Macro untuk menghapus hyperlink tanpa merubah format
• Cara cepat menciptakan ribuan hyperlink sekaligus
• Cara cepat isi sel kosong
• Mengatur sasaran hyperlink pada posisi yang tepat.