Cara Membatasi Scroll Area Pada Worksheet

Pada Excel versi 2007 atau yang lebih baru, area worksheet dibatasi oleh jumlah kolom maksimal 16.364 dan jumlah baris maksimal 1.048.576.  Namun pada kenyataanya, user lebih sering menggunakan sebagian kecil saja dari jumlah sel yang tersedia dalam worksheet. Penggunaan sebagian area dalam worksheet tanpa pembatasan scroll area terkadang cukup menyulitkan dalam melakukan observasi lembar kerja excel. Karena itulah maka disarankan untuk membatasi scroll area.

Keuntungan pembatasan scroll area:

• Pembatasan scroll area memudahkan dalam melakukan observasi dan navigasi workhsheet.
• Jika anda pembuat tempate laporan, maka anda bisa menyembunyikan data yang tidak ingin diketahui oleh user di luar lokasi scroll area.

Pembatasan scroll area dapat dilakukan dengan beberapa cara, antara lain:

• Menyembunyikan kolom dan baris tidak terpakai
• Menentukan valid range
• Mengaktifkan range terpakai (used range)

Menyembunyikan kolom dan baris tidak terpakai

Tehnik paling sederhana untuk membuat batasan lembar kerja excel adalah dengan cara menyembunyikan baris dan kolom tidak terpakai.

Langkahnya cukup mudah:

• Seleksi semua kolom yang tidak digunakan, kemudian klik kanan ➨ hide
• Dengan cara serupa, seleksi semua baris yang tidak digunakan, kemudian klik kanan ➨ hide

Hasilnya dapat dilihat seperti screenshot di bawah ini.

Dari gambar di atas, kita dapat melihat, excel hanya menampilkan kolom dan baris untuk area yang digunakan saja, yaitu baris 1 s/d 15 dan kolom A s/d H. Hal tersebut karena area yang tidak digunakan sudah disembunyikan (di-hide).

Menentukan Valid Range Spesifik

Metode kedua untuk menetapkan batasan scroll area adalah dengan cara menentukan valid range spesifik. Valid range dapat di-setting melalui property worksheet.

Caranya:

• Klik kanan pada tab sheet yang akan ditentukan sroll area nya.
• Klik view Code, sehingga akan masuk ke VBA editor. 

• Di dalam vba editor, cari jendela properties. Jika jendela properties belum terlihat, tekan F4 untuk memunculkannya.
• Pada jendela properties, pada field ScrollArea, ketikan valid range yang anda inginkan. Contoh pengisian seperti dalam gambar di bawah ini.

Keterangan: tanda absolute ($) akan muncul otomatis meskipun kita mengetik valid range tanpa tanda dolar $.

Setelah selesai, kembali ke worksheet dan kita bisa buktikan bahwa scroll range akan dibatasi sesuai valid range yang kita ketikan di jendela properties. Jika scroll area yang kita gunakan sedikit saja atau kurang dari ukuran range yang terlihat (visible range), maka kolom dan baris diluar scroll area masih akan nampak. Namun kita  tidak bisa menyeleksi sel / range dan mengetik pada sel diluar scroll area. Silahkan dibuktikan sendiri.

Sayangnya, kita tidak bisa menyimpan setting scrollarea yang dibuat dengan cara edit properties karena scroll area akan di-reset kembali setelah close file.

Untuk mengatasi hal tersebut, kita bisa menggunakan code vba untuk menetapkan scroll area. Code ini ditempatkan pada even worksheet_activate sehingga akan dijalankan setiap kali worksheet yang disisipkan code ini diaktifkan.

Caranya:

• Klik kanan pada tab sheet yang akan dibatasi sroll area nya.
• Klik view Code, sehingga akan masuk ke VBA editor.
• Pada jendela project explorer, double clik nama sheet yang akan dibatasi scroll area nya sehingga muncul muncul object sheet tersebut.
• Ketik atau copy code berikut pada modul ojbect sheet:


