In Excel 2007, how do I use mini-circles for marking a selection?
Would it be possible with help of VBA to create this function?:
When I click inside an empty mini-circle, the cell becomes selected and the mini-circle becomes filled. The column width is only 1, so the mini-circle must fit into a cell with only 1 in column width.
Then when I click again inside this mini-circle (which now is filled), the cell should be un-selected and the mini-circle empty again.
This is the layout of mini-circle I was thinking of:
http://blog.contextures.com/archives/2014/02/25/create-colored-harvey-balls-in-excel/
When I click inside an empty mini-circle, the cell becomes selected and the mini-circle becomes filled. The column width is only 1, so the mini-circle must fit into a cell with only 1 in column width.
Then when I click again inside this mini-circle (which now is filled), the cell should be un-selected and the mini-circle empty again.
This is the layout of mini-circle I was thinking of:
http://blog.contextures.com/archives/2014/02/25/create-colored-harvey-balls-in-excel/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I think it is Radio Buttons that you need. These can be found on the Developer ribbon on the Insert Control option.
A radio button can be linked to a single cell and this will set the value in the cell to TRUE or FALSE.
A radio button can be linked to a single cell and this will set the value in the cell to TRUE or FALSE.
ASKER
Rob, I tried your solution first, but it's a bit inconvenient to insert each of the radio buttons. Probably I can copy each of them, but there are many of them in many different columns. And the text beside the radio button is visible all the time (I don't have room for that, no room for anything but the radio button). When I selected it, the radio button became filled, but then I can't unfill it.
yo_bee, then I tried your solution but I will not input any value, I only need either to mark the circle so it becomes filled or unmark it so it becomes unfilled.
Martin, your solution seems what would suit best in this situation: I would have 3 statuses; nothing, unfilled circle and filled circle. That is perfect, then I can mark a category as preliminary and then set the marking as final by clicking again to get it filled.
Can I just copy your code to my worksheet without risk that it interferes with other SelectionChange codes? And the only thing I need to change in your code is the range?
yo_bee, then I tried your solution but I will not input any value, I only need either to mark the circle so it becomes filled or unmark it so it becomes unfilled.
Martin, your solution seems what would suit best in this situation: I would have 3 statuses; nothing, unfilled circle and filled circle. That is perfect, then I can mark a category as preliminary and then set the marking as final by clicking again to get it filled.
Can I just copy your code to my worksheet without risk that it interferes with other SelectionChange codes? And the only thing I need to change in your code is the range?
The text next to the Radio Button can be deleted but I agree, inserting more than just a few of them can be tedious.
ASKER
Martin, I tried your suggestion now but the circles get misplaced somewhat (because the column width is only 1). So each circle displays on the column line, not inside the cell. Is there any way to modify in the code to display each circle inside the cell?
ASKER
I said there were 3 statuses with your solution Martin, and it was in your Excel. But in my Excel there are only 2 statuses: filled or un-filled. I can't click to remove the display once I have clicked in a cell to display the circle.
ASKER
Please forget about what I said about 3 and 2 statuses, 2 statuses are ok, it works fine for me.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Martin, your solution works perfect now. The only issue that remains is that I want to copy this function to several non-adjacent columns. It worked fine for two non-adjacent columns:
If Not Intersect(ActiveCell, Range("C298:C312", "D298:D312")) Is Nothing Then
But I get an error message when I try to add a third non-adjacent column:
If Not Intersect(ActiveCell, Range("C298:C312", "D298:D312", "C273:C287")) Is Nothing Then
The error message I get when trying to add several non-adjacent columns is this:
"Compile error:
Syntax error"
If Not Intersect(ActiveCell, Range("C298:C312", "D298:D312")) Is Nothing Then
But I get an error message when I try to add a third non-adjacent column:
If Not Intersect(ActiveCell, Range("C298:C312", "D298:D312", "C273:C287")) Is Nothing Then
The error message I get when trying to add several non-adjacent columns is this:
"Compile error:
Syntax error"
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Excellent VBA solution as always from Martin! The function works just like I wanted it. Thanks!
You're welcome and I'm glad I was able to help.
In my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
In my profile you'll find links to some articles I've written that may interest you.
Marty - Microsoft MVP 2009 to 2016
Experts Exchange MVE 2015
Experts Exchange Top Expert Visual Basic Classic 2012 to 2015
Highlight the cells and from the Home Ribbon select Conditional Formatting.
In there you will have an option for ICONS SET.