I have made a "fake checkbox "with Wingdings char - 254 and 168
However, i would also like to make a master check box, to do "select all or unselect all"
How can i do it ?
ThanksPlease Login or Register to view this content.
Eric
I have made a "fake checkbox "with Wingdings char - 254 and 168
However, i would also like to make a master check box, to do "select all or unselect all"
How can i do it ?
ThanksPlease Login or Register to view this content.
Eric
Last edited by Eric Tsang; 10-12-2019 at 12:13 PM. Reason: forget the [code]
Dear all
I tried the work around way to use If function but it does not apply to the column,
may be i do need code. Appreciate any help
You will need to edit your op to include code tags, before anyone can help you. Please see post#2
Like this
Please Login or Register to view this content.
Yes but you need to add the code tags to your original post.
Dear all
I hope i get it right
I have made a "fake checkbox "with Wingdings char - 254 and 168
However, i would also like to make a master check box, to do "select all or unselect all"
How can i do it ?
ThanksPlease Login or Register to view this content.
Eric
Please edit your original post (ie post#1) to include the code tags.
Once you have done that I can help you, but not before.
Thanks
Dear Fluff13
I hope i did i right
Eric
That's fine, thanks.
How aboutDouble click in A2 to clear the checkboxes.Please Login or Register to view this content.
Dear Fluff
I replaced the code with yours
it does not work
Fluff
I tried to click A2, it prompts see the attached
Please help
Dear Fluff
What i really want is when user click the check box at A2
The check box (in fact is char 254/168) will change , if it is A2 is true, the target cell, will change to char254 if not remain unchange
Use can still be able to use the "double click"function
Eric
If you are using wingdings for your checkboxes, why put a real checkbox in A2? Rather than just double click A2, the same way you are doing for the rest of the column?
Rick
The checkbox is a real one.
The rest are fake , it special symbol- Char 254 /168.
Hence, my first attamp is to use the following if function in the cell from A4 onward.
However, it works but the double click to change bwt char is lost
=if(a2=true, char(254),char(168)
Thanks for helping me
Not sure if you saw post#15 as we posted at the same time.
But with the file I supplied, just doubleclick A2
Fluff
it does not work
It usus thi method, as some of the user, they do not know adding the checkbox and link to cell.
It would be better if they just copy and past when they need in column 1.It seems much easier than tell user to add the checkbox.
as somethime the list would be long and need extra check box.
Eric
Dear Fluff and Rick
Does it make sense in the VBA
if the checkbox 1 ( at the a2) is true
then the A4:A9 cells turn to Char(254)
When if the checkbox is false
the double click code valid?
Sorry to tell you that i know nothing about VBA, i just follow the Youtube and copy and past
Have you tried the sample file I supplied? All they need to do is double click A2 & it resets the "checks"
Give this a try...
Add a general Module (not the Class Module you have now) and put this code in it...
Then change you BeforeDoubleClick code to this...Please Login or Register to view this content.
Please Login or Register to view this content.
Last edited by Rick Rothstein; 10-12-2019 at 02:06 PM.
Dear Fluff
thanks a lot and your solution is worked. I did not realised the download file.
As i am a navie of VBA code..
When i click A2 , Range A4:A9 will turn to Char168 , but when i double click again , hoping that all will turn to Char254.
I hope i can get this responses as well.
Also can i add the same char (168 or 254) in A2, so it looks the same as A4:A9
Thanks
Please Login or Register to view this content.
@Eric,
Just wondering if you tried the code I posted in Message #21... it allows you to keep your original setup... a real checkbox named "Check Box 1" on top of cell A2 which places the value TRUE or FALSE in the cell A2 directly. By the way, with that setup, you might consider making the font color in cell A2 white to match the cell's background color so that the words TRUE and FALSE do not visibly display.
Last edited by Rick Rothstein; 10-13-2019 at 12:48 AM.
Dear Rick
I do not understand Post 21
See what i made in the file
You had the CheckBox point to the wrong code procedure. Here is the file with it corrected.
Dear Rick
I have assigned the correct Marco to the checkbox.
It works. However, it would be perfect if they can detect the last cell with the char
Next.
What does it mean -
"Target.Value = Chr(422 - Asc(Target))"
"Range("A4:A9").Value = Chr(168 - 86 * [A2])"
Can share with me ?
I do appreciatedPlease Login or Register to view this content.
There are a few things packed into those two code lines.
Let's start with the first code line. It is a toggle between the checked Wingding character and the unchecked Wingding character. The 422 is the sum of the ASCII values (168 and 254) for those two characters. If you subtract the current ASCII value for the Target (it has to be either 168 or 254) from the sum of the two numbers (422), you get the other number's ASCII value. The Asc function takes a text character and returns its ASCII value while the Chr function takes an ASCII value and returns the text character for that ASCII value.
Now, for the second code line. This line is also a toggle, but it uses cell A2's value (TRUE or FALSE) to decide which character to place in cells A4 through A9. You may not be familiar with the square bracket notation... it is a shortcut for the Evaluate function (which evaluates Excel formulas where the equal sign is not required) and can be used when nothing in the formula is variable. Since we want the value of cell A2 and only cell A2, we can use the square bracket notation to retrieve it. [A2] is equivalent to the Excel formula =A2. As for the constants in the code line... 168 is ASCII code for one of the Wingding characters and 86 is how much you have to add to 168 to get 254 (the other Wingding character's ASCII value). If cell A2 is FALSE, VBA will treat that as 0 and the expression 168-86*[A2] will evaluate to 168. If, on the other hand, cell A2 is TRUE, VBA will treat that as -1 (minus one). Note this is the opposite of what TRUE evaluates to numerically in a worksheet. That is why there is a minus sign in front of the 86... to change the minus value resulting in multiplying 86 times -1 to a positive value so that the 86 gets added to the 168 to yield 254 (the ASCII value for the other Wingding character). The Chr function converts the ASCII value to the appropriate Wingding character.
I hope that all made sense to you... I find it much easier to conceive coding in my mind than to write it all out in words.
I do not understand what you mean by this. Can you explain it in more detail please?
Rick
Your explaination is so great. However, i am not at that level to understand at the moment but appreciated
Now i know what the code is trying to do.
It works. However, it would be perfect if they can detect the last cell with the char
Next.
It means, possible to set the range as a:a instead of A4:A9. As it will let the user to copy and paste the "char168 "to next cell.
Thanks
A simple example that may help. Let's say X=168 and you want to toggle X between 168 and 254. This code line does it...
X = (168 + 254) - X
If X = 168, then (168 + 254) - 168 gives 254 (I am hoping that is obvious).
If X = 254, then (168 + 254) - 254 gives 168 (Again, I am hoping that is obvious).
Instead of leaving (168 + 254), let complete the addition which yield 422. So, the first code line above becomes this...
X =422 - X
And as long as X equals 168 or 254 to begin with, executing that line of code will toggle X between 168 and 254 repeatedly. The only thing additional in my code was a function (Asc) to change a character to its ASCII value and another function (Chr) to change ASCII values back to text characters. The other code line's toggle is slightly different, but I think if you read my explanation a few times, it might start to make more sense.
As for your request, just change the A4:A9 to A:A in both procedures.
Dear Rick
Thanks a lot , very clear
I tried to change the Range A:A but it change the whole column and does not starts or ends on the last record.
If you can give me one more advice will be great or i think i will keep the pervious version
Eric
How aboutPlease Login or Register to view this content.
Fluff
it is not working
In what way?
Dear Fluff13
See the attached
I replaced the code and the A2 ,when i click, not working
Eric
It works for me if you double click A2, try removing the checkbox & then double click A2
Maybe this is what you want (replace each existing procedure with these)...
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks