How to record macro using validated cells?

[MichaelRobert]

Seems that when I record a macro that uses 'validated' cells, the only thing
recorded is the selection of the last cell - nothing is recorded showing the
values of the cells.

I want to reset any selections in the validated cells back to the 'starting
point' entries (example: (Select one)).

Any ideas?

Thanks.

Mike

 

[JLGWhiz]

Will not record validation entries.

http://spreadsheets.about.com/od/advancedexcel/ss/excel_macro.htm

 

[Gary''s Student]

The Recorder is no good for this.

Our strategy is to begin by storing the initial value of the "validation"
cells in a public, static, array using ValidationTracker. At any future
point we can resotre these values using ValidationReset:

Public ValidValues(100) As Variant
Public rvld As Range

Sub ValidationTracker()
' gsnuxx
Set rvld = ActiveSheet.UsedRange.SpecialCells(xlCellTypeAllValidation)
i = 0
For Each r In rvld
ValidValues(i) = r.Value
i = i + 1
Next
End Sub

Sub ValidationReset()
i = 0
For Each r In rvld
r.Value = ValidValues(i)
i = i + 1
Next
End Sub

 

[MichaelRobert]

Thanks. Do we need to define 'r','i'? I am getting a compile error 'Sub or
function not defined' at

ValidValues(i) =

Mike

 

[MichaelRobert]

Btw, I have six Validation tables associated with the cells in question ...
Let's call them ValidAs through ValidFs. Do I need to use variables i through
n?

Mike