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
-------------------------------------------------------------------------------------------------------------------------
On Oct 19, 2:21 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> I would think so. But I think you'll have to share that code to get any good
> suggestions.
>
>
>
> dan dungan wrote:
>
> > 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
>
> > On Oct 19, 12:06 pm, Dave Peterson <peter...@verizonXSPAM.net> wrote:
> > > 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
>
> --
>
> Dave Peterson
Similar Threads
