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