00:00Every Excel user at least one time in their career has experienced this laborious pain
00:04of adding multiple checkboxes in a cell like this.
00:08What if I tell you, you can add checkboxes using a hotkey?
00:12And here's how you do it.
00:14The first thing I have to do is to launch your VBA editor by pressing Alternate F11.
00:19If your Alternate F11 key is not working, I've got another video showcasing how you
00:23can resolve that issue.
00:24If you need the link to that video, please leave a request in the comments section and
00:27I'll send the link to you.
00:28Once your VBA editor launches, go all the way to the top, right click on your VBA project
00:33and you're going to say Insert Module.
00:36And in that module, module number two for me, you're going to Ctrl V and paste this VBA function.
00:42If you'd like a copy of this VBA function, please leave a request as well and I'll send
00:46that function to you.
00:47Let's dissect this VBA function a bit more.
00:50The first two lines are basically defining the variable CHX and the range.
00:54And the third line is basically assigning whatever the user has selected on your spreadsheet as
00:59the selection.
00:59And this will store your row and column.
01:02The next set of lines is going through a for loop, going through every cell that you have
01:06selected and inserting a checkbox using this function here.
01:11And after that, once you insert that checkbox to that particular cell, you're basically changing the
01:15caption or the text on the checkbox as blank and setting the default value of the checkbox as off.
01:22After that, you're linking your checkbox, whatever the status of the checkbox is, to the cell that
01:27the cell is in.
01:28And after that, you're linking the state of the checkbox to the cell that you are in.
01:32It could be either true for checked or false for non-checked.
01:36And then this line here is assigning the default value or the initial value on the cell as false
01:41because when you insert the checkbox for the very first time, it doesn't know its state.
01:45So it would normally be blank.
01:46So it's going to be off, which translates to false.
01:49And after that, I'm changing the font color on the same cell to be white so that it's
01:54not distracting or overlaid behind the checkbox.
01:58Once that's done, you're going to press Ctrl S to save one more time and close my VBA editor
02:02here.
02:02Now you're going to go to developer and then under code, there's this macro icon.
02:06Click on it.
02:07You should see your function called checkbox that you just added a few seconds ago.
02:12After that, select it and go options.
02:14Over here, you can assign the hot key that actually triggers this function.
02:18I'm going to say Ctrl T. But if you would like to have a Ctrl Shift T, you're going to
02:23have
02:23to clear this and you're going to have to press Shift T on your keyboard and you will have
02:26Ctrl Shift T to actually trigger that checkbox or trigger this function.
02:31I'm going to keep it simple and say Ctrl T and I'm going to click on OK.
02:34And I'm basically going to close this pop up here.
02:37Now, if you were to select a series of cells like this and press Ctrl T that you have assigned
02:43previously, you can see that the VBA scripts go ahead and actually creates a checkbox
02:47in each cell.
02:48And the beauty of this script is that let's put this cursor say around here.
02:52Let's say if I toggle this, you can see the status is false.
02:55Now, if you toggle as on, you can see it's basically assigning the cell value with the
03:01state that the checkbox in.
03:03And you can use that state to do manipulation or do your macro or formula.
03:07Now, it's great that you can assign or create multiple checkbox like this.
03:12But to delete the checkbox, the only way I know is right click on it, cut, right click
03:17on it, cut.
03:18As you can see, I do have a bit of challenge right clicking on it and cutting.
03:22And imagine if there's checkbox all across here like this.
03:25And now try deleting this one at a time.
03:27It's quite challenging.
03:29You might say maybe you can do cut or copy or paste or whatever.
03:32It's quite difficult to delete the checkbox once you instantiate it.
03:36How can you do it?
03:37Or better yet, how can you do it with a hotkey?
03:39Let me show you how this is done.
03:41You're going to launch your VBA editor by pressing alternate F11 again.
03:44And in here, either you can create a brand new module or you can add the function on the
03:48same module yourself.
03:50I'm going to make it simple and I'm going to add a new module so it's clearer.
03:53And then after that, you're going to paste this function.
03:55Again, if you like a copy of this function, please leave a request on the comment section and
03:59I'll send you this function here.
04:01So basically dissecting this function a bit here.
04:03The first two rows is basically defining variable for checkbox and the range.
04:08And after that, I'm doing the same thing here.
04:10You can see the selection is assigned to the range so that the row and the column can be
04:13start on this variable here.
04:15And after that, I'm going through the for loop, going through each cell that I've selected
04:19on the workbook.
04:20And then if the cell on that particular intersection is not blank, as in there's nothing in it,
04:26then you're going to go ahead and delete the checkbox that you have found going through
04:31each of the cells.
04:31If it's not there, of course, it's not going to do anything at all.
04:34So over here, I'm going to say control S to save it.
04:38And then after that, close the VBA editor here.
04:40And then now we're going to go back to developer code and macro.
04:43And you can see that your checkbox function is available here.
04:46Select that and go options.
04:48And like before, you can assign a hotkey for it.
04:51I'm going to assign control R as a hotkey.
04:54But if you like control shift R, you're going to press shift R. Let's do that for now, I
04:58guess.
04:58I'm going to do shift R like this and then click on OK and then cancel.
05:02Now if you go ahead and select the cells that you want the checkbox to be removed like this,
05:07and you press control shift R, you can see that the checkbox get removed.
05:11I hope this shot letter tape will make your life a whole lot easier.
Comments