I have a workbook which any user can come in and choose several options in
order to give a price to a customer. The problem is that some customer's
qualify for promotions for the pricing column (F) and the user needs to
select the promotion from a validated list of options for each cell
(F14-F22). I integrated a "reset button" that when pressed runs a macro to
clear the contents of column "B"... What should the VBA be in order to, at
the same time, change my F14-F22 lists back to their first choice (blank)
when the "reset button" is pressed? I pasted what I currently have which is
only clearing column B (I tried writing the F column macro which is below
"Next Acell"):

Private Sub cmdReset_Click()
Dim TotRows As Integer
Dim Acell As Range
Dim i As Integer
Dim SheetName As String
Dim NetRev As Double

SheetName = "New Customers"
TotRows = 28
i = 3
For Each Acell In Worksheets(SheetName).Range("B" & i & ":B" & TotRows)
'reset to nothing
Worksheets(SheetName).Range("B" & i).Value = 0
i = i + 1
Next Acell

SheetName = "New Customers"
For Each cell In Range("F14,F15,F16,F17,F18,F19,F20,F21,F22")

If cell.Validation.Type = x1ValidateList Then

cell.Value = Range(cell.Validation.Formula1).Cells(1, 1).Value
End If
Next cell
End Sub

From what I am understanding, it looks like you could just say:

SheetName = "New Customers"
For Each cell In Range("F14:F22")

If cell.Validation.Type = x1ValidateList Then

cell.ClearContents
End If
Next cell
(I changed the For statement (for clarity) and the line above End If)
By clearing just the contents from the cell, the data validation will remain.

--
Brad E.


"JAbels001" wrote:

I have a workbook which any user can come in and choose several options in
order to give a price to a customer. The problem is that some customer's
qualify for promotions for the pricing column (F) and the user needs to
select the promotion from a validated list of options for each cell
(F14-F22). I integrated a "reset button" that when pressed runs a macro to
clear the contents of column "B"... What should the VBA be in order to, at
the same time, change my F14-F22 lists back to their first choice (blank)
when the "reset button" is pressed? I pasted what I currently have which is
only clearing column B (I tried writing the F column macro which is below
"Next Acell"):

Private Sub cmdReset_Click()
Dim TotRows As Integer
Dim Acell As Range
Dim i As Integer
Dim SheetName As String
Dim NetRev As Double

SheetName = "New Customers"
TotRows = 28
i = 3
For Each Acell In Worksheets(SheetName).Range("B" & i & ":B" & TotRows)
'reset to nothing
Worksheets(SheetName).Range("B" & i).Value = 0
i = i + 1
Next Acell

SheetName = "New Customers"
For Each cell In Range("F14,F15,F16,F17,F18,F19,F20,F21,F22")

If cell.Validation.Type = x1ValidateList Then

cell.Value = Range(cell.Validation.Formula1).Cells(1, 1).Value
End If
Next cell
End Sub

That did not work for me. It still won't clear the contents within column
F... this is what I put from your response... is this what you meant:

Private Sub cmdReset_Click()
Dim TotRows As Integer
Dim Acell As Range
Dim i As Integer
Dim SheetName As String
Dim NetRev As Double

SheetName = "New Customers"
TotRows = 28
i = 3
For Each Acell In Worksheets(SheetName).Range("B" & i & ":B" & TotRows)
'reset to nothing
Worksheets(SheetName).Range("B" & i).Value = 0
i = i + 1
Next Acell

SheetName = "New Customers"
For Each cell In Range("F14:F22")

If cell.Validation.Type = x1ValidateList Then

cell.ClearContents
End If
Next cell
End Sub


"Brad E." wrote:

From what I am understanding, it looks like you could just say:

SheetName = "New Customers"
For Each cell In Range("F14:F22")

If cell.Validation.Type = x1ValidateList Then

cell.ClearContents
End If
Next cell
(I changed the For statement (for clarity) and the line above End If)
By clearing just the contents from the cell, the data validation will remain.

