Protect dan Unprotect Semua Worksheet Sekaligus

Halo sobat, kali ini mimin akan berbagi contoh code macro vba untuk Protect dan Unprotect Worksheet Sekaligus Pada Microsoft Excel. Proteksi worksheet berguna untuk melindungi sebuah template laporan dari kesalahan penginputan atau editing. 

Secara konvensional, prosedur protect  worksheet di excel dapat dilakukan melalui langkah - langkah berikut:

Tab Review ⇨ Pada grup Protect, klik command "Protect Sheet"

Demikian juga untuk melakukan unprotect prosesnya serupa, yaitu:

Tab Review ⇨ Pada grup Protect, klik command "Unprotect Sheet"

Ketika hanya ada 1 atau 2 sheet saja maka tidak terlalu sulit melakukan protect / unprotet sheet secara konvensional.  Jika ada banyak sheet juga sebenarnya tidak sulit juga. Hanya saja kamu akan merasa bosan mengulang - ulang proses yang sama.

Oleh karenanya macro datang sebagai solusi untuk protect dan unprotect semua worksheet sekaligus dan cepat.

Baiklah, mari kita langsung menuju contoh code macro untuk protect atau unprotect  semua worksheet dalam sekali proses.

1. Protect Semua Worksheet Sekaligus

Nah prosedur yang pertama ini dapat kamu gunakan untuk memproteksi semua sheet dalam workook aktif. silahkan di copy contoh code vba berikut:

Sub ProtectSemuaWs() Dim Wb As Workbook Dim Ws As Worksheet Dim Sandi As String Sandi = "sandiku123" Set Wb = ActiveWorkbook For Each Ws In Wb.Sheets Ws.Protect Password:=Sandi Next End Sub

2. Unprotect Semua Worksheet Sekaligus

Sedangkah prosedur yang kedua ini digunakan untuk tujuan sebaliknya dari prosedur nomor 1. Ini diperlukan jika kamu akan melakukan editing pada template excel yang kamu buat.

Sub UnprotectSemuaWs() Dim Wb As Workbook Dim Ws As Worksheet Dim Sandi As String Sandi = "sandiku123" Set Wb = ActiveWorkbook For Each Ws In Wb.Sheets Ws.Unprotect Password:=Sandi Next End Sub

 

Cara menggunakan code:

Baik contoh code pertama maupun kedua, digunakan dengan langkah-langkah yang sama. 

1. Menyiapkan code macro

• Copy code di atas
• masuk ke microsoft excel dimana kamu akan menyimpan code nya
• masuk ke VB Editor dengan menekan shortcut Alt + 11, atau melalui Tab Developer ⇨ pada grup Code, klik Visual Basic.
• Di dalam VB Editor, klik menu Insert ⇨ Module, atau jika kamu sudah punya sebuah module yang dipersiapkan sebelumnya untuk menyimpan semua macro yang kamu punya, maka cukuplah masuk ke dalam module dimaksud.
• Paste code di dalam module

2. Menjalankan macro

• Salah satu cara menjalankan macro, kamu bisa melakukannya via Tab Developer ⇨ Macros ⇨ Pilih macro yang sesuai ⇨ Run
• Selain itu kamu juga bisa membuat short cut untuk yang bisa digunakan untuk mengeksekusi macro secara cepat. 

Cara membuat shortcut macro:

Pada jendela excel, masuk ke Tab Developer ⇨ Macros ⇨ Pilih macro yang akan dibuat shortcut-nya ⇨ klik Options ⇨ ketik huruf shortcut yang kamu mau. 

Dengan adanya shortcut ini maka ketika kamu ingin menjalankan sebuah macro, maka kamu tinggal tekan key shortcut yang sudah kamu buat sebelumnya.

***

Itulah pembahasan singkat mengenai contoh code macro VBA excel untuk protect dan unprotect worksheet. Semoga bermanfaat.

Contoh lainnya dalam link berikut ini mungkin barmanfaat, silahkan pindah TKP saja:

