how to clear all unlocked cells except D4

[dan dungan]

Hi Excel Programmers,

Excel 2000, xp pro

I'm using the code below to clear unlocked cells.

I call this procedure with three command buttons,

1. cmdReset-used to exit quote without printing
2. cmdAddPart-used to add another part number for the same customer
3. cmdComplete-used to print and store quote

when the associate wants to add a part, I need to keep the customer id
in cell D4 and clear all other unlocked cells.

when the associate resets or completes, I need to clear cell D4 along
with all other unlocked cells.

Does anyone have any suggestions?

Thanks,

Dan
--------------------------------------------------------------------------------------
Sub Clear_Unlocked2()
'clears the unlocked cells in range A1:N100 including merged cells
Dim myCell As Range
Application.EnableEvents = False
For Each myCell In Range("A1:N100")
If myCell.Locked = False Then
If myCell.MergeCells Then
If myCell.Address = myCell.MergeArea(1).Address Then
myCell.MergeArea.ClearContents
End If
Else
myCell.ClearContents
End If
End If
Next myCell
Range("A6").Select
Selection.End(xlUp).Select
Range("A2:C2").Select
Application.EnableEvents = True
End Sub

 

[Dave Peterson]

For Each myCell In Range("A1:N100")
if mycell.address = "$D$4" then
'skip it
elseIf myCell.Locked = False Then
If myCell.MergeCells Then
If myCell.Address = myCell.MergeArea(1).Address Then
myCell.MergeArea.ClearContents
End If
Else
myCell.ClearContents
End If
End If
Next myCell

 

[dan dungan]

Thanks Dave,

That works great!

I used the original procedure in the cmdReset button, and your edit
for the cmdAddPart button. I'm wondering if there is a way to write
that procedure so either button could use it to perform it's different
function.

Dan

 

[Dave Peterson]

I would think so. But I think you'll have to share that code to get any good
suggestions.

 

[dan dungan]

Hi Dave,

It seems that I'm cluttering things up and have not stated my question
very well. Please let me know if I have put too much information here.

Thanks,

Dan

Here's my request:

1.
a. when user clicks cmdReset, procedure clears all unlocked cells.
b. when user clicks cmdAddPart, procedure clears all unlocked cells,
except D4 because user is adding parts to a quote from the same
customer.

2. Dave provided a solution, but I had to create two procedures--one
for each button.

3. I put both procedures--Clear_Unlocked1 and Clear_Unlocked2 in a
regular module named modReset.

4. In the click event of the button

Here is the procedure I call in my cmdReset Button:

Sub Clear_Unlocked2()
'clears the unlocked cells in range A1:N100 including merged cells
Dim myCell As Range
Application.EnableEvents = False
For Each myCell In Range("A1:N100")
If myCell.Locked = False Then
If myCell.MergeCells Then
If myCell.Address = myCell.MergeArea(1).Address Then
myCell.MergeArea.ClearContents
End If
Else
myCell.ClearContents
End If
End If
Next myCell
Range("A6").Select
Selection.End(xlUp).Select
Range("A2:C2").Select
Application.EnableEvents = True
End Sub

Here is the procedure I call for cmdAddPart

Sub Clear_Unlocked1()
'Called by cmdAddPart-clears the unlocked cells in
'range A1:N100 including merged cells but does not clear
'the customer name so it is not cleared until the quote is complete.

Dim myCell As Range
Application.EnableEvents = False
For Each myCell In Range("A1:N100")
If myCell.Address = "$D$4" Then
'skip it

ElseIf myCell.Locked = False Then
If myCell.MergeCells Then
If myCell.Address = myCell.MergeArea(1).Address Then
myCell.MergeArea.ClearContents
End If
Else
myCell.ClearContents
End If
End If
Next myCell
Range("A6").Select
Selection.End(xlUp).Select
Range("A2:C2").Select
Application.EnableEvents = True
End Sub

---------------------------------------------------------------------------------------------------
Here's the code in the click event of the cmdAddPart

