Data validation problem

[julie_nickb]

Hello,
I have a drop-down list for data validation:

1. ABC
2. DEF
etc.

I would like to transfer only the first character to my cell. It would always be just one digit, i.e. the first character of the drop-down list.
I need to do it in VBA.

Thanks for your help.

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

This script assumes your column with The Data Validation list is column F that is why you see:
If Target.column=6
Change the 6 to the proper column number

To run the script just select any value in the column and the script runs when the value is selected and entered.

You said:
I would like to transfer only the first character to my cell
My cell ?? What cell. My script copies the first charter to the cell one column to the right

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/14/2021  3:02:01 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 6 Then
    Target.Offset(, 1).Value = Left(Target.Value, 1)
End If
End Sub

 

[julie_nickb]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window

This script assumes your column with The Data Validation list is column F that is why you see:
If Target.column=6
Change the 6 to the proper column number

To run the script just select any value in the column and the script runs when the value is selected and entered.

You said:
I would like to transfer only the first character to my cell
My cell ?? What cell. My script copies the first charter to the cell one column to the right

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/14/2021  3:02:01 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
If Target.Column = 6 Then
    Target.Offset(, 1).Value = Left(Target.Value, 1)
End If
End Sub

I will try to make it clearer:
I have a cell with data validation. The drop-down list for data validation is what I stated before -
1. ABC
2. DEF
etc.
This list should not change; When the user clicks on the selected entry in the list, only the number 1,2,etc. should be entered in the cell.

So, if cell A1 has validation with the above list, if the user selects "2. DEF". cell A1 should get value "2"

 

[My Aswer Is This]

Your saying the best I can understand is you have a Data Validation list in Range("A1")
And only in Range("A1")

Let's say it includes "Alpha" "Bravo" and "Charlie"
These are the only three values allowed to be entered.
This means only those three values can be entered in Range("A1")
But now you want a macro to run so when you choose "Apple" the value in Range("A1") will now change to "A"

Just "A" cannot be entered because "A" is not a allowed value to be entered.
So I'm still attempting to understand what you want.

 

[Peter_SSs]

I have a cell with data validation. The drop-down list for data validation is what I stated before -
1. ABC
2. DEF
etc.
This list should not change; When the user clicks on the selected entry in the list, only the number 1,2,etc. should be entered in the cell.

This Worksheet_Change event code should do it for you. To implement ..
1. Right click the sheet name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1 & edit the 'Const' line if required.
3. Close the Visual Basic window & test.

If you actually have a range of cells with this Data Validation requirement then post back with details

Private Sub Worksheet_Change(ByVal Target As Range)
  Const DVcell As String = "A1" '<- Edit to match your DV cell address
  
  If Not Intersect(Target, Range(DVcell)) Is Nothing Then
    Application.EnableEvents = False
    Range(DVcell).Value = Left(Range(DVcell).Value, 1)
    Application.EnableEvents = True
  End If
End Sub

 

[My Aswer Is This]

I did not know this could be done but I see it can by disabling Events.

I would do it this way:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/15/2021  4:44:13 AM  EDT
If Target.Address = Range("A1").Address Then
    Application.EnableEvents = False
    Target.Value = Left(Target.Value, 1)
    Application.EnableEvents = True
End If
End Sub

 

[Peter_SSs]

I did not know this could be done but I see it can by disabling Events.

The disabling events actually has nothing to do with being able to enter a Data Validation "non-allowed value" into the cell by vba. It is simply there because, if it wasn't, when the vba entered the left character back in the cell, the event change code would be called again ... and again ... and again in an infinite loop.

BTW, a simpler (& more efficient for the vba) way to write
If Target.Address = Range("A1").Address Then
is
If Target.Address = "$A$1" Then