How does this code know which sheet to act upon?

[dan dungan]

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

 

[FSt1]

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]

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

 

[dan dungan]

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]

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]

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

Dan