Private Sub cmdAddPart_Click()
'To prepare for printing--This checks that all required component
prices are entered
Dim rng As Range
Dim myRng As Range
Set myRng = Range("FormulaCriteria")

Dim qRng As Range
Dim qmyRng As Range
Set qmyRng = Range("QuantityRange")


'To determine how many parts the agent has added,
'Set a variable to count how many times the agent clicks the command
button
Dim clickcount As Variant
'If I don't do the following,
Application.EnableEvents = False
ActiveWorkbook.Unprotect ("pricing")
If Cells("2", "A").Value = "" Then
MsgBox "You have not entered a Part Number to quote.",
vbOKCancel
Range("A2").Activate
Exit Sub
End If
If Cells("2", "D").Value = "" Then
MsgBox "You have not entered a Connector Code.", vbOKCancel
Range("D2").Activate
Exit Sub
End If
If Cells("4", "D").Value = "" Then
MsgBox "You have not entered a Customer Name to quote.",
vbOKCancel
Range("D4").Activate
Exit Sub
End If


For Each rng In myRng


If Len(rng.Value) >= 1 And rng.Offset(0, 6).Value < 1 Then
MsgBox rng.Offset(-1, 0).Value & vbCrLf & "missing.",
vbAbortRetryIgnore, "Missing Price Error"
Exit Sub
End If
Next rng

If WorksheetFunction.Sum(Range("E83:O83")) < 1 Then
MsgBox "You have not entered a quantity", vbAbortRetryIgnore
Exit Sub
End If
For Each qRng In qmyRng


If Len(qRng.Value) >= 1 And qRng.Offset(3, 0).Value < 1 Then
MsgBox "Please enter the lead time for this quantity.",
vbAbortRetryIgnore, "Missing Price Error"
Exit Sub
End If
Next qRng

Hide_Print
Copy_1_Value_Property
Clear_Unlocked1
clickcount = txtCount + 1
txtCount = clickcount
Worksheets("QuotedPart").Cells(2, 1).Value = ""
ActiveWorkbook.Protect password:="pricing"
cboPartnum.Visible = False
Application.EnableEvents = True
Range("A2:C2").Select
End Sub

---------------------------------------------------------------------------------------------------------------------------
Here's the code for the click event of the cmdReset

Private Sub cmdReset_Click()
Clear_Unlocked2
cboPartnum.Visible = False
cmdAddPart.Visible = False
cmdReset.Visible = True
End Sub

 

[Dave Peterson]

Maybe something like this--where you pass the range to clear and the range to
skip as parms to the function that does the work:

Option Explicit

Sub Clear_UnlockedBoth(RngToClear As Range, RngToSkip As Range)
'Called by cmdAddPart-clears the unlocked cells in
'range A1:N100 including merged cells but does not clear
'the customer name so it is not cleared until the quote is complete.

Dim myCell As Range
Dim SkipThisCell As Boolean
Application.EnableEvents = False
For Each myCell In RngToClear.Cells
SkipThisCell = False
If RngToSkip Is Nothing Then
'nothing to skip
Else
If Intersect(myCell, RngToSkip) Is Nothing Then
'not one that should be skipped
Else
SkipThisCell = True
End If
End If

If SkipThisCell Then
'skip it
Else
If myCell.Locked = False Then
If myCell.MergeCells Then
If myCell.Address = myCell.MergeArea(1).Address Then
myCell.MergeArea.ClearContents
End If
Else
myCell.ClearContents
End If
End If
End If
Next myCell
Range("A6").Select
Selection.End(xlUp).Select
Range("A2:C2").Select
Application.EnableEvents = True
End Sub

'and call it with code like:
Sub testme()
Call Clear_UnlockedBoth(RngToClear:=Activesheet.Range("A1:N100"), _
Activesheet.RngToSkip:=Range("D1"))
Call Clear_UnlockedBoth(RngToClear:=Activesheet.Range("a1:N100"), _
Activesheet.RngToSkip:=Nothing)
End Sub

 

[dan dungan]

Thanks Dave,

I'll need to study this for a while.

Dan