Skip to main content

Conas aird a tharraingt ar chill nó as a chéile leis an mbosca seiceála in Excel?

Mar a thaispeántar thíos an pictiúr, ní mór duit an tsraith nó an cill a aibhsiú leis an mbosca seiceála. Nuair a dhéantar bosca seiceála a sheiceáil, aibhsítear sraith shonraithe nó cill go huathoibríoch. Ach conas é a bhaint amach in Excel? Taispeánfaidh an t-alt seo dhá mhodh duit chun é a bhaint amach.

Aibhsigh cill nó as a chéile le ticbhosca le Formáidiú Coinníollach
Aibhsigh cill nó as a chéile le bosca seiceála le cód VBA


Aibhsigh cill nó as a chéile le ticbhosca le Formáidiú Coinníollach

Is féidir leat riail Formáidithe Coinníollach a chruthú chun aird a tharraingt ar chill nó as a chéile le ticbhosca in Excel. Déan mar a leanas le do thoil.

Nasc gach bosca seiceála le cill shonraithe

1. Ní mór duit boscaí seiceála a chur isteach i gcealla ceann ar cheann de láimh trí chliceáil Forbróir > Ionsáigh > Bosca Seiceála (Rialú Foirme).

2. Anois tá boscaí seiceála curtha isteach i gcealla i gcolún I. Roghnaigh an chéad bhosca seiceála in I1, iontráil an fhoirmle = $ J1 isteach sa bharra foirmle, agus ansin brúigh an Iontráil eochair.

Leid: Mura dteastaíonn uait luachanna a bheith bainteach le cealla cóngaracha le boscaí seiceála, is féidir leat an ticbhosca a nascadh le cill bhileog oibre eile mar = Bileog3! $ E1.

2. Déan céim 1 arís go dtí go mbeidh gach bosca seiceála nasctha leis na cealla nó na cealla cóngaracha i mbileog oibre eile.
nótaí: Ba chóir go mbeadh na cealla nasctha go léir i ndiaidh a chéile agus lonnaithe sa cholún céanna.

Cruthaigh riail um Fhormáidiú Coinníollach

Anois ní mór duit riail Formáidithe Coinníollach a chruthú mar a leanas céim ar chéim.

1. Roghnaigh na sraitheanna is gá duit a aibhsiú le boscaí seiceála, ansin cliceáil Formáidiú Coinníollach > Riail Nua faoi ​​na Baile cluaisín. Féach an pictiúr:

2. Sa Riail Nua Formáidithe bosca dialóige, ní mór duit:

2.1 Roghnaigh an Úsáid foirmle chun a fháil amach cé na cealla atá le formáidiú rogha sa Roghnaigh Cineál Riail bosca;

2.2 Iontráil foirmle = MÁ ($ J1 = TRUE, TRUE, FALSE) isteach sa Luachanna formáide nuair atá an fhoirmle seo fíor bosca;
      Or = MÁ (Bileog3! $ E1 = TRUE, TRUE, FALSE) má tá na boscaí seiceála nasctha le bileog oibre eile.

2.3 Cliceáil ar an déanta cnaipe chun dath aibhsithe a shonrú do na sraitheanna;

2.4 Cliceáil ar an OK cnaipe. Féach an pictiúr:

nótaí: San fhoirmle, $ J1 or $ E1 Is í an chéad chill nasctha do na boscaí seiceála, agus déan cinnte gur athraíodh an tagairt cille go colún iomlán (J1> $ J1 or E1> $ E1).

Anois cruthaítear an riail um Fhormáidiú Coinníollach. Agus na boscaí seiceála á seiceáil, cuirfear na sraitheanna comhfhreagracha chun suntais go huathoibríoch mar a thaispeántar pictiúr bellows.


Aibhsigh cill nó as a chéile le bosca seiceála le cód VBA

Is féidir leis an gcód VBA seo a leanas cabhrú leat aird a tharraingt ar chill nó as a chéile le bosca seiceála in Excel. Déan mar a leanas le do thoil.

1. Sa bhileog oibre ní mór duit cill nó rámha a aibhsiú leis an mbosca seiceála. Cliceáil ar dheis ar an Cluaisín Bileog agus roghnaigh Féach an cód ón roghchlár cliceáil ar dheis chun an Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

2. Ansin cóipeáil agus greamaigh an cód VBA thíos i bhfuinneog an Chóid.

Cód VBA: Aibhsigh buaic le bosca seiceála in Excel

Sub AddCheckBox()
Dim xCell As Range
Dim xRng As Range
Dim I As Integer
Dim xChk As CheckBox
On Error Resume Next
InputC:
    Set xRng = Application.InputBox("Please select the column range to insert checkboxes:", "Kutools for Excel", Selection.Address, , , , , 8)
If xRng Is Nothing Then Exit Sub
If xRng.Columns.Count > 1 Then
    MsgBox "The selected range should be a single column", vbInformation, "Kutools fro Excel"
    GoTo InputC
