Select first choice in a validation list in a range that are not the same

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
I have a range of validation lists that needs to be reset to their first value (its a text string not numerical value).

Imagine if the validation lists in the range can be set to any number from 0-20 then you could reset it by the following simple macro:
Sub X()
Range("G16:G22").Value = "0"
End Sub

But in a range of validation lists where the first choice is not the same the above simple macro does not work, also it is text not numbers.
Also some cells in the range does not contain a validation list and is just a normal cell with text.

How can I make a macro that resets the range to the first selection in all the validation lists in the range, while I still have some none validation cells ?
 

Some videos you may like

Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
Try this, it checks the entire sheet for data validation but you can easily change that.
VBA Code:
Sub ResetValidation()
Dim rngValidation As Range
Dim cell As Range
Dim arrValues As Variant

    For Each cell In Cells.SpecialCells(xlCellTypeAllValidation)
        If Left(cell.Validation.Formula1, 1) = "=" Then
            arrValues = Evaluate(cell.Validation.Formula1)
            cell.Value = arrValues(1, 1)
        Else

            arrValues = Split(cell.Validation.Formula1, ",")
            cell.Value = arrValues(0)
        End If

    Next cell

End Sub
 

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
1606655339499.png

I tried to change the code from the best of my understanding. But the Array says type mismatch and I couldn't figure out what to change, I tried the numbers.
Also I have no VBA experience, only very basic C a very long time ago.
So if anyone could push me in the correct direction I'd appreciate it.

Thanks
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
How is it not working?

Are you getting errors?

Also, how have you set the data validation up?
 

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

How is it not working?
When I press the macro button it gives this error:
1606830916205.png

Are you getting errors? See above and last post.

Also, how have you set the data validation up?


Most of the cells are set up with normal Data Validation:
1606830561202.png

Two of the cells are set up with a data list in another sheet because I need another cell to be vlookup dependable: (had to blackout some sensitive information)
1606830701123.png


And again some cells are no lists at call just normal cells with text
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
Can you post some sample data rather than images?
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
When I try the code I posted in the workbook you uploaded it works without problems.

The data validations are all set to their initial values and there are no errors.
 

Stjernemix

New Member
Joined
Nov 28, 2020
Messages
12
Office Version
  1. 365
Platform
  1. Windows
When I use your posted code on the test sheet it does this:
1606988348351.png

As you can see there is some inherent error in the 2 first table cells. Where is shows the entire Data Validation string. (I'm using Excel 365)

Also, the code I posted was me trying to re-code your code so it would only effect a Range in one Column eg. F16 to F22 ("F16:F22").
This is were I get the yellow error in the VBA code, because I tried to re-code it within a Range.

-------------------------
Ok while replying I figured out what I need to change for it to work correctly in the test sheet. I needed to change
arrValues = Split(cell.Validation.Formula1, ",")
to
arrValues = Split(cell.Validation.Formula1, ";")

Now it works in the test sheet, but just without within a Range (since you mentioned your code resets the entire sheet)
-------------------------
But in the real sheet it gives me this error when I run the same code that worked in the test sheet.
1606989744031.png

1606989909293.png
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,264
Office Version
  1. 365
Platform
  1. Windows
What happens if you don't change the comma in the Split?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,204
Messages
5,623,352
Members
415,969
Latest member
Rey99

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