Petua: Bahasa lain diterjemahkan Google. Anda boleh melawat English versi pautan ini.
Log masuk
x
or
x
x
Daftar Sekarang
x

or

Bagaimana dobel klik sel untuk membuka lembaran kerja yang dinyatakan dalam Excel?

Adakah anda mahu dengan cepat menavigasi ke lembaran kerja yang dinyatakan dalam buku kerja Excel? Artikel ini akan menyediakan kaedah VBA untuk membuka lembaran kerja tertentu dengan mengklik dua kali sel tertentu dalam Excel.

Klik dua kali sel untuk membuka lembaran kerja yang dinyatakan dengan kod VBA


Klik dua kali sel untuk membuka lembaran kerja yang dinyatakan dengan kod VBA


Sila lakukan seperti berikut untuk membuka lembaran kerja tertentu dengan mengklik dua kali sel dalam Excel.

1. Klik kanan tab helaian yang mengandungi sel yang anda hendak buka lembaran kerja dengan mengklik padanya. Kemudian klik Lihat Kod dari menu konteks. Lihat tangkapan skrin:

2. Dalam pembukaan Microsoft Visual Basic untuk Aplikasi tetingkap, sila salin kod VBA berikut ke tetingkap Kod.

Kod VBA: sel double klik untuk membuka lembaran kerja tertentu dalam Excel

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Updated by Extendoffice 20180822
Dim xArray, xAValue As Variant
Dim xFNum As Long
Dim xStr, xStrRg, xStrSheetName As String
xRgArray = Array("A1;Sheet2", "A12;Sheet3", "A4;Sheet4", "A100;Sheet5")
On Error Resume Next
For xFNum = LBound(xRgArray) To UBound(xRgArray)
xStr = ""
xStr = xRgArray(xFNum)
xAValue = ""
xAValue = Split(xStr, ";")
xStrRg = ""
xStrRg = xAValue(0)
xStrSheetName = ""
xStrSheetName = xAValue(1)
If Not Intersect(Target, Range(xStrRg)) Is Nothing Then
Sheets(xStrSheetName).Activate
End If
Next
End Sub

Nota: Dalam kod VBA, "A1; Sheet2""A12; Sheet3""A4; Sheet4""A100; Sheet5"bermakna sel klik doube A1 akan membuka Sheet2, klik dua kali A2 akan membuka Sheet3 ..., sila ubah berdasarkan keperluan anda.

3. Tekan butang Alt + Q kunci bersama untuk menutup Microsoft Visual Basic untuk Aplikasi tingkap.

Mulai sekarang, apabila mengklik dua kali sel A1 dalam lembaran kerja semasa, lembaran kerja yang dinyatakan akan diaktifkan dengan serta-merta.


Related Articles:



Cadangan Alat Produktiviti untuk Excel

Kutools untuk Excel Membantu Anda Selalu Menyelesaikan Kerja di Hadapan Masa, dan Berdiri Daripada Orang

  • Lebih daripada ciri-ciri canggih 300 yang canggih, yang direka untuk senario kerja 1500, meningkatkan produktiviti oleh 70%, memberi anda lebih banyak masa untuk menjaga keluarga dan menikmati kehidupan.
  • Tidak perlu lagi menghafal formula dan kod VBA, berikan rehat dari otak anda sekarang.
  • Menjadi pakar Excel dalam minit 3, Operasi yang rumit dan berulang boleh dilakukan dalam beberapa saat,
  • Mengurangkan beribu-ribu operasi papan kekunci & tetikus setiap hari, mengucapkan selamat tinggal kepada penyakit pekerjaan sekarang.
  • 110,000 orang yang sangat berkesan dan pilihan syarikat 300 + yang terkenal di dunia.
  • Ciri-ciri penuh 60-hari adalah percubaan percuma. Jaminan wang balik 60 hari. Tahun 2 peningkatan dan sokongan percuma.

