Makro Peristiwa Untuk Mengubah Header Excel - Tips Excel

Daftar Isi

Donna dari Missouri bertanya:

Apakah Anda tahu bagaimana saya bisa mendapatkan jalur di mana dokumen diajukan yang ditunjukkan di header atau footer - atau di mana saja di dokumen. Saya bisa mendapatkan nama file dengan & f tetapi saya tidak dapat menemukan cara melakukan jalurnya.

Pertama-tama, saya memahami bahwa Microsoft telah menambahkan fungsi ini ke Excel XP dan saya menawarkan pujian kepada mereka karena ini adalah masalah yang sering ditanyakan. Setiap pembaca yang telah meningkatkan ke Office XP tidak akan memerlukan teknik dalam tip minggu ini untuk memecahkan masalah ini, tetapi mereka masih akan berguna untuk memecahkan masalah lainnya.

Solusi untuk tip ini adalah jenis makro khusus yang disebut makro penanganan kejadian. Kami akan mengambil kendali Excel secara singkat setiap kali akan mencetak buku kerja kami dan menambahkan jalur saat ini ke header.

Banyak pengguna Excel mencoba-coba merekam makro sederhana. Makro disimpan dalam modul yang disebut Module1 atau Module2 dan menjadi bagian dari proyek Anda. Hari ini, saya akan membahas makro event handler. Makro ini berada di modul kode khusus yang terkait dengan setiap lembar kerja atau buku kerja.

Tip sebelumnya seperti Enter Excel Time Without the Colon tip telah berurusan dengan acara Worksheet_Change. Tip hari ini mengharuskan kita menambahkan beberapa kode ke acara BeforePrint Workbook.

Kode yang ditambahkan ke acara akan dijalankan setiap kali acara itu "dipicu". Dalam kasus ini, kapan pun buku kerja Excel dicetak, sebelum pencetakan dimulai, Excel mengalihkan kontrol ke kode VBA dan memungkinkan apa pun yang Anda tentukan dalam kode VBA terjadi secara otomatis sebelum pencetakan.

Saya akan menganggap Anda baru mengenal makro event handler. Saya akan menjelaskan secara tepat bagaimana menuju ke tempat yang tepat untuk memasukkan makro ini.

Saya memiliki buku kerja yang disebut 'Tip055 Sample.xls'. Dengan buku kerja dimuat di Excel, saya akan menekan alt = "" + F11 untuk memulai editor visual basic. Tampilan default editor adalah seperti yang ditunjukkan di kanan. Di sebelah kiri, Anda biasanya melihat panel Proyek yang ditumpuk di atas panel Properti. Sebagian besar sisi kanan layar menyertakan Panel Kode. Jika Anda tidak memiliki makro di buku kerja Anda, panel kode Anda akan berwarna abu-abu seperti yang diperlihatkan di sebelah kanan.

Saya telah menambahkan kata-kata skrip miring biru ke gambar untuk mengidentifikasi tiga panel - Anda tidak akan melihatnya dalam contoh Anda.

Penting agar Anda dapat melihat Project Pane di editor VB. Jika tampilan editor VB Anda tidak menyertakan panel proyek, tekan Ctrl + R untuk melihat Panel Proyek. Atau, klik ikon bilah alat yang ditunjukkan di bawah ini:

Panel proyek akan memperlihatkan proyek untuk setiap buku kerja Excel yang terbuka dan setiap Add-In yang diinstal. Klik tanda tambah abu-abu di samping nama buku kerja Anda untuk memperluas proyek untuk buku kerja Anda. Kemudian, klik tanda tambah abu-abu di samping folder Objek Microsoft Excel untuk memperluas folder objek. Anda sekarang akan melihat satu entri untuk setiap lembar kerja dan satu entri yang disebut Buku Kerja Ini.

Klik kanan entri untuk ThisWorkbook dan pilih Lihat Kode dari menu pop-up.

Anda sekarang mungkin akan memiliki panel kode putih kosong besar yang berada di sisi kanan layar. Ada dua dropdown di bagian atas panel kode yang akan menampilkan (Umum) dan (Deklarasi).

  • Dari dropdown kiri, pilih Workbook.
  • Turun bawah kanan sekarang diisi dengan semua acara terprogram yang terkait dengan buku kerja. Ada acara di sini yang akan menjalankan kode setiap kali buku kerja dibuka, diaktifkan, dinonaktifkan, dll. Hari ini, kami ingin menulis kode di acara BeforePrint, jadi pilih BeforePrint dari dropdown kanan.

Perhatikan bahwa setiap kali Anda memilih sesuatu dari dropdown kanan, editor VBA menulis baris awal dan akhir kode ke dalam modul kode untuk Anda. Pertama kali Anda mengubah dropdown kiri ke Workbook, Anda mungkin menerima permulaan subrutin Workbook_Open secara default. Jika Anda tidak akan menulis prosedur Workbook_Open, Anda harus mempertimbangkan untuk menghapus prosedur kosong ini.

