Skip to main content

Conas cill a chliceáil faoi dhó chun bileog oibre shonraithe a oscailt in Excel?

Ar mhaith leat nascleanúint a dhéanamh go tapa chuig bileog oibre shonraithe i leabhar oibre Excel? Cuirfidh an t-alt seo modh VBA ar fáil chun bileog oibre shonraithe a oscailt trí chliceáil faoi dhó ar chill áirithe in Excel.

Cliceáil faoi dhó ar chillín chun bileog oibre shonraithe a oscailt le cód VBA


Cliceáil faoi dhó ar chillín chun bileog oibre shonraithe a oscailt le cód VBA

Déan mar a leanas le do thoil chun bileog oibre shonraithe a oscailt trí chill a chliceáil faoi dhó in Excel.

1. Cliceáil ar dheis ar an táb bileog ina bhfuil an chill a theastaíonn uait bileog oibre a oscailt trí chliceáil air. Agus ansin cliceáil Féach an cód ón roghchlár comhthéacs. Féach an pictiúr:

2. San oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog, cóipeáil an cód VBA seo a leanas isteach i bhfuinneog an Chóid.

Cód VBA: cill cliceáil dúbailte chun bileog oibre shonraithe a oscailt in 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

nótaí: Sa chód VBA, "A1; Bileog2""A12; Bileog3""A4; Bileog4""A100; Bileog5"a chiallaíonn go n-osclóidh cill cliceáil doube A1 Bileog 2, osclóidh cliceáil dúbailte A2 Bileog3 ..., athraigh iad le do thoil bunaithe ar do chuid riachtanas.

3. Brúigh an Eile + Q eochracha le chéile chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

As seo amach, nuair a dhéantar cliceáil dúbailte ar chill A1 sa bhileog oibre reatha, cuirfear an bhileog oibre shonraithe i ngníomh láithreach.


Airteagail ghaolmhara:

Uirlisí Táirgiúlachta Oifige is Fearr

🤖 Kutools AI Aide: anailís sonraí a réabhlóidiú bunaithe ar: Forghníomhú Chliste   |  Gin Cód  |  Cruthaigh Foirmlí Saincheaptha  |  Anailís a dhéanamh ar Sonraí agus Cairteacha a Ghin  |  Feidhmeanna Kutools a agairt...
Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

Tuairisc


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
Comments (14)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
Can't get the code to open the other worksheet
This comment was minimized by the moderator on the site
Hi Will,Is there any prompts while using the code?
This comment was minimized by the moderator on the site
Can't get code to open the other sheet, can some help
This comment was minimized by the moderator on the site
hi!
It cannot accept the code for more than 59 sheets.
What code do i need to use to insert more sheets.
When it change the line the code doesnt work.
Help!
This comment was minimized by the moderator on the site
Hi Crystal

I have copied the code and edited according to the name of the worksheets. The code is running but I still cannot open the sheets, what have I done wrong?

Sub OpenbyDoubleclicking(ByVal Target As Range, Cancel As Boolean)

Dim xArray, xAvlaue As Variant '
Dim xFSum As Long
Dim xStr, xStrRg, xStrSheetname As String
xRgArray = Array("A3;FTIR", "A4;Viscometer")
On Error Resume Next
For xFNum = LBound(xRgArray) To UBound(xRgArray)
xStr = ""
xStr = xRgArray(xFNum)
xAValue = ""
xAValue = Split(xStr, ";")
xStrRg = ""
xStrRg = xAValue(0)
xStrSheetname = xAValue(1)
If Not Intersect(Target, Range(xStrRg)) Is Nothing Then _
Sheets(xStrSheetname).Active
End If
Next
End Sub


Many thanks
This comment was minimized by the moderator on the site
Hi Carl,
In your code, please replace the first line with "Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)".
Thank you for your comment. The entire code should be as follows.

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("A3;FTIR", "A4;Viscometer")
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
This comment was minimized by the moderator on the site
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")
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
Hi James
You need to manually click the original worksheet tab to back to it. Sorry can't take this into consideration.
This comment was minimized by the moderator on the site
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?
This comment was minimized by the moderator on the site
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
This comment was minimized by the moderator on the site
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!!
This comment was minimized by the moderator on the site
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
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations