Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to clear all unlocked cells except D4
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to clear all unlocked cells except D4
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 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to clear all unlocked cells except D4
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 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to clear all unlocked cells except D4
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 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to clear all unlocked cells except D4
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 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 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to clear all unlocked cells except D4
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 wrote: 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 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 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 -- Dave Peterson |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
how to clear all unlocked cells except D4
Thanks Dave,
I'll need to study this for a while. Dan On Oct 19, 5:17 pm, Dave Peterson wrote: 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: |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
clear unlocked cells macro help please | Excel Worksheet Functions | |||
Macro: Clear contents of unlocked cells | Excel Programming | |||
How can I have formatting options like merge cells ,Bold,active for the unlocked cells of the protected worksheet.Is it possible in excel? | Excel Programming | |||
Moving from unlocked cells to unlocked cells in an excel form | Excel Worksheet Functions | |||
Selecting only unlocked cells to clear | Excel Programming |