none
how to make vba and manual entry both in a cell RRS feed

  • Question

  • Hi,

    I want to enter value in a range with vba and manual both.

    Say    

    If Range("B82").Value <> "" Then Range("B88").Value = "investigation report ."   

    If Range("B82").Value = "" Then Range("B88").Value = ""

    Here it clear value in B88 when macro runs. I want it not to clear but retain value , if I enter value manually . If it is empty then it should work.

    Is it possible ? please help.

    regards

    Saturday, October 17, 2015 1:24 AM

All replies

  • I am not sure that I am understanding correctly. I am assuming that the code is in a Worksheet change Event. Is this correct? If so, then please post all of existing code and then list each of the scenarios separately with what you want to occur with the various changes and conditions. 

    Regards, OssieMac

    Saturday, October 17, 2015 5:01 AM
  • Yes, It is in worksheet change event. Other code is not related to this. I only put this code there. It is not sufficient for what I want. I want first line of code. If first line does not meet there. Then I want the second line. I want it to remove only if the first line did it's job earlier. When B88 is empty then I want to type in B88 , which should not be removed by this code "If Range("B82").Value = "" Then Range("B88").Value = "" ". What I type in B88 should be out of this line's command. Is it possible ?

    Thank you

    regards.

    Saturday, October 17, 2015 3:03 PM
  • I am still not sure that I understand. Are you saying that if you manually edit B88 then whatever you type in it should remain unchanged. If so, then try the following.

        If Target.Address = "$B$88" Then Exit Sub
       
        If Range("B82").Value <> "" Then Range("B88").Value = "investigation report ."
       
        If Range("B82").Value = "" Then Range("B88").Value = ""


    Regards, OssieMac

    Saturday, October 17, 2015 8:33 PM
  • Thank you,

    condition one--

        If Range("B82").Value <> "" Then Range("B88").Value = "investigation report ."
       
        If Range("B82").Value = "" Then Range("B88").Value = ""

    condition two--

    when second line is working because of this If Range("B82").Value = "" , in this condition I may need to enter something manually in B88. I want it to remain in cell B88 and should not be cleared by code  " If Range("B82").Value = "" Then Range("B88").Value = "" " when it run next time.

    In other word code line "If Range("B82").Value = "" Then Range("B88").Value = "" " should work only for  "If Range("B82").Value <> "" Then Range("B88").Value = "investigation report ."  " And not for what I type manually .

    Is it possible or not ?

    regards

    Sunday, October 18, 2015 11:01 AM
  • Try the following and see if it is what you want.

    Private Sub Worksheet_Change(ByVal Target As Range)

        Application.EnableEvents = False
       
        If Target.Address = "$B$88" Then GoTo SkipCommands  'user edited B88 directly
       
        If Range("B82").Value <> "" Then Range("B88").Value = "investigation report ."
       
        If Range("B82").Value = "" And Range("B88").Value = "investigation report ." Then Range("B88").Value = ""
       
    SkipCommands:
       
        Application.EnableEvents = True


    Regards, OssieMac

    • Proposed as answer by Wouter Defour Tuesday, October 20, 2015 2:48 PM
    Sunday, October 18, 2015 8:19 PM
  • Hi OssieMac ,

    Thank you. It is exactly what I wanted .

    regards

    Monday, October 19, 2015 1:47 PM