How to record macro using validated cells?
 

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

How to record macro using validated cells?
 

Will not record validation entries.

http://spreadsheets.about.com/od/adv...xcel_macro.htm

"MichaelRobert" wrote:

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

How to record macro using validated cells?
 

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(xlCellTypeAllVa lidation)
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

--
Gary''s Student - gsnu200817


"MichaelRobert" wrote:

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

How to record macro using validated cells?
 

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

ValidValues(i) =

Mike

"Gary''s Student" wrote:

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(xlCellTypeAllVa lidation)
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

--
Gary''s Student - gsnu200817


"MichaelRobert" wrote:

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

How to record macro using validated cells?
 

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

"MichaelRobert" wrote:

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

ValidValues(i) =

Mike

"Gary''s Student" wrote:

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(xlCellTypeAllVa lidation)
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

--
Gary''s Student - gsnu200817


"MichaelRobert" wrote:

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