none
Numberformat RRS feed

  • Question

  • Hello

    I have a problem in Vb with Numberformat.

    I want the value (text : intials with max 3 letters) in a activecell convert to the numberformat but it doesn't work

    Is it possible?

    Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim cel As String
    cel = ActiveCell.Value
    If ActiveCell.Value <> "" And ActiveCell.Value <> 1 Then
    ActiveCell.NumberFormat = cel
    ActiveCell.Value = 1
    Cancel = True
    End If
    End Sub

    Many thanks for the respons.

    Johan


    • Moved by KareninstructorMVP Saturday, July 2, 2016 8:52 AM Moved from VB.NET
    • Edited by simgill Saturday, July 2, 2016 1:08 PM
    Saturday, July 2, 2016 5:49 AM

Answers

  • Hi Edwards

    I search for another solution.

    I tought it was easy in visual basic but it isn't.

    It 's the only way to count different cells with 3 caracters in a matrix.

    Thanks for your time.

    Johan

    Wednesday, July 6, 2016 2:59 PM

All replies

  • Hi Johan,

    >> I want the value (text : intials with max 3 letters) in a activecell convert to the numberformat but it doesn't work

    What is the value for ActiveCell? It would be helpful if you could share us your value in a ActiveCell and the expected value you want. The value in ActiveCell should be a valid NumberFormat. For creating a custom number format, you could refer the link below:

    # Create or delete a custom number format

    https://support.office.com/en-us/article/Create-or-delete-a-custom-number-format-78f2a361-936b-4c03-8772-09fab54be7f4?ui=en-US&rs=en-US&ad=US#bm2

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Monday, July 4, 2016 3:16 AM
  • Hi Edward

    Thanks for the quick respons.

    The value in the activecell before PROCEDURE Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean) is constantly a text with 3 characters (initials) 

     for exemple "JVK" or "BVE" ....

    the expected value in de activecell afterwards is a number for exemple 1

    Many thanks.

    NOTE:

    It 's strange that the procedure works with the letters "JVK" but not with "BVE" ...

    Johan

    <sub></sub><sup></sup><strike></strike>


    • Edited by simgill Monday, July 4, 2016 4:29 PM
    Monday, July 4, 2016 3:14 PM
  • Hi Johan,

    I am not sure whether this format like “BVE” is supported in Excel, could you share us how do you know how to use with this? I made a test with this format in Excel 2013, and it is not a support format. And I made a test with “JVK”, it only shows “JVK” in cell, I don’t know the use of “JVK”. Could you share us more information about this “JVK” and “BVE”?

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Tuesday, July 5, 2016 6:12 AM
  • Try it:

    If (ActiveCell.Value <> "" And ActiveCell.Value <> 1) or ActiveCell.Value = "BVE" Then

    Tuesday, July 5, 2016 6:38 AM
  • Hi Edward

    Apparently it isn't so easy then I a thought ;-)

    I have a large matrix (schedule) in excel with diverent names (random text of MAX 3 initials /cell) when I click with the Right moussebutton on the activecell I wants that the content (text) of the activecell confert to NUMBER format.

    Exemple: when I click (procedure Worksheet_BeforeRightClick) on activecell "ABC" I want the letters "ABC" as numberformat so when I fill in afterwards a number ( for exemple number one ) I see ABC in the activecell but number one in the formulabalk (I add a screenshot)

    Many Thanks

    Johan


    • Edited by simgill Tuesday, July 5, 2016 5:02 PM
    Tuesday, July 5, 2016 4:57 PM
  • Sorry

    I want to add a screenshot but it doesn't allowed (messagebox ... until we able to verify your account?)


    • Edited by simgill Tuesday, July 5, 2016 5:08 PM
    Tuesday, July 5, 2016 5:07 PM
  • Hi simgill,

    For uploading screen shot, you could upload it to oneDrive, and share us link here.

    I am wondering the meaning of setting number format with ABC. With manually setting format with ABC, if I enter 1 in the cell, it will output A43C, is it your expected result? Before using code to format, I would suggest you try to manually achieve this to check whether it is your expected result.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Wednesday, July 6, 2016 6:30 AM
  • Hi Edwards

    I search for another solution.

    I tought it was easy in visual basic but it isn't.

    It 's the only way to count different cells with 3 caracters in a matrix.

    Thanks for your time.

    Johan

    Wednesday, July 6, 2016 2:59 PM
  • Hi Johan,

    Do you mean your issue has been resolved? If so, I would suggest you mark your reply as answer to close this thread. If not, please feel free to share more information about your issue.

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Thursday, July 7, 2016 6:11 AM