+ Reply to Thread
Results 1 to 35 of 35

How to use the if function for changing the Font of Windings

  1. #1
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    How to use the if function for changing the Font of Windings

    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 ?


    Please Login or Register  to view this content.
    Thanks
    Eric
    Attached Images Attached Images
    Last edited by Eric Tsang; 10-12-2019 at 12:13 PM. Reason: forget the [code]

  2. #2
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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
    Attached Images Attached Images
    Attached Files Attached Files

  3. #3
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    You will need to edit your op to include code tags, before anyone can help you. Please see post#2

  4. #4
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    Like this


    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    Yes but you need to add the code tags to your original post.

  6. #6
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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 ?


    Please Login or Register  to view this content.
    Thanks
    Eric

  7. #7
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    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

  8. #8
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    Dear Fluff13

    I hope i did i right

    Eric

  9. #9
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    That's fine, thanks.
    How about
    Please Login or Register  to view this content.
    Double click in A2 to clear the checkboxes.

  10. #10
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    Dear Fluff

    I replaced the code with yours

    it does not work

  11. #11
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    Fluff

    I tried to click A2, it prompts see the attached

    Please help
    Attached Images Attached Images

  12. #12
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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

  13. #13
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: How to use the if function for changing the Font of Windings

    Quote Originally Posted by Eric Tsang View Post
    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
    Is the checkbox in cell A2 a real checkbox (if so, is it a Form or ActiveX checkbox) or is it one of your fake checkboxes (if so, is it being activated by selecting the cell or double clicking it)?

  14. #14
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    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?
    Attached Files Attached Files

  15. #15
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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

  16. #16
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    Not sure if you saw post#15 as we posted at the same time.
    But with the file I supplied, just doubleclick A2

  17. #17
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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
    Attached Images Attached Images
    Attached Files Attached Files

  18. #18
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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

  19. #19
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    Have you tried the sample file I supplied? All they need to do is double click A2 & it resets the "checks"

  20. #20
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: How to use the if function for changing the Font of Windings

    Quote Originally Posted by Eric Tsang View Post
    if the checkbox 1 ( at the a2) is true
    then the A4:A9 cells turn to Char(254)

    When if the checkbox is false
    Give this a try...

    Add a general Module (not the Class Module you have now) and put this code in it...
    Please Login or Register  to view this content.
    Then change you BeforeDoubleClick code to this...
    Please Login or Register  to view this content.
    Last edited by Rick Rothstein; 10-12-2019 at 02:06 PM.

  21. #21
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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.

  22. #22
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: How to use the if function for changing the Font of Windings

    @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.

  23. #23
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    Dear Rick

    I do not understand Post 21

    See what i made in the file
    Attached Files Attached Files

  24. #24
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: How to use the if function for changing the Font of Windings

    You had the CheckBox point to the wrong code procedure. Here is the file with it corrected.
    Attached Files Attached Files

  25. #25
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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 ?

    Please Login or Register  to view this content.
    I do appreciated

  26. #26
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: How to use the if function for changing the Font of Windings

    Quote Originally Posted by Eric Tsang View Post
    What does it mean -

    "Target.Value = Chr(422 - Asc(Target))"
    "Range("A4:A9").Value = Chr(168 - 86 * [A2])"
    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.



    Quote Originally Posted by Eric Tsang View Post
    It works. However, it would be perfect if they can detect the last cell with the char
    Next.
    I do not understand what you mean by this. Can you explain it in more detail please?

  27. #27
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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

  28. #28
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: How to use the if function for changing the Font of Windings

    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.

  29. #29
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    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

  30. #30
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    How about
    Please Login or Register  to view this content.

  31. #31
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    Fluff

    it is not working

  32. #32
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    In what way?

  33. #33
    Forum Contributor
    Join Date
    12-06-2015
    Location
    Hong Kong
    MS-Off Ver
    Office 365
    Posts
    509

    Re: How to use the if function for changing the Font of Windings

    Dear Fluff13

    See the attached



    I replaced the code and the A2 ,when i click, not working

    Eric
    Attached Files Attached Files

  34. #34
    Forum Guru
    Join Date
    09-10-2017
    Location
    Chippenham, England
    MS-Off Ver
    365
    Posts
    15,027

    Re: How to use the if function for changing the Font of Windings

    It works for me if you double click A2, try removing the checkbox & then double click A2

  35. #35
    Forum Guru
    Join Date
    04-23-2012
    Location
    New Jersey, USA
    MS-Off Ver
    Excel 365
    Posts
    2,401

    Re: How to use the if function for changing the Font of Windings

    Quote Originally Posted by Eric Tsang View Post
    Dear Rick

    I tried to change the Range A:A but it change the whole column and does not starts or ends on the last record.
    Maybe this is what you want (replace each existing procedure with these)...
    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Changing Font with Function
    By Dan McCollick in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-10-2005, 01:05 PM
  2. [SOLVED] changing font style in a complex worksheet function
    By gvm in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 03:05 PM
  3. [SOLVED] changing font style in a complex worksheet function
    By gvm in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 PM
  4. changing font style in a complex worksheet function
    By Rowan in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 09-06-2005, 07:05 AM
  5. changing font style in a complex worksheet function
    By Rowan in forum Excel Formulas & Functions
    Replies: 18
    Last Post: 09-06-2005, 04:05 AM
  6. changing font style in a complex worksheet function
    By gvm in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 01:05 AM
  7. [SOLVED] changing font style in a complex worksheet function
    By gvm in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-06-2005, 12:05 AM
  8. [SOLVED] changing font style in a complex worksheet function
    By gvm in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-02-2005, 09:05 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1