• Contoh Macro Untuk Hide dan Unhide Sheet
• Contoh Macro Untuk Hide untuk Delete Sheet

Dapatkan lebih banyak lagi contoh - contoh code macro excel dalam blog ini. Silahkan telusuri via Daftar Isi

Jika sobat ada saran atau koreksi. Mimin sangat berterimakasih kalau kamu bisa menyampaikan feed back nya dalam kolom komentar.

Terimakasih.

Salam...😎😎

Mengurutkan Sheet

Urutan sheet tidak beraturan? Capek mengurutkannya secara manual? jangan khawatir !! Kamu bisa mengurutkan / sortir sheet secara cepat menggunakan makro. Ga perlu mikir panjang, tinggal copas  saja kode - kode ini ke workbook berisi kumpulan makro milik kamu.

1. Mengurutkan Sheet Sesuai Alfa Numerik (Alfabet dan Angka)

Jika urutan sheet tidak karuan, apalagi jumlah sheetnya bejibun alias banyak, yang bila diukur panjang tab sheetnya bisa bermeter-meter, maka mengurutkan sheet secara manual pasti sangat melelahkan. Untungnya ada code VBA yang sangat membantu untuk mengatasi masalah ini. 

Menyusun sheet sesuai urutan angka dari terkecil ke tertinggi atau huruf dari "A" ke "Z" akan sangat memudahkan dalam membaca alur worksheet. Maka sortir worksheet  secara alfanumerik adalah solusinya. Tinggal copy saja code di bawah ini gaes... dan paste ke module standar dalam workbook kamu.

Sub UrutkanSheetSesuaiAbjadDanAngka() Dim jmlSheet As Integer, i As Integer, j As Integer Application.ScreenUpdating = False jmlSheet = Sheets.Count For i = 1 To jmlSheet - 1 For j = i + 1 To jmlSheet If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

2. Mengurutkan Sheet Sesuai Nama Bulan Ringkas Bahasa Inggris

Jika sebuah workbook laporan bulanan terdiri dari sekumpulan sheet dengan nama bulan, maka kita bisa mengurutkan sheet sesuai nama bulan. Nama bulan yang dikenali VBA secara default adalah bahasa inggris, sehingga kamu harus menamai sheet dengan nama bulan dalam bahasa Inggris. Untuk penulisan nama bulan ada 2 type yaitu tipe ringkas dan type panjang. Gunakan kode makro berikut untuk mengurutkan nama bulan yang ditulis dalam bahasa inggris model ringkas.

Sub UrutkanSheetMonthRingkas() Dim Wb As Workbook, i As Integer Application.ScreenUpdating = False Set Wb = ActiveWorkbook On Error Resume Next For i = 1 To 12 Debug.Print i Worksheets(MonthName(i, True)).Move after:=Sheets(Sheets.Count) Next Application.ScreenUpdating = True End Sub

Keterangan:  

Nama bulan type ringkas dalam bahasa Inggris = Jan, Feb, Mar, Apr, May, Jun, ,Aug, Sep, Oct, Nov, Dec.

3. Mengurutkan Sheet Sesuai Nama Bulan Full Bahasa Inggris

Dengan sedikit modifikasi dari contoh kode nomor 2, maka contoh kode nomor 3 berikut ini bisa digunakan untuk mengurukan sheet nama bulan dalam bahasa inggris type nama bulan panjang.

Sub UrutkanSheetMonthFull() Dim Wb As Workbook, i As Integer Application.ScreenUpdating = False Set Wb = ActiveWorkbook On Error Resume Next For i = 1 To 12 Debug.Print i Worksheets(MonthName(i, False)).Move after:=Sheets(Sheets.Count) Next Application.ScreenUpdating = True End Sub

Keterangan:  

Nama bulan type panjang dalam bahasa Inggris  = January, February, March, April, May, June, July, August, September, October, November, December.

4. Mengurutkan Sheet Sesuai Nama Bulan Ringkas Bahasa Indonesia

