select the first value from data validation list

[white_flag]

Hello I have the following code
This will make a selection based on array list. What is not working is to select the first value of the data validation list after data validation was created. I mean is selected the first value but is looking like this value1;value2 insted to be value under value



Sub test2()
Dim c1 As Range
Dim Tape As Variant, Plate, Paint
Tape = Array("m", "m²")
Plate = Array("kg", "kg", "kg")
Paint = Array("kg", "liters")
For Each c1 In Range("$B$2:$B" & Cells(Rows.Count, "B").End(xlUp).Row)
Select Case c1
Case "Tape"
With ActiveSheet.Cells(c1.Row, "C").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(Tape, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Cells(c1.Row, "C").Value = Mid(Cells(c1.Row, "C").Validation.Formula1, 1)
Case "Plate"
With ActiveSheet.Cells(c1.Row, "C").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(Plate, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Cells(c1.Row, "C").Value = Mid(Cells(c1.Row, "C").Validation.Formula1, 1)
Case "Paint"
With ActiveSheet.Cells(c1.Row, "C").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(Paint, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
Cells(c1.Row, "C").Value = Mid(Cells(c1.Row, "C").Validation.Formula1, 1)
Case Else:
End Select
Next

End Sub

[raj85]

Create one string variable named temp and use below code


temp = Cells(c1.Row, "C").Validation.Formula1
Cells(c1.Row, "C").Value = Mid(temp, 1, InStr(1, temp, ",", vbTextCompare) - 1)

[snb]

this can be done more simply:


Sub test2()
With Sheets("sheet1").Cells(1).CurrentRegion
For j = 1 To 3
.AutoFilter 1, Choose(j, "Tape", "Plate", "Paint")
.Offset(1, 1).Resize(, 1).SpecialCells(12).SpecialCells(2).Validation.Modify 3, , , Choose(j, "m,m²", "mgr,gr,kgr", "kg,liters")
.AutoFilter
Next
End With
End Sub

[white_flag]

snb, if I put your code in "Worksheet_Change" is acting strange
raj85, I have error 5 - Invalid procedure call of argument

[white_flag]

in:

Cells(c1.Row, "C").Value = Mid(temp, 1, InStr(1, temp, ",", vbTextCompare) - 1)
so , need to be ;
It is mixing the , with ; because the operating system is in dutch an excel is in English. Do you have an idea how to avoid this?

[SamT]

The problem is in the "Mid" function. it is returning the entire comma delimited string of Formula1 starting at the first character

With ActiveSheet.Cells(c1.Row, "C").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(Tape, ",")
.IgnoreBlank = True
.InCellDropdown = True
'All empty values not needed
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
'No input message
.ShowInput = True
'No Error Message
.ShowError = True
End With

The ShowInput and ShowError Properties might not be the issue, but try this simplified code and see.

With ActiveSheet.Cells(c1.Row, "C").Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(Tape, ",")
.IgnoreBlank = True
.InCellDropdown = True
End With



If you are a lazy typist :) you might want to structure the code like this.

Dim ValidList As Variant
For Each c1 In Range("$B$2:$B" & Cells(Rows.Count, "B").End(xlUp).Row)
Select Case c1
Case "Tape"
ValidList = Tape
Case "Plate"
ValidList = Plate
Case "Paint"
ValidList = Paint
End Select
With c1.Offset(0, 1).Validation
.Delete
.Add Type:=xlValidateList, Formula1:=Join(ValidList, ",")
.IgnoreBlank = True
.InCellDropdown = True
c1.Offset(0, 1).Value = ValidList(1) 'or (2) or (3) ' Not known what you want
End With
Next

[white_flag]

ok I putted like this:



Cells(c1.Row, "C").Value = Mid(temp, 1, InStr(1, temp, Application.International(xlListSeparator), vbTextCompare) - 1)


and it is working like I want.

thank you!

[raj85]

Hello,

I have attached file and called the procidure in worksheet change event it working fine. NO errors.
And Thanks SamT for explaining thses basic things as well :hi:

[white_flag]

Hello Sam
I like your code it is working well :) (thank you! for that)

[SamT]

Validations add to file size. Consider before saving workbook:


Delete all validations


Alternately:


Dim Workbook Global Variable LastValid As Range
On Worksheet Selection Change LastValid.Validation.Delete
After Cell.Validation.Add

Set LastValid = Cell


Workbook.BeforeSave LastValid.Validation.Delete

[snb]

snb, if I put your code in "Worksheet_Change" is acting strange
raj85, I have error 5 - Invalid procedure call of argument

Did I put my code in an event ?
Did you test the code in the attachment I provided ?
Did you analyse the code I provided ?
Do you think 'acting strange' is meaningful feedback ?

I don't think deleting & adding a validation is an elegant solution for a property that can be modified using .modify (even if it's working 'well')

[SamT]

If a Validation Object is not present, will .Modify add one? I've never tried it.

[snb]

@SamT

Modify will only modify an existing validationrule.


on error resume next
cells(j,3).validation.modify 3,,,"a1,b1,c3"
if err.number <>0 then cells(j,3).validation.add 3,,,"A1,b2,c3"
err.clear

[SamT]

thanks for confirming.

[white_flag]

Did I put my code in an event ?
Did you test the code in the attachment I provided ?
Did you analyse the code I provided ?
Do you think 'acting strange' is meaningful feedback ?

I don't think deleting & adding a validation is an elegant solution for a property that can be modified using .modify (even if it's working 'well')


Hello snb
Yes I did test your code and I putted in the event.
I have to admit that I did not had time too look in details to your code (your code need time to be digested)
by 'acting strange' please look in attachment.

[snb]

Didn't test the file I posted, you changed it.
The 'strangeness' is due to those changes.

[white_flag]

I just add


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Call test2
End Sub

because I need for example on each new entry to have an validation list

[snb]

It doesn't make sense to run that macro every time another cell is being selected in the sheet; it's overly redundant.
You also added an item in A5.
As you might have noticed in the macro it modifies the validation rule in column B; since you didn't add a validation rule in B5 the macro errors out: it can't modify a non-existing validation rule.
See also: http://www.vbaexpress.com/forum/showthread.php?47258-select-the-first-value-from-data-validation-list&p=295669&viewfull=1#post295669

[white_flag]

But the trigger of code can be intersection from cell with columns (not for all cells from sheet)
Otherwise how can be done If I want to add a new product based on category (Paint, Plate ..etc) then to have for that product unit of measurement?

[snb]

Add all new products; run the macro once: ready.

If you need a macro that adds a validationrule in column B every time a new product has been added in Column A you need quite a different macro. It digresses considerably from the title of this thread.

[white_flag]

Correct ... but anyway, for me was necessary to know how can I select the first value from an validation list :)
Damn ... The rain just start