Masukkan Masa Tanpa Kolon - Petua Excel

Soalan Excel minggu ini datang dari John yang ditempatkan di Okinawa.

Saya sedang membina spreadsheet Excel untuk menggambarkan keberangkatan dan ketibaan. Pada asasnya akan ada tiga sel: Waktu Bertolak Sebenar, Perkiraan Waktu Masuk, dan Anggaran Waktu Ketibaan. Saya ingin orang itu hanya dapat memasukkan (misalnya) 2345 dan meminta sel memformat paparan secara automatik untuk menunjukkan 23:45. Apa yang saya dapat ialah 0:00, tanpa mengira formula atau format. Dan, pengiraan tidak akan memaparkan apa-apa kecuali 0:00 jika pengguna gagal menukar kunci dan titik dua. Saya tahu nampaknya mudah untuk melakukannya, bagaimanapun, setiap detik disimpan, terutamanya ketika memasukkan data serupa berulang kali di Excel.

Untuk membuat kerja ini, anda perlu menggunakan pengendali acara. Pengendali acara baru di Excel 97 dan dibincangkan dalam Jalankan Makro Setiap Kali Nilai Sel berubah di Excel. Namun, kembali pada petua itu, pengendali acara menggunakan format yang berbeza pada sel tertentu. Aplikasi ini sedikit berbeza, jadi mari kita kunjungi semula pengendali acara.

Pengendali peristiwa adalah sebilangan kecil kod makro yang dilaksanakan setiap kali peristiwa tertentu berlaku. Dalam kes ini, kami mahu makro dijalankan setiap kali anda menukar sel. Untuk menyediakan pengendali acara, ikuti langkah berikut:

  • Pengendali acara dikaitkan dengan hanya satu lembaran kerja. Mulakan dari lembaran kerja itu dan tekan alt-F11 untuk membuka editor VB.
  • Di tetingkap kiri atas (Projek - Projek VBA) klik dua kali nama lembaran kerja anda.
  • Di panel kanan, klik kiri bawah dan ubah umum ke Lembaran Kerja.
  • Di drop down kanan, pilih Change.

Ini akan menyebabkan Excel memasukkan cengkerang makro berikut untuk anda:

Private Sub Worksheet_Change(ByVal Target As Range) UserInput = Target.Value If UserInput> 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End Sub

Setiap kali sel diubah, sel yang diubah diteruskan ke program ini dalam pemboleh ubah yang disebut "Target". Apabila seseorang memasukkan titik dengan titik dua dalam lembaran kerja, ia akan dinilai menjadi bilangan yang kurang dari satu. Blok If memastikan hanya menukar sel jika lebih besar daripada satu. Saya menggunakan fungsi kiri () dan kanan () untuk memecah input pengguna menjadi beberapa jam dan minit dan memasukkan titik dua di antara.

Setiap kali pengguna memasukkan "2345", program akan menukar entri ini menjadi 23:45.

Kemungkinan Peningkatan

Sekiranya anda ingin mengehadkan program hanya berfungsi pada lajur A & B, anda boleh memeriksa nilai Target.Column dan hanya menjalankan blok kod jika anda berada di dua lajur pertama:

Private Sub Worksheet_Change(ByVal Target As Range) ThisColumn = Target.Column If ThisColumn 1 Then NewInput = Left(UserInput, Len(UserInput) - 2) & ":" & Right(UserInput, 2) Application.EnableEvents = False Target = NewInput Application.EnableEvents = True End If End If End Sub

Sekiranya anda ingin membuat perubahan pada lembaran kerja tanpa memasukkan titik dua (contohnya anda perlu menambahkan formula atau mengubah tajuk, dll.) Anda boleh menukar pengendali acara dengan makro pendek ini:

Sub TurnEventHanderOff() Application.EnableEvents = False End Sub You can turn event handlers back on with this macro: Sub TurnEventHanderOff() Application.EnableEvents = True End Sub

Sekiranya anda mengambil konsep ini dan mengubahnya, ada konsep penting yang perlu diberi perhatian. Apabila makro pengendali acara memberikan nilai baru ke sel yang dirujuk oleh Target, Excel mengira ini sebagai perubahan lembaran kerja. Sekiranya anda tidak menukar giliran pengendali acara secara ringkas, Excel akan secara berkala mula memanggil pengendali acara dan anda mendapat hasil yang tidak dijangka. Sebelum membuat perubahan pada lembar kerja dalam pengendali peristiwa perubahan, pastikan untuk menangguhkan sementara pengendalian acara dengan baris Aplikasi.EnableEvents.

Artikel menarik...