Double Click to add/remove Tick

A simple method to add a tick to a cell instead of using controls. Double click to add tick and double click to remove tick.

Please Login or Register  to view this content.

Hey Smuzoen.
Would you also not need something like this in the code?
Of coarse you would need to replace the font with what ever your PC is using.

Please Login or Register  to view this content.

No. I have used if for example in a column to mark a row rather than having stacks of form controls and then coding each of the controls e.g checkbox. Place it in a worksheet and see how it works. The idea is to double click to place a tick and double click again to remove. It is easier then to find value of cell than coding a control testing it for true/false. Hope that makes sense.

Hi,

I still think JapanDave makes a valid point since if anything else happens to be entered in a column F cell which has previously been ticked and unticked there will be an unusual character.

JD's addition is a useful addition in what I assume is meant to be a generalised macro for others rather than being specific just for you.

I see your point however the code it is only for a set range and not all of Column F which would be designated as requiring a tick or not a tick. If you want to allow the user to enter data into that range then I would use JD's suggestion however if you require a Tick or No Tick then I would not use the change otherwise users will be able to enter data. The reason I find it useful is that it saves having a stack of checkbox controls which need to be coded.

Yes, love this method and was first introduced to it here

Example workbook attached at this site.

remarkable coincidence that it's nearly the same as this

Please Login or Register  to view this content.

that i first saw at ozgrid
http://www.ozgrid.com/VBA/excel-checkbox-tick-cell.htm

Hi,
I'm a little late to this thread but I wrote something like this a long time ago. Find it attached. I believe you can change the "x" to any character you like.

Now that I read my own code, I see I didn't know "Intersect" back in March 2011.

There's been variations of this posted fo years. I have used WingDings & Marlett.

I humbly apologise - In the rules for the submitted tips it did not say the work had to be original - I was just submitting tips that I have come across that I have found useful in my day to day use of VBA - In retrospect I should have given credit to where I found the code but to tell you the truth I cannot remember where I came across this code all I know is I saved the file in December 2010. I file away anything I find useful in a folder and I went through that folder looking at what I had accumulated over time and I thought this was a pretty cool trick. In retrospect I should have said this is not my work it is just some code I had come across in my travels.
I am sorry if I mislead anyone .... it was never my intention to submit this implying it is original work so I apologise if I have offended anyone.
I like most of you I am sure, hate cut and paste coders and I do not regard myself as such however I will use tried and true techniques that others have developed like we all do when extending classes.
I am more than happy for this thread to be removed if you see fit. I do not want my reputation tarnished. Again my humble apologies.

I don't think you need to apologize because it was discussed early on that the competition was not for 'novel' ideas but rather what anyone thought was a cool or neat trick. In the thread I submitted, I tried to provide links where I knew they existed but even the only 'novel' idea that came to my mind (last tip) I duly noted that someone else might have thought of this previously. It doesn't mean that's not a neat twist to a common excel function. We're all here to share ideas and there should be no penalties for that.

@ Anthony

I echo abousetta's comments.

No apology is necessary. The rules make no mention of tips needing to be original, and quite rightly too.

In respect to the last comments, agreed, and my post was only because there is a workbook for download which can be beneficial to the VBA newcomer.

@Jeff - I had no problems with comments such as yours. There was one comment however that I thought was completely unwarranted. If we are to be a altruistic community which I have always found Excel Forum to be then I think we can do without the sarcasm.

...and now we know how this really wasn't a great idea.. this competition.... why not just make it a place where people can post tips and tricks that others may find useful... why make it a "competition". Nothing ever seems to go right here, does it?

Originally Posted by NBVC

...and now we know how this really wasn't a great idea.. this competition.... why not just make it a place where people can post tips and tricks that others may find useful... why make it a "competition". Nothing ever seems to go right here, does it?

...or even a place where before the ink is hardly dry on the first postings there aren't any sour grapes negative comments.

I post on a few forums but now I mainly post here as I found the ego's on the other sites too much to bear, was sick of being over posted with overly complicated solutions that did nothing to educate the OP's and the general tone of the OP's was not very thankful. I must admit in all fairness the regulars are much friendlier here and the OP's tend to be more thankful. No matter which forum you talk about there is always going to be a few that have a perpetual stick stuck (..somewhere) however on the whole I have found the regulars here that post solutions to have much less of an ego than compared to some other sites. On the whole I find Excel Forum a friendly place. I just wish that some contributors would keep their egos in check. Helping people out I think is an altruistic exercise, not one to display prowess and build egos however I guess some of us have different reasons for posting solutions.
With respect to the competition, I must admit this experience does not encourage me to contribute again if I am going to be accused of plagiarism and cut and paste coding. That is not sour grapes, it is simply that I do not want to get into confrontational situations where I have to explain myself. I do this because I enjoy coding, I see this as my volunteer work and my reasons are as I said purely altruistic. Finally I also get to learn from some really great people. Hardly a day goes by where I do not learn something new from you guys.
For those of you that know everything you in fact know nothing.

to be fair,it's not you that's being criticised. it's just this whole competition idea is a farce. there are many solutions to things that maybe i/you and others take for granted,but seem a brilliant revelation to others. I learn things all the time and this bit of code is quite useful.but imo it boils down to "have you seen this before? " and to be realistic most of us have. It doesn't make it an invalid tip and it's good to remind people it exists or introduce people to it. see my post
http://www.excelforum.com/july-compe...lls-trick.html
there you go a blinding tip but i bet you knew it already

Hi Martin
I see you point - I guess I was not aiming at the right audience. However there is always the inherent problem with forums/emails/SMS and that the intended tone of the author and the tone interpreted by the reader may be quite different. When you said

remarkable coincidence that it's nearly the same as this

I interpreted this as a dig at my integrity that I just Googled for something to enter into the competition. I have pretty thick skin but two things I value above all else is my integrity and my reputation.
I think what may be worthwhile is creating a Categorised Tip List - this could have different lists with code pertinent to the category - perhaps this could reduce recurring questions like "How do I merge all worksheets into one sheet" - we all know that the same questions get asked over and over and if people were encouraged to search the forums I would guess that 6/10 questions could be answered by OP's themselves if they just took the time to search. After all OP's learn nothing if they are spoon fed.
Anyway I think this is the last comment I am going to make in this thread as perhaps this is something that would be best discussed around the Water Cooler. No hard feelings.

I think the idea of competitions are not bad, but need to be more structured. Rather than, give me "the best you got", and we will wade through the salad of responses there should be a target. For example I have seen competitions asking people to provide the best solutions for a specific situation. For example:

1) Using only formulas and graphs, create a dashboard that uses powerpivot and Excel's built-in functions for data being streamed live from xyz.com

2) Using vba only, create a game in Excel

etc.

Something creative, not easily unearthed with a Google search. That way, it will test the minds and abilities of the pros who contribute here and give us less knowledgeable people inspiration on the power behind Excel and ideas for incorporation.

Adding to Smuzoen's tip, I've collected "Checkbox" ideas over the years in a single workbook.

Sheet 1 uses VBA and symbol font
Sheet2 uses VBA and marlett
Sheet3 is VBA driven mutually exclusive checkboxes
Sheet4 uses no VBA and will put a check no matter what you enter
Sheet5 talks briefly about Form and activeX checkboxes

I may have modified some of it and added my comments but all the info was from this forum.
Enjoy.

Originally Posted by abousetta

I think the idea of competitions are not bad, but need to be more structured. Rather than, give me "the best you got", and we will wade through the salad of responses there should be a target.

What an excellent idea.

Arlette, Vai - are you up for this for the August competition? Time is short but this seems a much more useful approach.

Richard,

I had mentioned in the guidelines for the competition that for the month of July, we were not providing any topic for the tips.

But for the next month, there would be a specific topic given.

I think abousetta's point is that not that a topic be prescribed, but rather a specific task.

Subtle difference maybe but extremely important. A topic is too wide ranging, a task focuses the mind.

Regards

Yes surely Richard. I get your point absolutely.

Lets see what best we can do in the short time we have.