Hi,

In a module, modReset, I have the following code. It works on the
proper worksheet, QuotedPart, but I don't understand how it knows
which sheet to act upon.

I want to clear some cells--E2, G2, I2, K2, Q2, R2, S2 & T2--in a
different worksheet, 217, and I'm not clear how to append this code
to accommodate that process.

I certainly appreciate any recommendations.

Thanks,

Dan

hi
unless otherwise specified, vb assumes the code is for the active sheet.

post your code and we might be able to tell you more.

Regards
FSt1

"dan dungan" wrote:

> Hi,
>
> In a module, modReset, I have the following code. It works on the
> proper worksheet, QuotedPart, but I don't understand how it knows
> which sheet to act upon.
>
> I want to clear some cells--E2, G2, I2, K2, Q2, R2, S2 & T2--in a
> different worksheet, 217, and I'm not clear how to append this code
> to accommodate that process.
>
> I certainly appreciate any recommendations.
>
> Thanks,
>
> Dan
>

Woops,

I guess I hit send before I was finished.

Sub Clear_Unlocked2()
'Called by cmdReset-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.Address = "$I$4" Then
'skip it

'ElseIf 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

I used the following approach which seems to be working.
Private Sub cmdNextPartNum_Click()
'Clears the unlocked cells in Sheets "217" and "A&E,
'range A2:AB2,including merged cells to prepare for next quote
Dim myCell As Range
Application.EnableEvents = False

With Sheets("217")
For Each myCell In Worksheets("217").Range("A2:AB2")
If myCell.Locked = False Then
myCell.ClearContents
End If
Next myCell
End With


With Sheets("A&E")
For Each myCell In Worksheets("A&E").Range("A2:AB2")
If myCell.Locked = False Then
myCell.ClearContents
End If
Next myCell
End With

Worksheets("QuotedPart").Activate
Selection.End(xlUp).Select
CmdNextPartNum.Visible = False
cmdGetPrice.Visible = True
Range("A2:C2").Select
Range("A2:C2").ClearContents

Application.EnableEvents = True
End Sub

If you're going to use the with/end with structure, then you don't want this:

With Sheets("217")
For Each myCell In Worksheets("217").Range("A2:AB2")
If myCell.Locked = False Then
myCell.ClearContents
End If
Next myCell
End With

You'd want:

With Sheets("217")
For Each myCell In .Range("A2:AB2")
If myCell.Locked = False Then
myCell.ClearContents
End If
Next myCell
End With

Notice the dot in front of the .range("A2:AB2"). This means that this belongs
to the object in the previous With statement (sheets("217") in your example).

You could have skipped the with/end with, too and used:

For Each myCell In Worksheets("217").Range("A2:AB2")
If myCell.Locked = False Then
myCell.ClearContents
End If
Next myCell

You've got that range("A2:AB2") qualified with the preceding Worksheets("217").

ps. If you have problems with those merged cells, you may want to change:

mycell.clearcontents
to
mycell.value = ""




dan dungan wrote:
>
> I used the following approach which seems to be working.
> Private Sub cmdNextPartNum_Click()
> 'Clears the unlocked cells in Sheets "217" and "A&E,
> 'range A2:AB2,including merged cells to prepare for next quote
> Dim myCell As Range
> Application.EnableEvents = False
>
> With Sheets("217")
> For Each myCell In Worksheets("217").Range("A2:AB2")
> If myCell.Locked = False Then
> myCell.ClearContents
> End If
> Next myCell
> End With
>
> With Sheets("A&E")
> For Each myCell In Worksheets("A&E").Range("A2:AB2")
> If myCell.Locked = False Then
> myCell.ClearContents
> End If
> Next myCell
> End With
>
> Worksheets("QuotedPart").Activate
> Selection.End(xlUp).Select
> CmdNextPartNum.Visible = False
> cmdGetPrice.Visible = True
> Range("A2:C2").Select
> Range("A2:C2").ClearContents
>
> Application.EnableEvents = True
> End Sub

--

Dave Peterson

Thanks for your feedback, Dave. I updated the macro with that change.

Dan