Ir para o conteúdo principal

Como inserir rapidamente várias caixas de seleção em Excel?

Como podemos inserir rapidamente várias caixas de seleção em Excel? Por favor, siga estes métodos complicados em Excel:

Insira várias caixas de seleção com a alça de preenchimento

Insira várias caixas de seleção com o código VBA

Insira e exclua várias caixas de seleção com um clique usando Kutools for Excel

seta azul bolha direita Insira várias caixas de seleção com a alça de preenchimento

In Excel, Fill Handle é uma função poderosa para fazer muitas coisas. Ele também pode preencher as caixas de seleção. Primeiro, você deve inserir uma caixa de seleção. Faça o seguinte:

1. Clique Desenvolvedor na barra de menu e clique em inserçãoe escolha a imagem da caixa de seleção em Controle de formulário. Veja a imagem:

doc-inserir-múltiplas-caixas de seleção1

2. Clique na imagem da caixa de seleção e você verá o ponteiro do sinal de mais, clique onde deseja adicionar a caixa de seleção.

doc-inserir-múltiplas-caixas de seleção2

3. Marque a caixa de seleção e mova-a para uma célula e exclua o nome da caixa de seleção.

4. Em seguida, selecione a célula onde a caixa de seleção permanece. E arraste a alça de preenchimento sobre o intervalo de células que deseja preencher. Veja a imagem:

doc-inserir-múltiplas-caixas de seleção3

Insira várias caixas de seleção nas seleções com um clique:

Kutools for Excel's Caixas de seleção de inserção de lote O utilitário pode ajudá-lo a inserir rapidamente várias caixas de seleção de uma vez. Clique para baixar gratuitamente este recurso.

doc inserir várias caixas de seleção 10

Kutools for Excel: com mais de 300 úteis Excel suplementos, gratuitos para teste sem limitação em 30 dias. Baixe e teste grátis agora!

seta azul bolha direita Insira várias caixas de seleção com o código VBA

Se você acha que o primeiro método é um tanto problemático, o código VBA a seguir pode ajudá-lo a inserir várias caixas de seleção de maneira fácil e rápida.

1. Clique Desenvolvedor > Visual Basic, Uma nova Microsoft Visual Basic para aplicativos janela será exibida, clique inserção > Móduloe insira o seguinte código no Módulo:

Sub InsertCheckBoxes()
'Updateby Extendoffice
Dim Rng As Range
Dim WorkRng As Range
Dim Ws As Worksheet
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
Set Ws = Application.ActiveSheet
Application.ScreenUpdating = False
For Each Rng In WorkRng
    With Ws.CheckBoxes.Add(Rng.Left, Rng.Top, Rng.Width, Rng.Height)
        .Characters.Text = Rng.Value
    End With
Next
WorkRng.ClearContents
WorkRng.Select
Application.ScreenUpdating = True
End Sub

2. Então clique doc-multiplicação-cálculo-3 botão para executar o código. E uma caixa de prompt aparecerá para lembrá-lo de selecionar um intervalo de células nas quais deseja inserir as caixas de seleção. Veja a imagem:

doc-inserir-múltiplas-caixas de seleção4

3. E, em seguida, clique em OK, as caixas de seleção foram inseridas na faixa de maneira limpa e bonita. Veja a imagem:

doc-inserir-múltiplas-caixas de seleção5

seta azul bolha direita Insira várias caixas de seleção com Kutools for Excel

Aqui, posso apresentar a você uma maneira fácil e prática de inserir várias caixas de seleção-Kutools for Excel, Com o seu Caixa de seleção de inserção de lote recurso, você pode inserir as caixas de seleção em células em branco ou células com dados.

Kutools for Excel : com mais de 300 úteis Excel suplementos, gratuitos para teste sem limitação em 30 dias.
Vá para o download
Teste grátis 30 diasComprar
PayPal / MeuCommerce

Depois de instalar Kutools for Excel, siga os seguintes passos:

1. Selecione um intervalo de células que deseja inserir nas caixas de seleção.

