VBA Code for Data Validation multiple selecion setup

August 11, 2016 at 09:39:51
Specs: Windows 64
Hi,

I am trying something I have only toyed with in the past on my own personal spreadsheets etc, but on Monday I was asked to create a VBA code which allows end users to select multiple items on a data validation piciklist. Although this is really not my job, I don't want to say that...I've done some research and it appeas there are a million and one codes out there on the internet but my spreadsheet is (1) protected (2) I need to set the code up to work on 3 different columns (T, Z & AF). This is one of the codes I found to be 'helpful' but will only work on one column.

Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
' www.contextures.com
'code runs on protected sheet
Dim oldVal As String
Dim newVal As String
Dim strSep As String
Dim strType As Long

'add comma and space between items
strSep = ", "

If Target.Count > 1 Then GoTo exitHandler

'checks validation type of target cell
'type 20 is a drop down list
On Error Resume Next
strType = Target.Validation.Type

If Target.Column = 20 And strType = 20 Then
Application.EnableEvents = False
newVal = Target.Value
Application.Undo
oldVal = Target.Value
If oldVal = "" Or newVal = "" Then
Target.Value = newVal
Else
Target.Value = oldVal _
& strSep & newVal
End If
End If

exitHandler:
Application.EnableEvents = True
End Sub

I appreciate any help you can offer!!!


See More: VBA Code for Data Validation multiple selecion setup

Report •

#1
August 11, 2016 at 10:16:38
First, a posting tip:

Please click on the blue line at the end of this post and read the instructions on how to post VBA code in this forum so that it is easier for us to read. Then edit/repost your code so that it looks similar to the example found via that link. Thanks!

re: your Target.Validation.Type section...

I had to use strType = 3 in order for the code to recognize the Drop Down:

If Target.Column = 20 And strType = 3 Then
  etc.

re: my spreadsheet is protected

Unprotect the sheet, Unlock the cells with the drop downs, Protect the sheet.

Obviously the users need to be able to access the Drop Downs. Unlocking them before Protecting the sheet allows for their use.

re: I need to set the code up to work on 3 different columns (T, Z & AF)

This requirement is not clear to me. When you say you want the code to work on 3 columns, I'm not sure if you mean individually e.g. in Column T the result should be e.g. 1, 2, 3 and in Column Z the result should be A, B C or if the final result should include every selection. e.g. 1, 2, 3, A, B, C.

If it's the former, try this:

     If Target.Column = 20 Or Target.Column = 26 Or Target.Column = 32 _
       And strType = 3 Then

If it's the latter, we'll need some more info, such as where the entire combination of choices from the Drop Downs is supposed to end up.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
August 11, 2016 at 10:44:52
Thank you for such a quick response - I've followed all of your instructions below:

1 - re: my spreadsheet is protected Let me clear this up - what I was trying to convey to all was I protected specific columns on the sheet however, of course none of the columns I've protected contain a dropdown list that the users would need to access.I protected these columns to restrict the user from changing the formulas.

2. re: I need to set the code up to work on 3 different columns (T, Z & AF) In columns T, Z & AF eah have a dropdown (which contains the same dropdown options e.g. Apple pie, Pineapple pie, Strawberry pie, Lemon spice pie). I need this code to allow the user to select pineapple pie,lemon spice pie. But this code does not appear to address all three columns.

3. The code is below: In my initial post I changed the 3's to 20' to reflect the T column. The code below was pulled off of a forum & I cannot (for the life of me) modify this code to get it to work for what I need.

Private Sub Worksheet_Change(ByVal Target As Range)
' Developed by Contextures Inc.
' www.contextures.com
'code runs on protected sheet
Dim oldVal As String
Dim newVal As String
Dim strSep As String
Dim strType As Long

'add comma and space between items
strSep = ", "

If Target.Count > 1 Then GoTo exitHandler

'checks validation type of target cell
'type 3 is a drop down list
On Error Resume Next
strType = Target.Validation.Type

If Target.Column = 3 And strType = 3 Then
  Application.EnableEvents = False
  newVal = Target.Value
  Application.Undo
  oldVal = Target.Value
  If oldVal = "" Or newVal = "" Then
    Target.Value = newVal
  Else
    Target.Value = oldVal _
      & strSep & newVal
  End If
End If

exitHandler:
  Application.EnableEvents = True
End Sub


Report •

#3
August 11, 2016 at 11:33:16
re: 1 - re: my spreadsheet is protected Let me clear this up...

So in essence, the comment about "protection" is irrelevant to this discussion, correct?

re: 2. re: I need to set the code up to work on 3 different columns (T, Z & AF)

I believe I have already addressed that requirement:

    If Target.Column = 20 Or Target.Column = 26 Or Target.Column = 32 _
       And strType = 3 Then

re: 3. The code is below: In my initial post I changed the 3's to 20' to reflect the T column.

I believe I addressed that also, but I'll explain it in more detail.

The code contains an If statement that is checking for 2 different things:

1 - Target.Column - This should be the number of the Column in which the change was made.

2 - Target.Validation.Type - This should be the value associated with the Type of DataValidation that is applied to the Target, as detailed here:

https://msdn.microsoft.com/en-us/li...

Changing both of those "tests" to 3 means:

You are checking for a change to Column C and a Validation.Type of "Drop Down". The Validation.Type is correct, but the Target.Column is not.

Changing both of those "tests" to 20 means:

You are checking for a change to Column T and a Validation.Type that doesn't exist. There is no Validation.Type of 20. The Target.Column is correct, but the Validation.Type is not.

If you back up one instruction before the If, you will see this:

strType = Target.Validation.Type

If the target is a Drop Down, strType will be set to 3. If you then test strType for "20", VBA will say "Nope, it's not 20" and the "And" will be false.

You need to use the instruction I used in my earlier response:

    If Target.Column = 20 Or Target.Column = 26 Or Target.Column = 32 _
       And strType = 3 Then

You should also change this comment because it is incorrect:

'type 20 is a drop down list

It should be:

'type 3 is a drop down list

If you are going to be trying to write/modify VBA code, perhaps you should familiarize yourself with the following debugging techniques. These techniques are invaluable when it come to figueing out what the code is (or isn't) doing. I couldn't do very much in VBA without them.

http://www.computing.net/howtos/sho...

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
August 11, 2016 at 11:39:01
Thank you! The mention of the protected columns was an fyi - I've learned from past experiences that I need to share EVERYTHING and let you (the experts) disregard whatever information is not necessary/or relevant.

I'll reread your comments and visit the site you've shared, thank you again!


Report •

#5
August 11, 2016 at 11:51:13
I understand (and appreciate) your comment about about sharing everything.

However, when we read "...but my spreadsheet is (1) protected..." especially when it is the #1 item mentioned, it would be hard for us to simply say "Oh...that's not relevant".

You gotta admit, that as written, it sure sounded like that was one of the reasons, if not the main reason, that the code you found on the internet was not working for you. :-)

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question