CODEWITHSUNDEEP
excelvba
Simon.G
Simon.G

Reputation: 25

Fill a cell when specific value entered in other cell

I am trying to oblige the user, filling in column B a value from a dropdown list, to also fill a column I cell from same row and sheet. With possibility to cancel the action, removing then his choice at column B.

I am thinking to do that with an inputbox to fill, clicking ok would then fill the cell at column I.

Upvotes: 0

Views: 58

Answers (1)

Simon.G
Simon.G

Reputation: 25

I did this that answers perfectly my need, for information

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Column = 2 Then
        ThisRow = Target.Row
        If Target.Value = "Sourcing" Then
            inputdate = InputBox("Please enter Sourcing end date target dd/mm/yyyy", "Enter Sourcing end date", Format(inputdate, "mm/dd/yyyy"))
            If inputdate = "" Or inputdate = vbNullString Then
                Range("I" & ThisRow).Value = ""
                Range("B" & ThisRow).Value = ""
            Else
                If IsDate(inputdate) Then
                    inputdate = Format(CDate(inputdate), "mm/dd/yyyy")
                    Range("I" & ThisRow).Value = inputdate
                Else
                    MsgBox "Wrong date format"
                    Range("I" & ThisRow).Value = ""
                    Range("B" & ThisRow).Value = ""
                End If
            End If
        ElseIf Target.Value = "Phasing out" Then
            inputdate = InputBox("Please enter History target date dd/mm/yyyy", "Enter History date", Format(inputdate, "mm/dd/yyyy"))
             If inputdate = "" Or inputdate = vbNullString Then
                Range("I" & ThisRow).Value = ""
                Range("B" & ThisRow).Value = ""
            Else
                If IsDate(inputdate) Then
                    inputdate = Format(CDate(inputdate), "mm/dd/yyyy")
                    Range("I" & ThisRow).Value = inputdate
                Else
                    MsgBox "Wrong date format"
                    Range("I" & ThisRow).Value = ""
                    Range("B" & ThisRow).Value = ""
                End If
            End If
        ElseIf Target.Value = "History" Then
            Range("I" & ThisRow).Value = Date
        ElseIf Target.Value = "" Then
            Range("I" & ThisRow).Value = ""
        End If
    End If
End Sub

Had to find a trick about date format that was changing from dd/mm to mm/dd, add some condition if window is cancelled or date empty and this works fine

Upvotes: 0

Related Questions