2. Clique Kutools > inserção > Caixas de seleção de inserção de lote, veja a captura de tela:

doc-inserir-múltiplas-caixas de seleção06

3. Em seguida, as células selecionadas foram preenchidas com as caixas de seleção conforme as seguintes capturas de tela mostradas:

Insira as caixas de seleção nas células em branco Insira as caixas de seleção nas células de dados
doc-inserir-múltiplas-caixas de seleção7 doc-inserir-múltiplas-caixas de seleção8

Dicas. Se você deseja excluir rapidamente várias caixas de seleção, tente usar o Kutools for Excel'S Excluir caixas de seleção como mostrado na imagem a seguir. Funciona sem limitações em 60 dias, faça o download e tenha um teste gratuito agora.

doc-inserir-múltiplas-caixas de seleção9

Kutools for Excel: com mais de 300 úteis Excel suplementos, gratuitos para teste sem limitação em 30 dias. Baixe e teste grátis agora!

seta azul bolha direita Insira e exclua várias caixas de seleção com Kutools for Excel

Kutools for Excel: com mais de 300 úteis Excel suplementos, gratuitos para teste sem limitação em 30 dias. Baixe e teste grátis agora!

Artigo relacionado:

Como excluir rapidamente várias caixas de seleção em Excel?

Melhores ferramentas de produtividade de escritório

Turbine suas planilhas: Experimente eficiência como nunca antes com Kutools for Excel

Recursos mais comuns: Localizar/destacar/identificar duplicatas   |  Excluir linhas em branco   |  Combine colunas ou células sem perder dados   |   Rodada sem Fórmula ...
Super pesquisa: VLookup de múltiplos critérios  |   VLookup de múltiplos valores  |   VLookup em várias planilhas   |   Pesquisa Difusa ....
Lista suspensa avançada: Crie rapidamente uma lista suspensa   |  Lista suspensa de dependentes   |  Lista suspensa de seleção múltipla ....
Gerenciador de colunas: Adicione um número específico de colunas   |   Mover colunas   |   Exibir colunas   |   Comparar colunas com Selecionar células iguais e diferentes ...
Recursos em destaque: Foco da Grade   |  Vista de Design   |   Grande Barra de Fórmula   |  Gerenciador de pastas de trabalho e planilhas   |  Biblioteca (Auto texto)   |  Data Picker   |  Combinar planilhas   |  Criptografar/Descriptografar Células   |  Enviar e-mails por lista   |  Super Filtro   |   Filtro Especial (filtro negrito/itálico/tachado...) ...
15 principais conjuntos de ferramentas:  12 Texto Ferramentas (Adicionar texto, Remover Personagens, ...)   |   50+ de cores Tipos (Gráfico de Gantt, ...)   |   Mais de 40 práticos Fórmulas (Calcule a idade com base no aniversário, ...)   |   19 Inclusão Ferramentas (Insira o código QR, Inserir imagem do caminho, ...)   |   12 Conversão Ferramentas (Números para Words, Conversão de moedas, ...)   |   7 Unir e dividir Ferramentas (Combinar linhas avançadas, Dividir células, ...)   |   Muito mais...

Kutools for Excel possui mais de 300 recursos, garantindo que o que você precisa está a apenas um clique de distância...

Suporta Escritório/Excel 2007-2021 e mais recentes, incluindo 365 | Disponível em 44 idiomas | Desfrute de um teste gratuito de 30 dias com todos os recursos.

guia kte 201905

Office Tab Traz a interface com guias para o Office e torna seu trabalho muito mais fácil

  • Ative a edição e leitura com guias em Word, Excel, Power Point, Publisher, Access, Visio e Project.
  • Abra e crie vários documentos em novas guias da mesma janela, em vez de em novas janelas.
  • Aumenta sua produtividade em 50% e reduz centenas de cliques do mouse para você todos os dias!