Meskipung secara default, VBA excel mengenali nama bulan dalam bahasa Inggris. Namun dengan sedikit modifikasi kita masih bisa mengurutkan sheet nama bulan dalam berbagai bahasa. Tinggal copas saja code berikut untuk mengurutkan sheet bulan type ringkas dalam bahasa Indonesia.

Sub UrutkanSheetBulanRingkas() Dim i As Integer, namaBulan As String Application.ScreenUpdating = False On Error Resume Next For i = 1 To 12 namaBulan = WorksheetFunction.Text(DateSerial(1, i, 1), "[$-421]mmm") Worksheets(namaBulan).Move after:=Sheets(Sheets.Count) Next Application.ScreenUpdating = True End Sub

Keterangan: Nama bulan type ringkas dalam bahasa Indonesia = Jan, Feb, Mar, Apr, Mei, Jun, Jul, Agu, Sep, Okt, Nov, Des

5. Mengurutkan Sheet Sesuai nama bulan Full Bahasa Indonesia

Jika kamu menuliskan nama bulan type panjang dalam bahasa Indonesia maka gunakan code berikut untuk mengurutkan sheet bulannya.

Sub UrutkanSheetBulanFull() Dim i As Integer, namaBulan As String Application.ScreenUpdating = False On Error Resume Next For i = 1 To 12 namaBulan = WorksheetFunction.Text(DateSerial(1, i, 1), "[$-421]mmmm") Worksheets(namaBulan).Move after:=Sheets(Sheets.Count) Next Application.ScreenUpdating = True End Sub

Keterangan: Nama bulan type panjang dalam bahasa Indonesia = Januari, Februari, Maret, April, Mei, Juni, Juli, Agustus, September, Oktober, November, Desember.

...

Itulah beberapa contoh kode makro VBA yang sering digunakan untuk mengurutkan / sortir sheet.

Ada koreksi / saran ? jangan ragu untuk tinggalkan komentar.

Mungkin kamu tertarik untuk mengetahui contoh-contoh lainnya, silahkan klik tautan di bawah ini:

• Contoh Macro Untuk Menghapus / Delete Sheet
• Contoh Macro Untuk Hide/Unhide Sheet
• Contoh Macro Untuk Menambah Sheet

Salam..😎😎😎

Menghapus Sheet

Menghapus / delete sheet merupakan hal yang lumrah bagi pengguna excel dan sangat mudah dilakukan dengan cara manual. Delete sheet menggunakan makro / VBA tentunya tidak merugikan untuk dicoba. Jika hapus menghapus sheet ini merupakan kegiatan rutin, maka VBA merupakan pilihan yang tepat untuk diterapkan.

1.  Menghapus Sheet Yang Sedang Aktif

Ini merupakan prosedur delete sheet yang paling sederhana. Seperti halnya cara manual delete sheet yang sering dilakuin kebanyakan user.

Sub HapusSheetAktif() Application.DisplayAlerts = False ActiveSheet.Delete Application.DisplayAlerts = True End Sub

2.  Menghapus Semua Sheet Kecuali Sheet Yang Sedang Aktif

Jika kamu hanya memerlukan satu sheet saja, sedangkan sheet lainnya hanya berisi kumpulan data tidak berguna maka prosedur nomor 2 adalah pilihan yang tepat.

Sub HapusSemuaSheetKecualiSheetAktif() Dim Wb As Workbook, Sht Set Wb = ActiveWorkbook Application.DisplayAlerts = False For Each Sht In Wb.Sheets If Not Sht.Name = ActiveSheet.Name Then Sht.Delete End If Next Application.DisplayAlerts = True End Sub

3.  Menghapus Sebuah Sheet Tertentu

Kamu melihat bahwa ada sebuah sheet tidak berguna dan harus dihapus, maka gunakan prosedur ini. Mirip prosedur nomor 1, hanya saja tidak perlu mengaktifkan sheetnya terlebih dahulu.