--
Brad E.


"JAbels001" wrote:

I have a workbook which any user can come in and choose several options in
order to give a price to a customer. The problem is that some customer's
qualify for promotions for the pricing column (F) and the user needs to
select the promotion from a validated list of options for each cell
(F14-F22). I integrated a "reset button" that when pressed runs a macro to
clear the contents of column "B"... What should the VBA be in order to, at
the same time, change my F14-F22 lists back to their first choice (blank)
when the "reset button" is pressed? I pasted what I currently have which is
only clearing column B (I tried writing the F column macro which is below
"Next Acell"):

Private Sub cmdReset_Click()
Dim TotRows As Integer
Dim Acell As Range
Dim i As Integer
Dim SheetName As String
Dim NetRev As Double

SheetName = "New Customers"
TotRows = 28
i = 3
For Each Acell In Worksheets(SheetName).Range("B" & i & ":B" & TotRows)
'reset to nothing
Worksheets(SheetName).Range("B" & i).Value = 0
i = i + 1
Next Acell

SheetName = "New Customers"
For Each cell In Range("F14,F15,F16,F17,F18,F19,F20,F21,F22")

If cell.Validation.Type = x1ValidateList Then

cell.Value = Range(cell.Validation.Formula1).Cells(1, 1).Value
End If
Next cell
End Sub

J - That is what I meant for you to put in. I am not REAL versed in
programming so I am not going to attempt again. I hope someone else gets you
the response which will work. Sorry
--
Brad E.


"JAbels001" wrote:

That did not work for me. It still won't clear the contents within column
F... this is what I put from your response... is this what you meant:

Private Sub cmdReset_Click()
Dim TotRows As Integer
Dim Acell As Range
Dim i As Integer
Dim SheetName As String
Dim NetRev As Double

SheetName = "New Customers"
TotRows = 28
i = 3
For Each Acell In Worksheets(SheetName).Range("B" & i & ":B" & TotRows)
'reset to nothing
Worksheets(SheetName).Range("B" & i).Value = 0
i = i + 1
Next Acell

SheetName = "New Customers"
For Each cell In Range("F14:F22")

If cell.Validation.Type = x1ValidateList Then

cell.ClearContents
End If
Next cell
End Sub


"Brad E." wrote:

From what I am understanding, it looks like you could just say:

SheetName = "New Customers"
For Each cell In Range("F14:F22")

If cell.Validation.Type = x1ValidateList Then

cell.ClearContents
End If
Next cell
(I changed the For statement (for clarity) and the line above End If)
By clearing just the contents from the cell, the data validation will remain.

--
Brad E.


"JAbels001" wrote:

I have a workbook which any user can come in and choose several options in
order to give a price to a customer. The problem is that some customer's
qualify for promotions for the pricing column (F) and the user needs to
select the promotion from a validated list of options for each cell
(F14-F22). I integrated a "reset button" that when pressed runs a macro to
clear the contents of column "B"... What should the VBA be in order to, at
the same time, change my F14-F22 lists back to their first choice (blank)
when the "reset button" is pressed? I pasted what I currently have which is
only clearing column B (I tried writing the F column macro which is below
"Next Acell"):

Private Sub cmdReset_Click()
Dim TotRows As Integer
Dim Acell As Range
Dim i As Integer
Dim SheetName As String
Dim NetRev As Double

SheetName = "New Customers"
TotRows = 28
i = 3
For Each Acell In Worksheets(SheetName).Range("B" & i & ":B" & TotRows)
'reset to nothing
Worksheets(SheetName).Range("B" & i).Value = 0
i = i + 1
Next Acell

SheetName = "New Customers"
For Each cell In Range("F14,F15,F16,F17,F18,F19,F20,F21,F22")

If cell.Validation.Type = x1ValidateList Then

cell.Value = Range(cell.Validation.Formula1).Cells(1, 1).Value
End If
Next cell
End Sub