Private Sub Worksheet_Activate()
Me.ScrollArea = "A1:J50"
End Sub


Contoh penempatan code dapat dilihat pada screenshot di bawah ini.

Gambar di atas menunjukan contoh penempatan code untuk membatasi scroll area sheet 3  pada range A1:J50. Code berjalan setiap kali sheet 3 diaktifkan (berpindah dari sheet lain ke sheet 3).

Untuk membuat batasan scroll area pada sheet lainnya, silahkan masuk kembali ke VBA editor, kemudian pada jendela project explorer, double klik nama sheet yang akan disisipkan code. Selanjutnya ketik code yang sama dengan contoh sebelumnya pada modul object sheet yang diinginkan.

Kita juga bisa berexperimen dengan merubah range scroll area. Silahkan range A1:J50 diedit kembali dengan range yang anda inginkan.

Bagaimana Jika kita ingin mengedit sel di luar scroll area?

Sebagaimana penjelasan di atas, setelah kita setting scroll area maka kita tidak bisa mengedit sel di luar scroll area. Untuk itu kita harus menghapus setting scroll area terlebih dahulu jika ingin memodifikasi area sheet di luar scrollarea.

Kita bisa menghapus secara manual dengan cara menghapus scroll area pada property worsksheet. Cara yang lebih cepat dan disarankan adalah dengan menggunakan macro / VBA. Adapun codenya sangat sederhana.

Misalnya:

Sub deleteScrollArea()
ActiveSheet.ScrollArea = ""
End Sub


Untuk menempatkan code pada module VBA, ikuti langkah berikut:

• Masuk ke jendela VBA dengan cara tekan Alt + F11
• Jika module standar belum tersedia, Klik menu Insert ➨ Module
• Maka akan muncul modul baru (misalnya : Module1)
• Tempatkan code pada module 1. Bisa dilakukan dengan copy paste contoh code di atas, maupun dengan mengetiknya sendiri.

Screenshot di bawah ini menggambarkan langkah-langkah penempatan code pada modul standar.

Setelah langkah di atas diikuti satu-persatu, maka kita bisa menghapus scroll range menggunakan makro.

Caranya:

• Tekan Alt + F8 untuk memunculkan list Macro 
• Pilih macro yang sudah kita buat sebelumnya (macro deleteScrollArea)
• Lalu klik Run

Mengaktifkan Range Terpakai (Used Range)

Cara Ketiga dan yang paling fleksibel adalah dengan menetapkan used range sebagai scroll area. Untuk menempatkan code, caranya serupa dengan cara kedua yaitu:

• Klik kanan tab sheet yang ingin di-setting scroll area nya
• Pada module object sheet terpilih, ketik atau copy code berikut:


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Me.ScrollArea = Me.UsedRange.Address
End Sub



• Setelah itu, keluar kembali dari VBA editor.

Karena code diatas akan dijalankan ketika sheet diaktifkan maka kita harus pindah dulu ke sheet lain, kemudian masuk kembali (mengaktifkan) ke sheet dimana code sudah disisipkan. Maka kita akan mendapati sheet dengan batasan scroll area sudah terpasang.

Jika kita ingin mengedit areal di luar used range, maka kita bisa mereset sementara scroll area dengan cara yang sudah dijelaskan sebelumnya

Sampai disini, mudahan-mudahan catatan excel mengenai cara membatasi scroll area dapat difahami dan semoga bermanfaat.
Thks

Artikel terkait:

• Cara mencegah save as, print, dan insert sheet.
• Membuat Daftar Isi Otomatis
• Macro untuk find & replance text dalam comment.

Referensi:
David Raina & Hawley 2007, Excel Hack, Tips & Tools for Streamlining Your Spreadsheet, 2nd edition. O’Reilly Media, Inc

Mencegah Save As, Print dan Insert Sheet