Sub HapusSheetDgnNamaTertentu() Dim Wb As Workbook, Sht, namaShtHapus As String Set Wb = ActiveWorkbook namaShtHapus = "List 1" ' bisa diganti sesuai keperluan Application.DisplayAlerts = False For Each Sht In Wb.Sheets If Sht.Name = namaShtHapus Then Sht.Delete End If Next Application.DisplayAlerts = True End Sub

4.  Menghapus Beberapa Sheet Yang Memiliki Nama Depan Tertentu

Kamu bisa menandai sheet-sheet yang tidak diperlukan dengan ciri-ciri nama depan tertertentu. Ajib.. kamu bisa menghapusnya sekaligus.

Sub HapusSheetDgnNamaDepanTertentu() Dim Wb As Workbook, Sht Dim namaDepanShtHapus As String Set Wb = ActiveWorkbook namaDepanShtHapus = "List" ' bisa diganti sesuai keperluan Application.DisplayAlerts = False For Each Sht In Wb.Sheets If Left(Sht.Name, Len(namaDepanShtHapus)) = namaDepanShtHapus Then Sht.Delete End If Next Application.DisplayAlerts = True End Sub

5. Menghapus Satu Atau Lebih Sheet Paling Depan

Oh... Ternyata beberapa sheet paling depan tidak berguna. Tinggal hapus saja pake cara ini.

Sub HapusBeberapaSheetPalingDepan() Dim Wb As Workbook, Sht Dim i As Integer, jmlShtHapus As Integer, jmlShtTotal As Integer Set Wb = ActiveWorkbook jmlShtTotal = Wb.Sheets.Count On Error GoTo batal jmlShtHapus = InputBox("Input jumlah sheet yang akan dihapus") If jmlShtHapus > jmlShtTotal Then MsgBox "Inputan jumlah sheet yang akan dihapus tidak boleh " & Chr(10) _ & "melebihi jumlah sheet yang ada dalam workbook", vbOKOnly, "Ups ada masalah !!!" Exit Sub End If Application.DisplayAlerts = False For i = 1 To jmlShtHapus Wb.Sheets(i).Delete Next Application.DisplayAlerts = True Exit Sub batal: Exit Sub End Sub

6.  Menghapus Satu Atau Lebih Sheet Paling Belakang

Eh... yang ini malah beberapa sheet paling belakang yang hanya menambah beban memory, tapi tidak ada gunanya sama sekali. Lebih baik hapus saja deh dengan cara ini.

Sub HapusBeberapaSheetPalingBelakang() Dim Wb As Workbook, Sht Dim i As Integer, jmlShtHapus As Integer, jmlShtTotal As Integer Set Wb = ActiveWorkbook jmlShtTotal = Wb.Sheets.Count On Error GoTo batal jmlShtHapus = InputBox("Input jumlah sheet yang akan dihapus") If jmlShtHapus > jmlShtTotal Then MsgBox "Inputan jumlah sheet yang akan dihapus tidak boleh " & Chr(10) _ & "melebihi jumlah sheet yang ada dalam workbook", vbOKOnly, "Ups ada masalah !!!" Exit Sub End If Application.DisplayAlerts = False For i = 1 To jmlShtHapus Wb.Sheets(jmlShtTotal - i + 1).Delete Next Application.DisplayAlerts = True Exit Sub batal: Exit Sub End Sub

7.  Menghapus Semua Sheet Grafik

File excel ku ternyata ada banyak sheet grafik yang sudah kehilangan referensi sumber datanya. Daripada menuh-menuhin  memoy, lebih afdhol delete saja ah..

Sub hapusSemuaSheetGrafik() Dim Wb As Workbook Application.DisplayAlerts = False Set Wb = ThisWorkbook If Wb.Charts.Count > 0 Then Wb.Charts.Delete Else MsgBox "Tidak ada sheet grafik" End If Application.DisplayAlerts = True End Sub

Jika ada saran / koreksi terkait code VBA untuk menghapus sheet, jangan ragu untuk tinggalkan pesan / komentar.

