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
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