Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako dvokliknite celico, da odprete določen delovni list v Excelu?

Ali se želite v delovnem zvezku Excel hitro pomakniti do določenega delovnega lista? Ta članek bo zagotovil metodo VBA, da odprete določen delovni list tako, da dvokliknete določeno celico v Excelu.

Dvokliknite celico, da odprete določen delovni list s kodo VBA


Dvokliknite celico, da odprete določen delovni list s kodo VBA


Če želite odpreti določen delovni list, naredite tako, da dvokliknete celico v Excelu.

1. Z desno miškino tipko kliknite zavihek stanja, ki vsebuje celico, ki jo želite odpreti z delovnim listom, tako da kliknete nanj. In nato kliknite Ogled kode iz kontekstnega menija. Prikaz slike:

2. V odprtju Microsoft Visual Basic za aplikacije okno, kopirajte naslednjo kodo VBA v okno Code.

VBA koda: dvokliknite celico, da odprete določen delovni list v Excelu

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

Opombe: V kodi VBA, "A1; Sheet2""A12; Sheet3""A4; Sheet4""A100; Sheet5"pomeni, da doube klikne celica A1 odpre Sheet2, dvojni klik A2 bo odprl Sheet3 ..., jih spremenite glede na vaše potrebe.

3. Pritisnite druga + Q skupaj za zapiranje Microsoft Visual Basic za aplikacije okno.

Od zdaj naprej, ko dvakrat kliknete celico A1 v trenutnem delovnem listu, se navedeni delovni list takoj aktivira.


Sorodni članki:


Priporočena orodja za produktivnost za Excel

zavihek kte 201905

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
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