VBA Worksheet_Change(ByVal Target As Range)-CodePudding

I'm trying to change the value of my variable by a string but it doesn't allow, for example when I choose 2 in the drop list I would like 2/2 to appear but 1 appears

    Private Sub Worksheet_Change(ByVal Target As Range)
'Updateby Extendoffice
    
    selectedNa = Target.Value
    If Target.Column = 2 Then
        selectedNum = Application.VLookup(selectedNa, Worksheets("Descrição").Range("ClassVEE"), 2, False)
        If Not IsError(selectedNum) Then
            Target.Value = selectedNum
        End If
    End If

CodePudding user response：

Try

Target.Value = "'" & selectedNum

CodePudding user response：

Whenever you change a cell value inside a Worksheet_Change event you need to disable events. Otherwise changing a cell value will trigger another Worksheet_Change event and this will trigger another …

Also 2/2 can be interpreted as date (depending on localization settings). So make sure Excel takes it as text by setting .NumberFormat to text.

Private Sub Worksheet_Change(ByVal Target As Range)
    'Updateby Extendoffice
     
    If Target.Column = 2 Then
        selectedNum = Application.VLookup(Target.Value, Worksheets("Descrição").Range("ClassVEE"), 2, False)
        If Not IsError(selectedNum) Then
            Application.EnableEvents = False  ' make sure you don't run into an endless loop
            Target.NumberFormat = "@"   ' ensure 2/2 does not get interpreted as date or something odd.
            Target.Value = selectedNum
            Application.EnableEvents = True
        End If
    End If
End Sub