Membawa Browsing Tab dan Editing ke Microsoft Office, Jauh Lebih Berkuasa Daripada Tab Penyemak Imbas

  • Tab Pejabat direka untuk Aplikasi Pejabat Word, Excel, PowerPoint dan Lain-lain: Penerbit, Akses, Visio dan Projek.
  • Buka dan buat beberapa dokumen dalam tab baharu pada tetingkap yang sama, dan bukannya dalam tetingkap baru.
  • Meningkatkan produktiviti anda oleh 50%, dan mengurangkan beratus-ratus klik tetikus untuk anda setiap hari!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Neil · 3 months ago
    Hi how can i extend my array? it stucks already and i cannot add more of this because it limits to col 1024 only for that line. pls help

    xRgArray = Array("A2;Sheet2", "A3;Sheet3", "A4;Sheet4", "A5;Sheet5")
    • To post as a guest, your comment is unpublished.
      crystal · 1 months ago
      Hi Neil,
      The code works well in my case even extended my array to Array = Array("A2;Sheet2", "A3;Sheet3", "A4;Sheet4", "A5;Sheet5", "A6;Sheet6").
      Can you tell me your Excel version?
  • To post as a guest, your comment is unpublished.
    James · 8 months ago
    After you get to the desired sheet. Is there a way to copy information from a cell in that sheet and automatically go back to the cell I double clicked on originally in the first sheet?
    • To post as a guest, your comment is unpublished.
      crystal · 7 months ago
      Hi James
      You need to manually click the original worksheet tab to back to it. Sorry can't take this into consideration.
  • To post as a guest, your comment is unpublished.
    Guest · 1 years ago
    Is there a way to do multiple codes for one tab? such as clicking on another cell to jump into another worksheet.

    How would that code look like?
    • To post as a guest, your comment is unpublished.
      crystal · 10 months ago
      Good day,

      The below VBA code can help you to solve the problem. Thanks for your comment.

      Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
      Dim xArray As Variant
      Dim xFNum As Long
      Dim xStr, xStrRg, xStrSheetName As String
      xRgArray = Array("A2;Sheet2", "A3;Sheet3", "A4;Sheet4", "A5;Sheet5")
      On Error Resume Next
      For xFNum = LBound(xRgArray) To UBound(xRgArray)
      xStr = ""
      xStr = xRgArray(xFNum)
      xStrRg = ""
      xStrRg = Left(xStr, 2)
      xStrSheetName = ""
      xStrSheetName = Right(xStr, Len(xStr) - 3)
      If Not Intersect(Target, Range(xStrRg)) Is Nothing Then
      Sheets(xStrSheetName).Activate
      End If
      Next
      End Sub
      • To post as a guest, your comment is unpublished.
        SG · 10 months ago
        Hi, In the line that states xStrRg = Left(xStr, 2), this picks up the cell if its a single number cell i.e. A1, A2, A3. but not if its A11, or A111. how do i write the code to allow me to use cells A1, A11, and A111?

        Hope this makes sense, i'm not particularly technical!!
        • To post as a guest, your comment is unpublished.
          crystal · 10 months ago
          Good Day,
          The code has been optimized again. Please have a try and thanks for your comment.

          Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
          Dim xArray, xAValue As Variant
          Dim xFNum As Long
          Dim xStr, xStrRg, xStrSheetName As String
          xRgArray = Array("A1;Sheet2", "A12;Sheet3", "A4;Sheet4", "A100;Sheet5")
          On Error Resume Next
          For xFNum = LBound(xRgArray) To UBound(xRgArray)
          xStr = ""
          xStr = xRgArray(xFNum)
          xAValue = ""
          xAValue = Split(xStr, ";")
          xStrRg = ""
          xStrRg = xAValue(0)
          xStrSheetName = ""
          xStrSheetName = xAValue(1)
          If Not Intersect(Target, Range(xStrRg)) Is Nothing Then
          Sheets(xStrSheetName).Activate
          End If
          Next
          End Sub