Sebagai pembuat sebuah template laporan, mungkin kita tidak menginginkan user melakukan tindakan tertentu. Misalnya melakukan save as, print, insert sheet, dan sebagainya. Dengan menambahkan sedikit code vba, kita bisa me-manage interaksi antara user dengan spreadsheet yang kita buat. Nah, jex-cel telah mencatat beberapa contoh code yang dapat digunakan untuk mencegah user menjalankan command tertentu di excel.

1. Mencegah Save As
2. Mencegah print
3. Mencegah print sheet tertentu
4. Mencegah insert sheet

Mencegah User Melakukan Save As

Atas dasar alasan tertentu, mungkin kita tidak ingin user melakukan save as dan mengganti nama file template yang kita buat. Untuk itu, kita bisa menyisipkan code pada module workbook.

• Jika kita membuat file baru, maka save dulu file tersebut sebelum disisipkan code. Jika workbook baru belum di save, dan kemudian kita menyisipkan code cegah save as maka file tersebut tidak akan bisa di-save sama sekali.
• Tekan Alt F11 untuk masuk ke VBA editor.
• Masuk ke module object Thisworkbook (dengan cara double klik object thisworkbook di jendela project explorer)
• Copy code berikut pada module object thisworkbook.

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim responUser As Long
If SaveAsUI = True Then
responUser = MsgBox("Maaf, file tidak bisa di save as dengan nama lain" _
& Chr(10) & "Apakah anda akan menyimpan saja?", vbQuestion + vbOKCancel)
Cancel = (responUser = vbCancel)
If Cancel = False Then Me.Save
Cancel = True
End If
End Sub



Letak code dalam modul VBA dapat dilihat dalam screenshot berikut:

Setelah code di copy paste atau diketik di modul object thisworkbook, silahkan coba lakukan proses Save As

Maka akan muncul pesan seperti pada gambar di bawah ini:

Jika user menekan tombol ok, maka file akan disimpan biasa (bukan save as) dan tidak ada perubahan nama file. Jika user menekan cancel, maka perubahan tidak akan tersimpan.

Mencegah User Melakukan Print.

Karena melihat banyak kertas print berakhir di tempat sampah  atau tercecer / menumpuk di bawah meja kerja, kita bisa berinisiatif untuk membantu menghemat kertas dengan cara mencegah user untuk melakukan print. Untuk itu kita bisa menyisipkan code pada event before print di module thisworkbook.

• Tekan Alt F11 untuk masuk ke VBA editor
• Masuk ke module object Thisworkbook (dengan cara double klik object thisworkbook di jendela project explorer)
• Copy code berikut pada module object thisworkbook.


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
MsgBox "Maaf anda tidak bisa print buku kerja ini", vbInformation
End Sub


Silahkan test dengan cara menjalankan command print (File --> Print --> Tombol Print)

Maka user akan melihat pesan pemberitahuan tidak bisa  print,  seperti gambar di bawah ini.

Kita bisa saja menyisipkan code tanpa code pesan (msgbox) seperti berikut:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
End Sub


Code tersebut sebenarnya sudah cukup untuk mencegah user melakukan print. Namun jika tidak ada pesan pemberitahuan, maka kemungkinan besar user akan menyangka ada error dan mengejar orang IT untuk memperbaikinya. Dengan kata lain, msgbox meski opsional tetapi sangat penting untuk memberitahu user mengenai apa yang tidak boleh dilakukan maupun yang harus dilakukan sehingga bisa mencegah adanya kesalahpahaman.

Mencegah print sheet tertentu

Jika kita ingin mencegah user melakukan print sheet tertentu saja maka gunakan code berikut:


Private Sub Workbook_BeforePrint(Cancel As Boolean)
Select Case ActiveSheet.Name
Case "Sheet1", "Sheet2"
Cancel = True
MsgBox "Maaf anda tidak bisa print " & ActiveSheet.Name, vbInformation
End Select
End Sub


Catatan:  dalam contoh di atas, nama sheet yang dicegah untuk diprint adalah "Sheet1", "Sheet2". Kita dapat merubah dan menambah sheet sesuai keperluan.
Misalnya:

Case "data 1", "data 2", "data 3"

Mencegah Insert Sheet

Excel sebenarnya sudah menyediakan fiture Protect Workbook’s Structure yang dapat mencegah user untuk bisa men-delete worksheet, merubah susunan sheet, merubah nama sheet dan sebagainya. Namun adakalanya kita hanya ingin mencegah user untuk menambahkan sheet baru dan tetap mengizinkan perubahan struktur  lainnya.

Untuk mencegah user menambahkan sheet baru, lakukan langkah seperti contoh sebelumnya. Bedanya hanya pada code yang digunakan.

• Masuk ke VBA editor dengan cara tekan Alt + 11
• Selanjutnya Masuk ke module object Thisworkbook dengan cara double klik object thisworkbook di jendela project explorer
• Selanjutnya pada module object thisworkbook, copy code di bawah ini:


Private Sub Workbook_NewSheet(ByVal Sh As Object)
Application.DisplayAlerts = False
MsgBox "Maaf, anda tidak bisa menambahkan sheet baru", vbInformation
Sh.Delete
Application.DisplayAlerts = True
End Sub


Silahkan lakukan test dengan cara mencoba tambahkan sheet baru. Maka akan tampil pesan seperti screenshot di bawah ini.

Sekian dulu catatan mengenai contoh-contoh code untuk mencegah user melakukan tindakan tertentu pada excel, yaitu: mencegah save as, mencegah print dan mencegah insert sheet. Semoga bermanfaat.

Artikel terkait:
Teknik Find & Replace Text dalam Comment Box
Cara Entri Data Pada Beberapa Sheet Sekaligus
Cara Membuat Daftar Isi Otomatis

Referensi:
David Raina & Hawley 2007, Excel Hack, Tips & Tools for Streamlining Your Spreadsheet, 2nd edition. O’Reilly Media, Inc

Co-Cote Senjata Pamungkas Pejuang Insert Comment

Co-Cote, excel comment converter diperuntukan untuk teman-teman, terutama para pejuang laporan yang sedang bekerja keras mengerjakan insert comment pada data laporan berformat excel. Sebuah pekerjaan yang sangat menguras waktu, fikiran dan tenaga terlebih bila harus menambahkan data berupa tabel pada kotak komentar. Atas dasar tersebut, Co-Cote hadir sebagai solusi dan senjata pamungkas bagi pejuang insert comment.

Co-Cote sangat efektif digunakan terutama jika kita perlu menambahkan tabel pada kotak  komentar (proses insert comment). Cara ini sangat terbukti lebih cepat dibandingkan dengan cara manual yang umumnya dilakukan pengguna excel, yaitu dengan cara mengcopy tabel dari dari range sel ke notepad terlebih dahulu, kemudian di-copy lagi dari notepad ke comment box, dan dilanjutkan dengan mengeditnya secara manual untuk mendapatkan tampilan tabel yang rapi dalam comment box.

Saatnya untuk meninggalkan cara manual, dan beralihlah ke Co-Cote.

Terlebih dahulu, silahkan download Add-In Co-Cote dari link di bawah ini:

https://depositfiles.com/files/kit53ky2a

Setelah didownload, lalu ikuti  panduan cara penggunaan add-in Co-Cote sebagai berikut:

1. Install Co-Cote

2. Pengaturan Co-Cote

3. Cara menggunakan Co-Cote

Cara Instalasi Co-Cote pada Excel 2003

• Klik menu File --> Save As -->
• Pada field Save As Type, pilih "Microsoft Office Excel Add-In"
• Klik Save.
• Close Aplikasi Microsoft Excel, kemudian buka lagi aplikasi excel atau file excel yang lain, tetapi jangan membuka file Co-Cote lagi.
• Klik menu Tools --> Add-Ins..
• Pada jendela Add-Ins, tik Co-Cote

• Co-Cote sudah bisa digunakan pada excel 2003

