Data validation problem

julie_nickb

New Member
Joined
Sep 13, 2018
Messages
46
Office Version
  1. 365
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.
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
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


VBA Code:
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

New Member
Joined
Sep 13, 2018
Messages
46
Office Version
  1. 365
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


VBA Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,473
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

VBA Code:
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

Well-known Member
Joined
Jul 5, 2014
Messages
17,669
Office Version
  1. 2013
Platform
  1. Windows
I did not know this could be done but I see it can by disabling Events.

I would do it this way:
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
49,473
Office Version
  1. 365
Platform
  1. Windows
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
 

Forum statistics

Threads
1,136,909
Messages
5,678,520
Members
419,768
Latest member
eguechi09x

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top