Else
    If xRng.Columns.Count = 1 Then
        For Each xCell In xRng        
            With ActiveSheet.CheckBoxes.Add(xCell.Left, _
               xCell.Top, xCell.Width = 15, xCell.Height = 12)
               .LinkedCell = xCell.Offset(, 1).Address(External:=False)
               .Interior.ColorIndex = xlNone
               .Caption = ""
               .Name = "Check Box " & xCell.Row
            End With    
            xRng.Rows(xCell.Row).Interior.ColorIndex = xlNone                  
        Next        
    End If    
    With xRng    
     .Rows.RowHeight = 16    
    End With   
    xRng.ColumnWidth = 5#    
    xRng.Cells(1, 1).Offset(0, 1).Select    
    For Each xChk In ActiveSheet.CheckBoxes   
      xChk.OnAction = ActiveSheet.Name + ".InsertBgColor"      
    Next
End If
End Sub

Sub InsertBgColor()
Dim xName As Integer
Dim xChk As CheckBox
For Each xChk In ActiveSheet.CheckBoxes 
  xName = Right(xChk.Name, Len(xChk.Name) - 10) 
  If (xName = Range(xChk.LinkedCell).Row) Then   
   If (Range(xChk.LinkedCell) = "True") Then   
    Range("A" & xName, Range(xChk.LinkedCell).Offset(0, -2)).Interior.ColorIndex = 6    
   Else    
    Range("A" & xName, Range(xChk.LinkedCell).Offset(0, -2)).Interior.ColorIndex = xlNone  
   End If  
  End If
Next
End Sub

3. Brúigh an F5 eochair chun an cód a rith. (nótaí: ba chóir duit an cúrsóir a chur sa chéad chuid den chód chun an eochair F5 a chur i bhfeidhm) Sa phreabadh suas Kutools le haghaidh Excel bosca dialóige, roghnaigh an raon is mian leat boscaí seiceála a chur isteach, agus ansin cliceáil ar an OK cnaipe. Roghnaím raon I1: I6 anseo. Féach an pictiúr:

4. Ansin cuirtear boscaí seiceála isteach i gcealla roghnaithe. Seiceáil aon cheann de na ticbhoscaí, aibhseofar an tsraith chomhfhreagrach go huathoibríoch mar atá thíos an pictiúr a thaispeántar.


Earraí gaolmhara:

Uirlisí Táirgiúlachta Oifige is Fearr

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 ...

cluaisín kte 201905


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 (3)
No ratings yet. Be the first to rate!
This comment was minimized by the moderator on the site
This is a horrendous article. It lacks a lot of information and steps. If you follow this article verbatim it will not end in the result you are seeking.

Essentially the article is saying to have 2 columns where the checkboxes are linked to adjacent columns to enter in values which are then used for conditional formats. No one wants to have values associated in adjacent cells to checkboxes. Lastly, if this is the route you're going you're not linking checkboxes to conditional formats, you are linking checkboxes to cell values which are then in turn associated to conditional formats.

It is easier to just use icons instead of checkboxes (use the green checkmark icon) and create the conditional formats for a value of 1 or 0. If cell = 1 then it will replace the value with the icon and highlight your row. To accomplish this you use 2 conditional formats on your table.

Top left of table is B4, bottom right of table is L28

1st conditional format:
USE A FORMULA TO DETERMINE WHICH CELLS TO FORMAT
Formula: =$B4=1
Format: fill
Applies to: =$B4:$L28

2nd conditional format:
FORMAT CELLS BASED ON THEIR VALUES
Icon Set Custom
SHOW ICON ONLY (check this box off)
First icon (green checkmark) when value is > = 1 (type: number)
Second icon (no icon) when value is > = -1 (type: number)
Third icon (no icon) when < -1

Now, when I enter a 1 in B4 or any of the B column cells, it will highlight the entire row for me and replace the "1" with a checkmark.

BUILT-IN TEMPLATE WITH THIS FORMATTING:
1) Open Excel, search for a new template. Enter "Inventory" as the search term
2) Select the template titled "Inventory list with highlighting"
3) Highlight the first row of the table, open conditional formats to manage/edit. You will see the 2nd and 3rd formats are for highlights and the icon in the B column. You can change the icon to whatever you want. Remove the first format if you don't want the strikeout options from the Discontinued column.
This comment was minimized by the moderator on the site
I have a question about the initial step of linking the checkbox to a true/false.

1. Select the first check box in I1, enter formula =$J$1 into the formula bar, and then press the Enter key.

2. Repeat step 1 until all check boxes are linked to the adjacent cells.

For the repeat, does it have to be done for each cell or can you get the drag down to autofill? Right now, when I drag down the corner box it will autofill with =$J$1 for everything so that if I check one box, every box is checked. How can I fix this without manually linking each checkbox?
This comment was minimized by the moderator on the site
Hi,
The Fill Handle can't help in this case. You need to manually link each checkbox to its adjacent cell.
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations