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

Mengekstrak Angka dari Text Data Entri

Meng-extract porsi angka dari sebuah entri data dapat dilakukan dengan berbagai cara. Jika sejumlah entri data mempunya pola urutan huruf dan angka yang tetap serta panjang text-nya konstan, maka kita bisa mengambil porsi angka dengan mudah menggunakan fungsi pengolah text seperti LEFT, RIGHT dan MID dikombinasikan dengan fungsi CONCATENATE. Namun jika pola urutan kombinasi angka dan huruf tidak tetap, maka fungsi standar pengolah text di excel tidak bisa menyelesaikan kasus tersebut. Untuk itu diperlukan sebuah UDF (User Defined Function) untuk menyelesaikan tugas ini.

Catatan pelajaran excel kali ini akan membahas bagaimana membuat dan menerapkan fungsi ambilAngka(), dimana fungsi ini berguna untuk men-ektrak porsi angka dari sebuah entri text.







Screenshot berikut memperlihatkan bagaimana fungsi ambilAngka() bisa mengextract porsi angka dari entri data, tidak peduli bagaimana pola susunan karakter serta panjang data entri.

Selanjutnya mari kita simak baik-baik bagaimana menerapkan code VBA untuk membuat fungsi ambilAngka sehingga bisa diterapkan pada spreadsheet seperti gambar di atas.

Contoh kode VBA untuk extract angka dari text.

Berikut contoh kode VBA yang dapat digunakan untuk extrak porsi angka dari data entri.

Function ambilAngka(txt As String) As String
Dim i As Integer, iKarakter As String, Angka As String
For i = 1 To Len(txt)
  iKarakter = Mid(txt, i, 1)
  If IsNumeric(iKarakter) Then
    Angka = Angka & iKarakter
  End If
Next
ambilAngka = Angka
End Function


Supaya code diatas dapat digunakan maka harus diketikan atau dicopy ke modul VBA. Jika pembaca sudah mengenal dasar – dasar VBA sebelumnya, tentunya bukan hal yang sulit bagi anda untuk segera mengcopy kan code di atas ke modul VBA.

Bagi pembaca yang masih baru mengenal VBA tidak perlu khawatir. VBA itu sangat menyenangkan, apalagi jika kita bisa merasakan manfaatnya yang luar biasa dalam meningkatkan efisiensi dan efektifitas kerja menggunakan microsoft Excel.

Baiklah mari kita lanjutkan. Bagaimana masuk ke modul VBA.

• Untuk excel 2007 atau yang lebih baru, pastikan tab developer tersedia dan setting macro security enable. Demikian juga jika anda masih menggunakan excel 2003, pastikan macro security enable.
• Untuk masuk ke module VBA, tekan shortcut ALT = F11 atau melalui ribbon dengan cara klik icon Visual Basic pada tab developer.

• Pada jendela VBA, klik menu Insert → klik Module

• Langkah selanjutnya ketikan atau copy kode VBA di atas pada module seperti diperlihatkan dalam screenshot di bawah ini.

• Setelah code diketik/ di copy ke modul VBA, maka fungsi ambilAngka() sudah tersedia dan siap digunakan.
• Simpan file dengan extension .xlsm (Excel Macro - Enable Workbook) atau dengan extension .xlsb (Excel Binary Workbook) jika anda menggunakan excel 2007, 2010 atau versi yang lebih baru.

Cara menggunakan fungsi ambilAngka()

Gambaran cara menggunakan fungsi ambilAngka() sudah diperlihatkan pada bagian awal catatan ini., silahkan di scroll kembali ke bagian atas untuk melihat screenshot contoh penerapannya di excel.

Cara penulisan rumusnya sangat sederhana. Yaitu:

=ambilangka(entri)

Misalnya kita menuliskan rumus sebagai berikut:

=ambilangka("AB12cfgR44Db")

Maka ouput dari rumus di atas adalah : "1244" yang merupakan porsi angka dari "AB12cfgR44Db"
Karena data entri terletak dalam sel excel, maka rumus ambilAngka dapat dituliskan dengan menggunakan referensi sel:

Misalnya:

=ambilAngka(A1)

Rumus ini berguna untuk mengambil porsi angka dari text data entri yang terletak pada sel A1.

Demikian pembahasan singkat mengenai contoh kode macro / vba yang dapat digunakan untuk mengekstrak porsi angka dari entri text. Semoga bermanfaat.

Salam.

Artikel terkait:

• Konversi Angka Berformat Text Menjadi Angka Nyata
• Membuat Daftar Isi Otomatis
• Memasukan Data ke Beberapa Worksheet Sekaligus

Mengisi Data Pada Beberapa Worksheet Sekaligus

Pada aplikasi excel, kita bisa mengisi data yang sama ke dalam beberapa worksheet sekaligus dengan cara menggabungkan atau grouping worksheet-worksheet tersebut. Grouping worksheet dapat dilakukan secara manual maupun secara otomatis menggunakan kode VBA. Dengan memahami tehnik ini diharapkan dapat membantu kita untuk menghemat waktu ketika harus mengisi data yang sama pada beberapa worksheet.

Grouping worksheet secara manual.

Ikuti langkah-langkah berikut untuk grouping worksheet secara manual, serta untuk mengisi data dan edit format sekaligus pada beberapa worksheet:

• Pada keyboard, tekan tombol Ctrl, kemudian dengan menggunakan mouse, klik tab worksheet yang akan di-group.

• Lakukan isi data pada salah satu sheet yang di-group.
• Silahkan lihat pada worsheet lainnya dalam group.
• Maka kita bisa melihat semua sheet dalam group sudah terisi data yang sama.
• Silahkan lakukan edit format pada salah satu sheet dalam grup dan kemudian lihat hasilnya pada masing-masing worksheet.
• Maka semua sheet dalam grup akan memiliki format yang sama.
• Untuk mengembalikan ke mode ungroup, silahkan klik salah satu tab sheet.

Kelemahan Grouping Worksheet secara manual

• Editing pada sebuah worksheet dalam group akan merubah worksheet lainnya dalam grup, tidak peduli dimana posisi sel yang diisi data atau di-edit. Padahal mungkin kita hanya perlu merubah atau mengisi data yang sama pada range sel tertentu saja.
• Sangat riskan user lupa melakukan ungroup setelah melakukan isi data yang diperlukan, sehingga tidak menyadari apa yang diedit pada sebuah worksheet ternyata merubah worksheet lainnya. Padahal belum tentu diperlukan.

Grouping worksheet secara otomatis.

Sebagai solusi untuk meniadakan resiko yang tidak diinginkan atas grouping worksheet secara manual, maka disarankan untuk grouping worksheet secara otomatis. Dengan cara ini kita bisa menetapkan grouping worksheet ketika hanya perlu mengedit atau mengisi data pada range tertentu saja. Selain itu kita tidak perlu khawatir kelupaan melakukan ungroup,  karena excel akan mengerjakannya secara otomatis.

Langkah-langkah grouping worksheet secara otomatis.

Anggaplah kita ingin memasukan data yang sama ke dalam sheet1, sheet2 dan sheet 3 pada range B3:E10

• Pastikan setting macro security sudah enable
• Klik kanan pada salah satu worksheet yang akan di-grup, misalnya sheet1, kemudian klik View Code.

• Maka kita akan di bawa ke jendela VBA, modul object Sheet1. Selanjutnya copy code berikut ke dala  modul VBA.
  
  
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Range("B3:E10"), Target) Is Nothing Then
  Me.Select
Else
  Sheets(Array("sheet1", "sheet2", "sheet3")).Select
End If
End Sub


  
  Untuk lebih jelasnya bisa dilihat pada screenshot di bawah ini.

• Lakukan hal yang sama pada sheet lainnya yang akan di grup (sheet2 dan sheet3) sehingga semua modul object sheet1, sheet2 dan sheet 3 sudah memiliki kode VBA seperti contoh di atas.
• Sekarang saatnya untuk menguji hasilnya:
• Silahkan seleksi salah satu sel dalam range B3:E10 pada salah satu sheet1, sheet2 atau sheet3.  Kemudian seleksi sembarang sel lainnya di luar range B3:E10 dan perhatikan perbedaan reaksi excel.
• Ketika kita menyeleksi salah satu sel pada range B3:E10 maka otomatis sheet1, sheet2 dan sheet3 akan di-group. Sebaliknya ketika kita menyeleksi sel di luar range B3:E10 maka sheet1, sheet2 dan sheet3 akan di-ungroup.
• Lakukan isi data atau edit format pada range B3:E10 dan bandingkan hasilnya dengan isi data / edit format pada range diluar B3:E10.
• Maka data yang sama atau format yang sama hanya akan berlaku jika kita mengedit sel dalam range B3:B10 saja.
• Hal ini tentu saja sangat berguna untuk memastikan isi data yang sama hanya pada range tertentu saja.

Untuk menentukan sheet dan range spesifik mana yang diinginkan supaya terisi data yang sama, maka kita bisa memodifikasi code sesuai contoh di atas.

Misalnya:

Code macro berikut dapat digunakan untuk grouping secara otomatis sheet1, sheet2, sheet3 dan sheet4 jika posisi aktive cell terletak pada range A1:B10


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Range("A1:B10"), Target) Is Nothing Then
  Me.Select
Else
  Sheets(Array("sheet1", "sheet2", "sheet3","sheet4")).Select
End If
End Sub


Silahkan dicoba dengan cara mengcopy kode tersebut pada ke modul VBA object sheet1, sheet2, sheet3 dan sheet4 sesuai langkah-langkah yang sudah dijelaskan sebelumnya. Kemudian perhatikan hasilnya ketika kita mengedit data pada sel dalam range A1:B10 dan bandingkan dengan hasil edit data pada range lainnya.

Demikian pembahasan singkat mengenai tips mengisi data sekaligus pada beberapa worksheet dengan cara grouping worksheet, baik secara manual maupun otomatis. Semoga bermanfaat.

Dapatkan tips-tips bermanfaat lainnya dari je-xcel:

• Membuat Daftar Isi Otomatis
• Macro Untuk Melihat dan Menyembunyikan Comment
• Macro Untuk Find & Replance Comment.