Sekarang, lanjutkan ke menulis kode VBA. Ada beberapa variabel berguna yang dapat Anda gunakan.

  • ActiveWorkbook.Path akan mengembalikan jalur buku kerja. Ini mungkin terlihat seperti "C: My Documents MrExcel".
  • ActiveWorkbook.FullName akan mengembalikan jalur dan nama file dari buku kerja. Ini mungkin terlihat seperti "C: My Documents MrExcel Tip055 Sample.xls".

Anda dapat menetapkan variabel ini menjadi salah satu dari 6 posisi berikut:

Worksheets("Sheet1").PageSetup.LeftHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.CenterHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.RightHeader = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.LeftFooter = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.CenterFooter = ActiveWorkbook.FullName Worksheets("Sheet1").PageSetup.RightFooter = ActiveWorkbook.FullName

Berikut tiga kemungkinan makro sampel.

Makro ini akan memiliki jalur dan nama file yang ditambahkan sebagai footer kanan lembar kerja aktif:

Private Sub Workbook_BeforePrint(Cancel As Boolean) ActiveSheet.PageSetup.RightFooter = ActiveWorkbook.FullName End Sub

Makro ini akan memiliki jalur yang ditambahkan sebagai header kiri Sheet1 dan sebagai footer tengah Sheet2:

Private Sub Workbook_BeforePrint(Cancel As Boolean) Worksheets("Sheet1").PageSetup.LeftHeader = ActiveWorkbook.Path Worksheets("Sheet2").PageSetup.CenterFooter = ActiveWorkbook.Path End Sub

Jika Anda cenderung menggunakan opsi "Keseluruhan Buku Kerja" saat mencetak, versi ini akan menambahkan nama lengkap sebagai footer tengah ke semua lembar:

Private Sub Workbook_BeforePrint(Cancel As Boolean) For Each Sh In ActiveWorkbook.Worksheets Sh.PageSetup.CenterFooter = ActiveWorkbook.FullName Next Sh End Sub

Ikuti salah satu contoh berikut atau buat salah satu dari Anda sendiri. Setelah selesai, tutup editor VBA dengan File> Tutup dan Kembali ke Microsoft Excel.

Setiap kali Anda mencetak lembar kerja, kode akan berjalan dan memasukkan jalur saat ini ke dalam header atau footer yang sesuai yang Anda tunjukkan dalam kode VBA.

Beberapa catatan dan perhatian:

  • Pengguna Excel pemula akan memiliki sedikit ide bahwa kode ini ada di buku kerja. Saat mereka membuka buku kerja, mereka mungkin mendapatkan peringatan keamanan bahwa file tersebut berisi makro, tetapi tidak akan ada peringatan saat kode VBA merusak apa pun yang mereka miliki sebagai footer tengah dan meletakkan nama jalur di sana. Ini bisa menyebabkan mulas. Bayangkan 5 tahun dari sekarang seseorang menggunakan buku kerja Anda dan manajer baru ingin nama file dipindahkan dari footer tengah ke footer kanan. Orang ini mungkin tahu untuk mengubah pengaturan secara manual di File> PageSetup, tetapi jika mereka tidak mengetahui kode yang ada di sana, itu akan membuat mereka gila karena kode terus-menerus mengubah footer mereka kembali.

    It is really unlikely that they will be able to find your code module, but just in case they do, you might want to leave a comment in the code module that directs them back to this website for an explanation.

    You also might want to add a comment to cell A1 reminding yourself that there is an event handler set up to change the print headers.

  • There is a setting within VBA which prevents events from running. If your macro suddenly stops working, t is common to find that something within VBA has turned this setting to prevent the events from running. The common scenario is that a programmer starts a macro with:

    Application.EnableEvents = False '… macro here… Application.EnableEvents = True

    Saat kesalahan terjadi di makro, acara tidak pernah diaktifkan kembali. Jika ini terjadi, ada sedikit peringatan. Jika Anda curiga bahwa penangan acara Anda tidak sedang dijalankan, buka editor visual basic. Tekan Ctrl + g untuk membuka panel langsung. Di panel langsung, ketik:

    Print Application.EnableEvents

    dan tekan enter. Jika Anda menemukan bahwa ini disetel ke False, ketikkan baris berikut di panel langsung:

    Application.EnableEvents = True

    dan tekan enter.

Terima kasih kepada Donna untuk pertanyaan yang bagus. Dalam proses menjelaskan jawabannya, ini adalah kesempatan besar untuk memperluas konsep Penangan Acara di VBA.

Artikel yang menarik...