Salam..

Code VBA Terkait:

Cara Menambah Worksheet Baru

Menyembunyikan dan Memunculkan Sheet

Menyembunyikan / Memunculkan Sheet

Menyembunyikan sheet diperlukan jika kamu menginginkan beberapa data tidak terlihat dan dirubah oleh user / kolega anda. Contoh - contoh kode VBA berikut dapat digunakan untuk menyembunyikan sheet maupun sebaliknya yaitu untuk memunculkan sheet yang tersembunyi.

1. Menyembunyikan sheet dengan nama depan tertentu

Ketika kamu membuat sebuah template laporan dan menyusun data dalam serangkaian sheet yang didesain dengan nama depan tertentu, misalnya: List 1, List 2, List 3 dan seterusnya. Kemudian kamu ingin menyembunyikan sheet list tersebut dengan cepat, Maka  kamu dapat membuat makro dengan kode berikut:

Sub HideWsNamaDepanTertentu() Dim namaDepanWs As String Dim Wb As Workbook Dim Ws As Worksheet Set Wb = ActiveWorkbook namaDepanWs = "List" ' "List" bisa diganti sesuai kebutuhan For Each Ws In Wb.Sheets If Left(Ws.Name, Len(namaDepanWs)) = namaDepanWs Then Ws.Visible = xlSheetHidden End If Next End Sub

2. Menyembunyikan sheet supaya tidak sembarang user bisa memunculkan kembali sheet tersebut.

Alternatif lainnya, kamu bisa menyembunyikan sheet dengan mode sangat tersembunyi. Dengan mode ini maka tidak sebarang user bisa melakukan unhide sheet tersebut (tentunya kecuali user yang sudah tau triknya...). Untuk tujuan ini anda dapat menggunakan konstan xlSheetVeryHidden sebagai nilai property sheet Visible.

Sub SuperHideWsNamaDepanTertentu() Dim namaDepanWs As String Dim Wb As Workbook Dim Ws As Worksheet Set Wb = ActiveWorkbook namaDepanWs = "List" ' "List" bisa diganti sesuai kebutuhan For Each Ws In Wb.Sheets If Left(Ws.Name, Len(namaDepanWs)) = namaDepanWs Then Ws.Visible = xlSheetVeryHidden End If Next End Sub

3. Memunculkan Sheet Tersembunyi Yang Memiliki Nama Dengan Kata Depan Tertentu

Prosedur nomor 3 ini merupakan kebalikan dari prosedur nomor 1.  Kita bisa memunculkan kembali satu atau lebih sheet tersembunyi yang memiliki nama depan tertentu. Cotoh kasus : dengan nama depan "List". 

Sub UnhideWsNamaDepanTertentu() Dim namaDepanWs As String Dim Wb As Workbook Dim Ws As Worksheet Set Wb = ActiveWorkbook namaDepanWs = "List" ' "List" bisa diganti sesuai kebutuhan For Each Ws In Wb.Sheets If Left(Ws.Name, Len(namaDepanWs)) = namaDepanWs Then If Ws.Visible = xlSheetHidden Then Ws.Visible = xlSheetVisible End If End If Next End Sub

4. Memunculkan Semua Sheet Tersembunyi dengan Mode Standar.

Prosedur berikut dapat digunakan untuk memunculkan semua sheet tersembunyi dengan mode standar tanpa memperhatikan nama yang melekat pada sheet tersembunyi, namun tetap mempertahankan semua sheet yang super tersembunyi (very hidden) untuk tetap berdiam diri di persembunyiannya.

Sub UnhideSemuaWs() Dim Wb As Workbook Dim Ws As Worksheet Set Wb = ActiveWorkbook For Each Ws In Wb.Sheets If Ws.Visible = xlSheetHidden Then Ws.Visible = xlSheetVisible End If Next End Sub

5. Memunculkan Sheet Tersembunyi Tanpa Kecuali

Jika kamu ingin mengeluarkan semua sheet dari persembunyiannya tanpa kecuali, maka prosedur nomor 5 menjadi pilihan yang paling tepat. Dengan prosedur ini semua sheet akan dipaksa keluar dari persembunyiannya dan terpampang nyata, termasuk very hidden sheet. 

Sub UnhideSemuaWsTanpaKecuali() Dim Wb As Workbook Dim Ws As Worksheet Set Wb = ActiveWorkbook For Each Ws In Wb.Sheets Ws.Visible = xlSheetVisible Next End Sub

Demikian beberapa contoh code makro / VBA yang bisa teman-teman gunakan untuk menyembunyikan sheet dan juga sebaliknya.

Jika ada saran / masukan silakan tinggalkan pesan.

Salam..

Contoh code terkait:

Code VBA Untuk Menghapus Sheet

Code VBA Untuk Menambah Sheet

Menambah Worksheet Baru

Contoh Kode VBA berikut dapat digunakan untuk menambah beberapa sheet baru dengan berbagai model pilihan sesuai kebutuhan.

Menambah beberapa worksheet di posisi depan.

'Menambah beberapa worksheet dengan posisi paling depan Sub TambahWsPosisiDiDepan() Dim i As Integer, jmlTambahWs As Integer, wsbaru As Worksheet jmlTambahWs = 5 'jumlah sheet yang du dapat disesuaikan dengan kebutuhan With ActiveWorkbook.Worksheets For i = jmlTambahWs To 1 Step -1 Set wsbaru = .Add(before:=.Parent.Worksheets(1)) wsbaru.Name = "Sheet Baru " & i Next End With End Sub

Menambah beberapa worksheet dengan posisi setelah sheet terakhir.

'Menambah beberapa worksheet dengan posisi setelah sheet terakhir Sub TambahWsSetelahWsAkhir() Dim i As Integer, jmlTambahWs As Integer, wsbaru As Worksheet jmlTambahWs = 5 'jumlah sheet yang du dapat disesuaikan dengan kebutuhan With ActiveWorkbook.Worksheets For i = 1 To jmlTambahWs Set wsbaru = .Add(after:=.Parent.Worksheets(.Count)) wsbaru.Name = "Sheet Baru " & i Next End With End Sub

Menambah beberapa worksheet setelah sheet active.

'Menambah beberapa worksheet setelah sheet active Sub TambahWsSetelahWsAktif() Dim i As Integer, jmlTambahWs As Integer, wsAwal As Worksheet Dim wsbaru As Worksheet jmlTambahWs = 5 Set wsAwal = ActiveSheet With ActiveWorkbook.Worksheets For i = 1 To 10 Set wsbaru = .Add(after:=wsAwal) wsbaru.Name = "Sheet Baru " & i Set wsAwal = wsbaru Next End With End Sub

Menambah beberapa worksheet setelah sheet dengan nama tertentu.

'Menambah beberapa worksheet setelah sheet dengan nama tertentu Sub TambahWsSetelahWsTertentu() Dim i As Integer, jmlTambahWs As Integer, wsAwal As Worksheet, wsbaru As Worksheet Dim namaWsAwal As String namaWsAwal = "Ws Awal" ' nama "Ws Awal" bisa disesuaikan kebutuhan Set wsAwal = ActiveWorkbook.Worksheets(namaWsAwal) jmlTambahWs = 5 With ActiveWorkbook.Worksheets For i = 1 To 10 Set wsbaru = .Add(after:=wsAwal) wsbaru.Name = "Sheet Baru " & i Set wsAwal = wsbaru Next End With End Sub

Catatan: Contoh kode di atas akan terjadi error pada saat dieksekusi apabila tidak ada nama sheet "Ws Awal". Supaya tidak terjadi error maka harus dipastikan variable namaWsAwal  merujuk ke nama sheet yang valid.

Ada koreksi / saran / request contoh VBA lainnya ? Silakan tinggalkan pesan di komentar.

Salam..