VBA script to add text to cell.

[Guest]

Is there a VBA script that can add text to cell depending on choice from drop
down menu. Not the same "exact" letters as the coice from the drop down menu
though... already know how to do that.

 

[Norman Jones]

Hi Calle,

Your question is unclear.

Perhaps, however the following will help you.

Assume that cells A10:A10 have a data validation list applied and that the
list includes three values: Anne, Ben Carol.

Try something like:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Intersect(Range("A1:A10"), Target)

If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
With rng
Select Case LCase(.Value)
Case "anne": .Value = "Anne-Marie"
Case "ben": .Value = "Benjamin"
Case Else: .Value = "Invalid Entry!"
End Select
End With
End If

XIT:
Application.EnableEvents = True

End Sub
'<<=============

This is worksheet event code and should be pasted into the worksheets's code
module (not a standard module and not the workbook's ThisWorkbook module):

Right-click the worksheet's tab
Select 'View Code' from the menu and paste the code.
Alt-F11 to return to Excel.

 

[Guest]

Hi! thx for the response.
That's about it but I want the new text in another cell...
How do I do that

 

[Norman Jones]

Hi Calle,

Hi! thx for the response.
That's about it but I want the new text in another cell...
How do I do that

Try something like:

'=============>>
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range

Set rng = Intersect(Range("A1:A10"), Target)

If Not rng Is Nothing Then
On Error GoTo XIT
Application.EnableEvents = False
With rng
Select Case LCase(.Value)
Case "anne": .Offset(0, 1).Value = "Anne-Marie"
Case "ben": .Offset(0, 1).Value = "Benjamin"
Case Else: .Offset(0, 1).Value = "Invalid Entry!"
End Select
End With
End If

XIT:
Application.EnableEvents = True

End Sub
'<<=============

This will place the new text in the next column. To place the text
elsewhere, simply amend the offset co-ordinates or stipulate the required
address(es).

 

[Guest]

thx for your time. it worked great