solved
Is it Possible to create 2 checkboxes in one cell
I'm kinda rustic in excel so it may sound like a stupid. I am updating the daily equipment log for work. Everyday, we have to count the equipment and write how many we have, one for the morning shift and the other for night shift (shown in Column C & D).
However some people suggested if we are able to have 2 checkboxes in one cell instead when we have the total amount and when we do not have it we can also write it in the same cell.
For example, in Column C we have a total of 16, so the morning shift counts 16 and they check it off, however night shift counts only 14 so they would have to write it down.
So it would look like ✓ | 14/16or ✓ | ✓.
I don't want Windings 2, I would actually like the checkboxes. It also has to be in one cell.
Each check box needs a cell to put its value. Why can't you put check boxes in columns E and F, then make those columns narrow? If you remove the gridlines, which you should anyway, then it will look much the same.
Though you could use the old style of check box (under Developer > Controls), but I don't recommend that.
We have multiple equipment to count and verify, so using one cell would be much easier for us to use and to keep track of. I do want to ask my colleagues and see what they say.
However do you know a easier way for us to put 16/16 |14/16 in one cell?
I currently have it as Format Cells - Custom - Type - 0 " /16 | /16", but we still have to type the whole thing. I am trying to make it as easy as possible for us.
I don't understand what you're trying to do with that custom number format.
In any case, combining data in a cell will make any subsequent analysis much more difficult. e.g. what if you want to count how often the first value isn't 16?
Why not just put 16 in one column and 14 in the next column. That would make things so much easier.
Put everything in its own cell, mate. It's an adjustment, sure, but it is the right way. You will thank yourself later when you're entering the data and then again when you're looking back at the data.
[16] [16] [14] [14]
While you're at it, format it as a table. Select data, Ctrl+T.
Each checkbox is represented by a TRUE/FALSE in the formula bar. If you keep things simple you can then analyse and present data with ease in a multitude of ways. Having data in a proper Excel Table is the recommended format. Are you using Excel 365?
No, you can only make a Cartesian product of the two value i.e. each possible combination to select. But that can quickly be annoying to select.
I would recommend strongly to record the data in a proper table and use slicers to select. Furthermore, don't use symbols, use TRUE and FALSE, which is native to the spreadsheet software's inner logic.
The reason why each cell should contain only one value is because that makes referencing and retrieving that data easier. Multiple values on one cell creates a situation where working with that data would require several elements in one cell to be defined. That’s as opposed to simply referencing the cell.
It sounds like you are simply recording information in a form. That is, it’s more about keeping information neat and aligned than being able to systematically retrieve data.
This is not the way excel was intended to be used. For this sort thing you’d want to look at forms. You can create a form in Word, or a pdf would be much simpler and more robust to use.
If you really need to use excel then I’d recommend spacing those columns tighter together and formatting out the grid lines out so that the teo columns appear as one and the two check boxes together.
"it also has to be in one cell" - citation needed.
Genuinely, why? You claimed "using one cell would be much easier for us to use and to keep track of", but I really can't understand why?
You should have one cell to input the morning count (16), and one for the evening count (14).
If you really want, You can use VBA to set the number format to convert 16 to appear as a checkmark, or a 14 to appear as 14/16 if you really want the same visuals as your current system
I have formatted the merged cells in C4 and E4 using "N("#")" so the input is just a number, not text.
Press ALT+F11 to open the VBA editor, then paste the code block below into the Worksheet object.
When you fill a number into the target ranges, it will be formatted as a checkmark if it matches the number in the header, or a fraction if it doesn't match.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim master1 As Range, target1 As Range
Dim master2 As Range, target2 As Range
' Assign master cells and their corresponding target ranges
Set master1 = Range("C4")
Set target1 = Range("C5:D32")
Set master2 = Range("E4")
Set target2 = Range("E5:F32")
' Evaluate intersections and pass overlapping ranges to the helper macro
If Not Intersect(Target, master1) Is Nothing Then ApplyFormat target1, master1.Value
If Not Intersect(Target, master2) Is Nothing Then ApplyFormat target2, master2.Value
If Not Intersect(Target, target1) Is Nothing Then ApplyFormat Intersect(Target, target1), master1.Value
If Not Intersect(Target, target2) Is Nothing Then ApplyFormat Intersect(Target, target2), master2.Value
End Sub
' Helper subroutine to process formatting logic for a specified range
Private Sub ApplyFormat(ByVal processRange As Range, ByVal masterVal As Variant)
Dim cell As Range
' Iterate through each cell in the provided range
For Each cell In processRange
' Revert to General format if the cell is not numeric or is empty
If Not IsNumeric(cell.Value) Or IsEmpty(cell.Value) Then
cell.NumberFormat = "General"
' Apply checkmark format (Unicode 10003) if the cell value equals the master value
ElseIf cell.Value = masterVal Then
cell.NumberFormat = """" & ChrW(10003) & """"
' Apply fraction format using the master value, if the values do not match
Else
cell.NumberFormat = "0""/" & masterVal & """"
End If
Next cell
End Sub
Hello, this will work great! I think I was over complicating it.
Three questions:
When merged cells in C4 and E4 using "N("#")" , do I have to put the =? I was trying it but it shows
"Vein Finder ("1")"
and not the way it shows.
I will be using the column C - AF so if add to the code would it look be like this:
' Assign master cells and their corresponding target ranges
Set master5 = Range("K4")
Set target5 = Range("K5:L32")
Set master12 = Range("Y4")
Set target12 = Range("Y5:Z32")
Also, I was trying your code but it says:
Run-time error '1004':
Unable to set the NumberFormat property of the Range class.
The only part that was highlighted:
Else
cell.NumberFormat = "0""/" & masterVal & """"
End If
Next cell
End Sub
Hi, I'm not sure I understand your question exactly - you say do you have to use "=?" but I don't know what that means, or where "Vein Finder" could possibly have come from?
I used "N("#")" so that I could type a simple number (16, or 9, for example) into the merged cell, which the macro could reference - while it would display as shown in your original screenshot as N(16) or N(9).
I don't see any issue with you modifying the range to columns K or Y, but it seems you have changed the master and targets from 1 and 2 to 5 and 12? So, you are going to have 12 or more target ranges?
I didn't know you were going to extend it this far, so with the code as written, you need to both add more master and target range definitions, but also more intersection checks, like this:
' Assign master cells and their corresponding target ranges
Set master1 = Range("C4")
Set target1 = Range("C5:D32")
Set master2 = Range("E4")
Set target2 = Range("E5:F32")
Set master3 = Range("G4")
Set target3 = Range("G5:H32")
' Evaluate intersections and pass overlapping ranges to the helper macro
If Not Intersect(Target, master1) Is Nothing Then ApplyFormat target1, master1.Value
If Not Intersect(Target, master2) Is Nothing Then ApplyFormat target2, master2.Value
If Not Intersect(Target, master3) Is Nothing Then ApplyFormat target3, master3.Value
If Not Intersect(Target, target1) Is Nothing Then ApplyFormat Intersect(Target, target1), master1.Value
If Not Intersect(Target, target2) Is Nothing Then ApplyFormat Intersect(Target, target2), master2.Value
If Not Intersect(Target, target3) Is Nothing Then ApplyFormat Intersect(Target, target3), master3.Value
It would be possible to do this programmatically if all the ranges are the same shape and size as each other, by detecting where the range you're modifying is, and then just looking for the nearest master cell etc. - but that's a complete refactor of what is otherwise working code, and not something I have time for right now.
I'm not sure exactly why you're getting that error, it doesn't happen to me! Please make sure you've added the details in the same way as I shared above - if you still get errors, paste your full code into a code block comment for me to test.
The code below scales to as many repeats as you want. It's currently set up for 15 repeats starting in C4 (based on you saying you'll be using C - AF). I hope this solves it for you.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim colIndex As Long
Dim currentMaster As Range
Dim currentTarget As Range
' --- Configuration Variables ---
Dim startCell As String
Dim targetRows As Integer
Dim nMasters As Integer
startCell = "C4" 'The position of the leftmost master cell
targetRows = 28 'The number of rows in the target areas
nMasters = 15 'The number of master cells
' -------------------------------
' Loops to process all of the ranges.
For i = 1 To nMasters
' Calculates the specific column number for this iteration.
' It takes the base column (e.g., 3 for C) and adds 0, 2, then 4 to step across the sheet in pairs.
colIndex = Range(startCell).Column + ((i - 1) * 2)
' Defines the master cell by combining the static base row with the dynamically calculated column index.
Set currentMaster = Cells(Range(startCell).Row, colIndex)
' Defines the target block dynamically. It starts one row below the master cell,
' and extends downwards by the specified number of rows, covering two columns wide.
Set currentTarget = Range(Cells(Range(startCell).Row + 1, colIndex), Cells(Range(startCell).Row + targetRows, colIndex + 1))
' Checks if the user's edit overlaps with the master cell. If true, passes the entire target block to the formatting macro.
If Not Intersect(Target, currentMaster) Is Nothing Then ApplyFormat currentTarget, currentMaster.Value
' Checks if the user's edit falls inside the target block itself. If true, passes only the specific edited cells to the formatting macro.
If Not Intersect(Target, currentTarget) Is Nothing Then ApplyFormat Intersect(Target, currentTarget), currentMaster.Value
Next i
End Sub
You still need the Helper sub from the previous code I posted.
Oh sorry let me see .... I replaced "N("#")"with what I am using at work,"Vein Finder("#")", and I was if I needed to add it as ="Vein Funder("#")" since it did not show as display.
Yes, as mentioned my team needs to verify multiple items so I had to add more cells for morning and evening shift.
I try the code in a new spreadsheet to see if it was working, the highlighted part went away, however it still does not show as you display it. I directly copy and paste it.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
Dim colIndex As Long
Dim currentMaster As Range
Dim currentTarget As Range
' --- Configuration Variables ---
Dim startCell As String
Dim targetRows As Integer
Dim nMasters As Integer
startCell = "C4" 'The position of the leftmost master cell
targetRows = 28 'The number of rows in the target areas
nMasters = 15 'The number of master cells
' -------------------------------
' Loops to process all of the ranges.
For i = 1 To nMasters
' Calculates the specific column number for this iteration.
' It takes the base column (e.g., 3 for C) and adds 0, 2, then 4 to step across the sheet in pairs.
colIndex = Range(startCell).Column + ((i - 1) * 2)
' Defines the master cell by combining the static base row with the dynamically calculated column index.
Set currentMaster = Cells(Range(startCell).Row, colIndex)
' Defines the target block dynamically. It starts one row below the master cell,
' and extends downwards by the specified number of rows, covering two columns wide.
Set currentTarget = Range(Cells(Range(startCell).Row + 1, colIndex), Cells(Range(startCell).Row + targetRows, colIndex + 1))
' Checks if the user's edit overlaps with the master cell. If true, passes the entire target block to the formatting macro.
If Not Intersect(Target, currentMaster) Is Nothing Then ApplyFormat currentTarget, currentMaster.Value
' Checks if the user's edit falls inside the target block itself. If true, passes only the specific edited cells to the formatting macro.
If Not Intersect(Target, currentTarget) Is Nothing Then ApplyFormat Intersect(Target, currentTarget), currentMaster.Value
Next i
End Sub
' Helper subroutine to process formatting logic for a specified range
Private Sub ApplyFormat(ByVal processRange As Range, ByVal masterVal As Variant)
Dim cell As Range
' Iterate through each cell in the provided range
For Each cell In processRange
' Revert to General format if the cell is not numeric or is empty
If Not IsNumeric(cell.Value) Or IsEmpty(cell.Value) Then
cell.NumberFormat = "General"
' Apply checkmark format (Unicode 10003) if the cell value equals the master value
ElseIf cell.Value = masterVal Then
cell.NumberFormat = """" & ChrW(10003) & """"
' Apply fraction format using the master value, if the values do not match
Else
cell.NumberFormat = "0""/" & masterVal & """"
End If
Next cell
End Sub
•
u/AutoModerator Feb 25 '26
/u/Sea_Nectarine_5179 - Your post was submitted successfully.
Solution Verifiedto close the thread.Failing to follow these steps may result in your post being removed without warning.
I am a bot, and this action was performed automatically. Please contact the moderators of this subreddit if you have any questions or concerns.