PDA

View Full Version : Reset different data validation cells



Dave T
05-14-2011, 07:35 AM
Hello All,

I have a macro that resets the data validation cells of a timesheet when I run it every time I create a new worksheet for the next four week period.

Running the following macro will reset the four named ranges to the third values in their respective lists:


Public Sub ResetDV()
Dim cell As Range

If MsgBox("Are you sure you want to reset times?", vbYesNo) = vbNo Then
'-- do nothing
Exit Sub
End If

For Each cell In Range("D12:D16,D22:D26,D32:D36,D42:D46,E12:E16,E22:E26,E32:E36,E42:E46,G12:G16,G22 :G26,G32:G36,G42:G46,H12:H16,H22:H26,H32,H35,H42,H45")
If cell.Validation.Type = xlValidateList Then
'.Cells(1, 1) = first value in data validation range column
'.Cells(3, 1) = third value in data validation range column
cell.Value = Range(cell.Validation.Formula1).Cells(3, 1).Value
End If
Next cell
End Sub

The validation cells in column D use a named range called BL_Start (before lunch start)
The validation cells in column E use a named range called BL_End (before lunch end)
The validation cells in column G use a named range called AL_Start (after lunch start)
The validation cells in column H use a named range called AL_End (after lunch end)What I would like to know is how to modify the macro to select different data for each validation cell i.e. at the moment every validation cell is populated with the third value from each named range/list.

What changes would be required to say select the second value from the BL_Start, the third value from the BL_End, the fourth value from the AL_Start, and so on ???

Also what would be needed to hard code specific values for specific cells ???

Any help would be appreciated.

Regards,
Dave T

Dave T
05-16-2011, 05:16 PM
Hello All,

I have a clumsy solution that works for me but I was hoping someone could offer a more elegant solution.



Public Sub ResetTimes()
If MsgBox("Are you sure you want to reset times?", vbYesNo) = vbNo Then
'-- do nothing
Exit Sub
End If
Call ResetDV_Columns_D_E_G
Call ResetDV_Columns_H
End Sub

Private Sub ResetDV_Columns_D_E_G()
Dim cell As Range
For Each cell In Range("D1216,D2226,D3236,D4246,E12:E16,E22:E26,E32:E36,E42:E46,G12:G16,G22:G26,G32 :G36,G42:G46")
If cell.Validation.Type = xlValidateList Then
cell.Value = Range(cell.Validation.Formula1).Cells(3, 1).Value
End If
Next cell
End Sub

Private Sub ResetDV_Columns_H()
Dim cell As Range
For Each cell In Range("H12,H15,H22,H25,H32,H35,H42,H45")
If cell.Validation.Type = xlValidateList Then
cell.Value = Range(cell.Validation.Formula1).Cells(4, 1).Value
End If
Next cell
End Sub

Regards,
Dave T