Install Co-Cote pada excel 2007 atau yang lebih tinggi.

Co-Cote dibuat menggunakan excel 2003, namun tetap bisa digunakan pada excel versi yang lebih tinggi (tested pada excel 2007, 2010, 2013, 2016, system operasi Windows)

Cara Instalasi Co-Cote pada Excel 2007, 2010 atau versi yang lebih tinggi.

• Klik tab File (excel 2010, 2013, 2016) atau klik Office Button         (excel 2007)  --> Save As -->
• Klik Browse (excel 2013, 2016)
• Pada field Save As Type, pilih "Excel 97-2003 Add-In"
• Klik Save
• Close Aplikasi Microsoft Excel.
• Buka dan mulai lagi aplikasi excel, tetapi  jangan buka file co-cote.
• Klik tab File (excel 2010, 2013, 2016)  dan kemudian klik Options, atau klik Office Buton pada excel 2007 diteruskan dengan klik Excel Options.

• Pada Jendela Excel Options, masuk ke tab Add-Ins

• Klik tombol Go...
• Pada jendela Add-Ins, tik Co-Cote

• Klik OK
• Add-In Co-Cote sudah siap untuk digunakan.

Pengaturan Add-In Co-Cote

Pengaturan add-in sangat penting, terutama terkait penggunaan jenis font. Hal ini karena Co-Cote akan memberikan hasil yang baik jika menggunakan jenis font type monospace, yaitu jenis font yang memiliki lebar yang sama untuk setiap karakternya.

Contoh font type monospace:

- Courier New

- Arial monospaced for SAP

- Lucida Sans Typewriter

Untuk versi ini, setting Co-Cote hanya bisa diakses dengan cara menekan tombol short cut Ctrl + Shift + M, sehingga akan muncul dialog box setting co-cote seperti dalam gambar di bawah ini:

Keterangan setting Co-Cote:

Jarak spasi   = menentukan kerapatan jarak antar kolom jika digunakan untuk konversi tabel.

Jenis font      = Gunakan jenis font monospace. Misalnya Courier New. Jenis font tersebut akan di set sebagai font dalam comment box. Ini bisa berbeda dengan jenis font dalam data excel sebelum di konversi ke text comment.

Ukuran Font = menentukan ukuran / size huruf dalam comment box.

Cara Menggunakan Add In Co-Cote.

Sekarang, insert comment menjadi lebih mudah menggunakan Co-Cote

• Seleksi range data atau tabel yang akan dikonversi
• Tekan short cut Ctrl+Shift+B   atau klik kanan → Co-Cote...

• Selanjutnya akan muncul kotak input berisi perintah untuk memilih salah satu sel untuk menempatkan komentar.

Pilih satu sel menggunakan mouse, lalu tekan Ok

Maka tabel akan dikonversi menjadi text komentar seperti terlihat dalam gambar berikut:

 

Tips sangat penting:

1. Jangan biarkan ada sel kosong pada tabel yang akan dikonversi karena akan menyebabkan hasil yang tidak  rapi. Perhatikan contoh tabel di atas. Jika ada nilai yang kosong maka bisa ditambahkan 0 atau apapun yang penting tidak kosong melompong alias blank, misalnya dengan cara menambahkan spasi.
2. untuk membuat garis tabel yang memisahkan data bagian atas dengan bawahnya maka bisa dilakukan dengan menmbahkan 2 strip datar (lihat contoh di atas), maka setelah dikonversi, strip datar tersebut akan menjadi solid.

Sampai di sini. Semoga add-in Co-Cote bisa bermanfaat bagi anda.

Add-in Co-Cote ini masih dalam proses pengembangan dan insya Alloh akan ditambahkan beberapa fiture lainnya. Oleh karenanya feed-back dan saran dari pembaca sangat diharapkan untuk meningkatkan perbaikan add-in Co-Cote ini ke depannya. Silahkan tinggalkan feedback di komentar.