Comments (51)
No ratings yet. Be the first to rate!
Rohit
Rohit
This comment was minimized by the moderator on the site
Thanks for the tips, could help me , I am trying to select multiple checkboxes and based on selection count should be displayed. could help me with macro code for same
Reply
0
0
skyyang
skyyang     Rohit
This comment was minimized by the moderator on the site
Hello, Rohit,

May be the following article can help you, please view it.
https://www.extendoffice.com/documents/excel/2460-excel-sum-count-checkboxes.html

Hope it can help you!
Reply
Report
0
0
Wayne Edmondson
Wayne Edmondson
This comment was minimized by the moderator on the site
Hello and thank you for the tips above. Note that in your Sub, you must declare the variable xTitleId with Dim xTitleId as String.. otherwise the routine will produce an error, assuming the user has VBA set with Option Explicit. The Dim above solves the problem and the Sub works perfectly thereafter. Thanks for the great advice and information.
Reply
0
0
Tianao Li
Tianao Li
This comment was minimized by the moderator on the site
Great macro, gracefully written!!! Thank you!
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
I just purchased the program. When I insert the checkboxes, they are slightly lower than the cell itself, so they are encroaching into the cell below. How can I reposition each of those cells?
Reply
0
0
Nesh
Nesh
This comment was minimized by the moderator on the site
Hye there. im new here n new for using excel.can i know how to put other excel sheet under one excel sheet. example when i open 1 excel sheet and i got my audit plan. Then i got list of country overall such as Canada and Belgium. So what i wan to know how if i click Canada and my audit plan for Canada will appear.
Reply
0
0
Ruby Lee
Ruby Lee
This comment was minimized by the moderator on the site
Why is my name in this list? I did not take this class.
Reply
0
0
sam
sam
This comment was minimized by the moderator on the site
Hi Just followed your code to create a list of checkboxes on my spreadsheet, I am very new to this but have managed to follow it and worked perfectly. I just cant remove the checkbox labels coming up? i have a pretty long spreadsheet and deleting them individually is impossible. can i remove the labels that come up with each checkbox??? please help
Reply
0
0
Matthew
Matthew
This comment was minimized by the moderator on the site
Really good macro How do insert them already checked? i.e. their value is already 1? Thanks
Reply
0
0
Kamolga
Kamolga
This comment was minimized by the moderator on the site
THANK YOU!!! Great post!
Reply
0
0
LLD
LLD
This comment was minimized by the moderator on the site
Hello, this macro is a GREAT timesaver - thank you! I'm terrible with VBA, but I was trying to determine which line(s) in the code I may be able to comment to keep the range-reminder dialog from appearing. Is that possible? I'm prepping some checklists for our marketing group and entering a MILLION checkboxes (so you can imagine how much this macro helps), but I have to stop with each group/range and click on that darn dialog. Any way to disable it so I can maybe re-enable it later? Thanks for any help! -LLD
Reply
0
0
JeffFromTims
JeffFromTims
This comment was minimized by the moderator on the site
I'm attempting to make a call log (in a table format) that has check boxes. I want to be able to sort/filter the table and have the check boxes move accordingly with the other data. At the moment, they're fixed.
Reply
0
0
TipTup21
TipTup21
This comment was minimized by the moderator on the site
Great Post! Is there a Way to do the VBA Code with toggle buttons?
Reply
0
0
Jessica
Jessica
This comment was minimized by the moderator on the site
:-) Hello, Thanks for the tips! I was wondering, is there another simple way to insert checkboxes into the actual cell..vs floating? Also, can you insert... -as a dropdown field - categorizable (is that even a word? ;/) -in the cell - so you can format alignment thanks!
Reply
0
0
Kman
Kman     Jessica
This comment was minimized by the moderator on the site
There is no built-in way that Microsoft makes that lets you turn Excel into a check list. There are tons of different list applications. I do know one way that you can make it the checkmarks work in the way that you are probably expecting but it is not simple or straightforward and would not be practical unless you did it once. http://www.excelhero.com/blog/2011/03/excel-dynamic-checkmark.html<br /> It uses conditional formatting, dynamically named ranges and an event listener script. You could also just use a font and type it in. http://www.addintools.com/documents/excel/where-is-check-mark-symbol.html<br /> And about text alignment within a cell through a dropdown,... I'm not sure what you want to do. But if you want the text alignment to change with the value in a cell you could try a custom number format where you use asterisk followed by a space to repeat a space. Format the cells as center aligned. That would force the values entered into the cell to the left or the right depending on your custom number format: http://office.microsoft.com/en-us/excel-help/create-or-delete-a-custom-number-format-HP001216503.aspx
Reply
0
0
Brian
Brian
This comment was minimized by the moderator on the site
I have an xml mapped excel spreadsheet where a user copies and pastes a number of rows then we can export to an xml file. I would like to add a column with check boxes and have the user check appropriately then have that field export as true/false to xml. Any idea how this could be accomplished?
Reply
0
0
Kman
Kman     Brian
This comment was minimized by the moderator on the site
If I understand you correctly you have an xml mapped excel file that is intended for users to copy and paste into. Then the user users the XML Export tool to create a second file that is an XML file that is then used in another process. Well if you have the excel file with cells mapped you could have the check boxes setup in the file ahead of time. The checkboxes can be placed on cells that are mapped. The checkboxes insert True/False into the cells they are linked to or turn empty or to a check mark depending on what is entered into the cell. If a user pastes values into those cells the checkbox won't be affected unless that pasted value is "True" or "1". You can have the user either paste values or use the checkboxes to enter TRUE/FALSE. Once the user has gotten the excel file the way they want they can then export to XML.
Reply
0
0
Ed
Ed
This comment was minimized by the moderator on the site
[quote][quote]Yes. Yes, there is. There is always a way.By MikeC[/quote] OK. How would I accomplish this. I am not well versed in excel syntax. I have tried .text and .value (for c in the above code). With incorrect results. I have tried a myriad of things that would normally be the result but no luck. What I have is a large bullet list of items that I would like to [quote][quote]Yes. Yes, there is. There is always a way.By MikeC[/quote] OK. How would I accomplish this. I am not well versed in excel syntax. I have tried .text and .value (for c in the above code). With incorrect results. I have tried a myriad of things that would normally be the result but no luck. What I have is a large bullet list of items that I would like to replace the bullet with a check box and have the value and text of that box be the original text of the bullet itemBy Danny[/quote]e] replace the bullet with a check box and have the value and text of that box be the original text of the bullet itemBy Danny[/quote] Original code, plus it takes the list that is in the cells and uses that as the text for the check box Sub AddCheckBoxes() On Error Resume Next Dim c As Range, myRange As Range, Text As String Set myRange = Selection For Each c In myRange.Cells BoxLabel = c.Value '
Reply
0
0
Jennebeth Monserat
Jennebeth Monserat
This comment was minimized by the moderator on the site
it really helped a lot. thank to you!
Reply
0
0
Ali Mac
Ali Mac
This comment was minimized by the moderator on the site
hi, this macro works beautifully for me. i needed a little favour - i would like to ensure that the checkboxes are placed bang in the centre of the cell. can someone please help? best regards.
Reply
0
0
Ali Mac
Ali Mac
This comment was minimized by the moderator on the site
hi, would you be able to help with one small tweak to your code, which is working just great for me otherwise? i would like the cell to be exactly centered within the cell housing it. thanks and regards.
Reply
0
0
Jackeroo
Jackeroo     Ali Mac
This comment was minimized by the moderator on the site
[quote]hi, would you be able to help with one small tweak to your code, which is working just great for me otherwise? i would like the cell to be exactly centered within the cell housing it. thanks and regards.By Ali Mac[/quote] For Each Rng In WorkRng With Ws.CheckBoxes.Add((Rng.Offset(0, 1).Left - Rng.Left) / 2 + Rng.Left - 8, Rng.Top, 0.33, Rng.Height) .Characters.Text = Rng.Value End With
Reply
0
0
Cole
Cole
This comment was minimized by the moderator on the site
Hey, I found this and it works great, but im using it for a daily checklist and i put rows, and when i check one box (i.e. in row 3) all check boxes in that row are checked. Is there a way to make each check box individual/independent?
Reply
0
0
Kman
Kman     Cole
This comment was minimized by the moderator on the site
You need to either delete those check boxes that are linked and re-create them with the macro or you can try the code I posted earlier that resets the link between cells and check boxes.
Reply
0
0
xyz
xyz
This comment was minimized by the moderator on the site
The code worked great. Thank you!
Reply
0
0
Kman
Kman
This comment was minimized by the moderator on the site
Sub RelinkCheckBoxes() 'You select cells that include the checkboxes that are acting weird. 'Then you run the macro. 'It changes those checkboxes so that the linked cells correspond to the cells underneath. 'Also it makes the checkbox text turn into the cell text. Dim ckbox As Shape Dim rngLinkedCell As Range If ActiveSheet.CheckBoxes.Count = 0 Then Exit Sub 'Test that selection is a range. If TypeName(Selection) "Range" Then Exit Sub For Each ckbox In ActiveSheet.Shapes 'Test that the shape is a control. If ckbox.Type = msoFormControl Then 'test that the control is a checkbox If ckbox.FormControlType = xlCheckBox Then 'find the cell under the checkbox that is within the selection Set rngLinkedCell = Application.Intersect(ckbox.TopLeftCell, Selection) 'assign a new link address and rename the checkbox to match. If Not rngLinkedCell Is Nothing Then With ckbox .OLEFormat.Object.LinkedCell = rngLinkedCell.Address .Name = rngLinkedCell.Address .TextFrame.Characters.Text = rngLinkedCell.Text End With End If End If End If Next
Reply
0
0
FAISAL
FAISAL
This comment was minimized by the moderator on the site
how to change cell address
Reply
0
0
Reuben
Reuben
This comment was minimized by the moderator on the site
I have a excel spreadsheet that has a question and then to the right are other columns "Controls Met, column f and G with check boxes that have been check, column F yes or column G no. I only want to use the filter to get each of the rolls that have a check mark, but the filter does not see s check mark
Reply
0
0
Meg
Meg
This comment was minimized by the moderator on the site
Hey guys, this is so helpful - thanks! Quick question, I'm completely inexperienced with this type of stuff so sorry if it's a stupid one! I would like to just have tick boxes (without the true or false) and I would like the boxes to be yellow when not ticked but have no fill once ticked, to indicate students who have not handed in drafts and assessment, etc. Is this possible? Could someone please post the actual code for this, or walk me through it? Many thanks in advance!!!
Reply
0
0
FAISAL
FAISAL     Meg
This comment was minimized by the moderator on the site
how to change the reference address for multiple checkboxes
Reply
0
0
MikeC
MikeC
This comment was minimized by the moderator on the site
Is there a way to have the checkbox text be the text of the selected cell?
Reply
0
0
Danny
Danny     MikeC
This comment was minimized by the moderator on the site
Yes. Yes, there is. There is always a way.
Reply
0
0
MikeC
MikeC     Danny
This comment was minimized by the moderator on the site
[quote]Yes. Yes, there is. There is always a way.By Danny[/quote] OK. How would I accomplish this. I am not well versed in excel syntax. I have tried .text and .value (for c in the above code). With incorrect results. I have tried a myriad of things that would normally be the result but no luck. What I have is a large bullet list of items that I would like to replace the bullet with a check box and have the value and text of that box be the original text of the bullet item
Reply
0
0
Ejaz-Ul-Haque
Ejaz-Ul-Haque
This comment was minimized by the moderator on the site
Thanks a lot!, for check box.
Reply
0
0
Priyanka
Priyanka
This comment was minimized by the moderator on the site
Can we then filter those check boxes? Basically display only those who attended?
Reply
0
0
Kman
Kman     Priyanka
This comment was minimized by the moderator on the site
Yeah totally. The checkboxes are actually independent of the cells. They basically float over the spreadsheet and are linked to a cell by address inserting True and False values when clicked (or N/A if you make it a three-state checkbox). But when you filter in excel it hides specific rows. It will not hide the check boxes. I really don't suggest filtering with this macro. Strange things will happen and you might not realize when there are several check boxes sitting on top of each other or that two check boxes are linked to one cell and a cell is linked to no checkboxes. What I usually do is I will create a list and instead of checkmarks I will type a 1 or a 0 into the adjacent cell and then total. You can even pivot table the thing if your information gets complicated. For instance say you had another column that had the gender of your attendees. You could figure out how many men and how many women attended.
Reply
0
0
Priyanka
Priyanka
This comment was minimized by the moderator on the site
Can we then filter on those check boxes? Basically display only those who attended?
Reply
0
0
Kman
Kman
This comment was minimized by the moderator on the site
The check boxes go a bit nuts when you copy them with their linked cells. I had to write a thing to reset the linking for when I want to copy those cells somewhere else.
Reply
0
0
CM_Mario
CM_Mario
This comment was minimized by the moderator on the site
It keeps giving me an error when I open it that it had to fix errors? Any assistance? If someone would like to help I will post the report that I'm getting from Microsoft office.
Reply
0
0
Violet
Violet
This comment was minimized by the moderator on the site
The code works great! I edit the color now I'd like to center the checkboxes in each cell, any idea what I would need to modify for that? Thanks!
Reply
0
0
Jackeroo
Jackeroo     Violet
This comment was minimized by the moderator on the site
[quote]The code works great! I edit the color now I'd like to center the checkboxes in each cell, any idea what I would need to modify for that? Thanks!By Violet[/quote] For Each Rng In WorkRng With Ws.CheckBoxes.Add((Rng.Offset(0, 1).Left - Rng.Left) / 2 + Rng.Left - CkBoxSize, Rng.Top, 0.33, Rng.Height) .Characters.Text = Rng.Value End With
Reply
0
0
Lior
Lior
This comment was minimized by the moderator on the site
I did it but when I check one boc, all are being checked together. how can I separate them?
Reply
0
0
# Sean
# Sean
This comment was minimized by the moderator on the site
When I did this, everything worked great (Thank you!) except when the boxes are checked, it automatically has a yellow fill color in the cell. How do I make it to where the only thing that happens is the box is checked, no fill color? Thanks!
Reply
0
0
Dennis
Dennis     # Sean
This comment was minimized by the moderator on the site
[quote]When I did this, everything worked great (Thank you!) except when the boxes are checked, it automatically has a yellow fill color in the cell. How do I make it to where the only thing that happens is the box is checked, no fill color? Thanks!By # Sean[/quote] Just change the line like it says in the comments: .FormatConditions(1).Interior.ColorIndex = 6 'change for other color when ticked Leave it out completely if you don't want a color change. Cheers!
Reply
0
0
O
O
This comment was minimized by the moderator on the site
AWESOME! Thank you so much!
Reply
0
0
Suleman
Suleman
This comment was minimized by the moderator on the site
Excellent work. Thank you :roll:
Reply
0
0
Jack
Jack
This comment was minimized by the moderator on the site
excellent macro works add checkbox in the cell, thank u :-)
Reply
0
0
Daniel
Daniel
This comment was minimized by the moderator on the site
Why is there only one boy in this class????
Reply
0
0
Jay Chivo
Jay Chivo     Daniel
This comment was minimized by the moderator on the site
[quote]Why is there only one boy in this class????By Daniel[/quote] :lol: That's just for demonstration.
Reply
0
0
Prem Gurung
Prem Gurung
This comment was minimized by the moderator on the site
Great trick... I enjoyed it.
Reply
0
0
Jamie
Jamie
This comment was minimized by the moderator on the site
Excellent macro that works beautifully to quickly and easily insert check boxes into a selected range; thank you. Would you consider showing an example of a macro to insert in a selected range a customized drop down list with three options: "Yes", "No", and "Maybe"?
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations