I think my problem is that my array says:

Private Sub OKButton_Click()
Set ObjCollArray(0) = ActiveSheet.Comments
Set ObjCollArray(1) = ActiveSheet.UsedRange
Set ObjCollArray(2) = ActiveSheet.UsedRange
Set ObjCollArray(3) = ActiveSheet.UsedRange
Set ObjCollArray(4) = ActiveSheet.UsedRange
Set ObjCollArray(5) = ActiveSheet.UsedRange

I then reference these within a For Each sheet Loop which
loops through specific sheets. I am not sure how to set
this up differently do that it works.



>-----Original Message-----
>When you set something to the ActiveSheet.{whatever} it
sets the
>variable to whatever is the activesheet at the time of
the set
>statement. From your comments (and a brief look at the
code) it
>appears you believe it should create a dynamic link that
evaluates
>'activesheet' when you refer to the object.
Unfortunately, not so.
>
>Also, you fix one potential bug. Without a 'option
compare text'
>clause, Lcase(sheet.name)<>"Audit..." will always be
false!
>
>Finally, you can clean up the code some. If you intend
deleting the
>Audit Results worksheet if it already exists, why go
through hoops
>checking if it exists? Also, deleting an existing
worksheet will pop
>up an alert; you may want to suppress it.
>
> Dim x As Worksheet
> Application.DisplayAlerts = False
> With ActiveWorkbook
> On Error Resume Next
> .Worksheets("Audit Results").Delete
> On Error GoTo 0
> Set x = .Worksheets.Add()
> x.Name = "Audit Results"
> End With
> Application.DisplayAlerts = True
>
>--
>Regards,
>
>Tushar Mehta
>www.tushar-mehta.com
>Excel, PowerPoint, and VBA add-ins, tutorials
>Custom MS Office productivity solutions
>
>In article <[email protected]>,
>[email protected] says...
>> I built a loop which I knew was going to have various
>> objects and collections. So I decided to set up a
>> variable called ObjCollArray which I declared as a
Public
>> Variable. Then when I enter the For Next Loop, I use
the
>> format:
>>
>> For Each ObjCur in ObjCollArray(variable)
>>
>> Next
>>
>> Problem is that this loop is within another For Each
Loop
>> which loops through my sheets. When I do a Debug Print
on
>> the sheet name and the ObjCollArray.Parent.Name, they
do
>> not equal each other.
>>
>> Debug.Print sh.Name, ObjCollArray
(AuditTypes).Parent.Name
>>
>> Secondly, as my sheets loop, ObjArray.Parent.Name stays
on
>> the same sheet. It never updates.
>>
>> I can't figure out why. I am wondering if its is
because
>> I have set up my array saying ActiveSheet. Here is my
code
>>
>>
>>
>>
>> Public ObjCollArray As Variant
>> Public Comrng As Range
>> Public Hardrng As Range
>> Public Errrng As Range
>> Public Colrnge As Range
>> Public Validrng As Range
>> Public ValidErrrng As Range
>> Public ObjType As String
>> Public CollType As String
>> Public CurObj As Object
>>
>> Private Sub OKButton_Click()
>> Set ObjCollArray(0) = ActiveSheet.Comments
>> Set ObjCollArray(1) = ActiveSheet.UsedRange
>> Set ObjCollArray(2) = ActiveSheet.UsedRange
>> Set ObjCollArray(3) = ActiveSheet.UsedRange
>> Set ObjCollArray(4) = ActiveSheet.UsedRange
>> Set ObjCollArray(5) = ActiveSheet.UsedRange
>>
>> Private Sub ListAuditResults()
>> Dim PasteStartCell As String
>> Dim sh As Worksheet
>> Dim sh1 As Worksheet
>> Dim AuditTypes As Integer
>> Dim AuditShtName As String
>>
>>
>> Application.Calculation = xlManual
>> 'Application.ScreenUpdating = False
>>
>> On Error Resume Next
>> 'Set up name of new summary sheet
>> Set sh1 = ActiveWorkbook.Sheets("Audit Results")
>> On Error GoTo 0
>>
>> 'If Sheet called "Audit Results" already exists
>> 'then delete it and prepare to create a new one
>>
>> If Not sh1 Is Nothing Then
>> Application.DisplayAlerts = False
>> sh1.Delete
>> Application.DisplayAlerts = True
>> End If
>>
>>
>> With ActiveWorkbook
>>
>> 'Add a worksheet for results to be pasted to
>> .Worksheets.Add(After:=.Worksheets
>> (.Worksheets.Count)).Name = "Audit Results"
>>
>> 'Set up column headings for summary report
>> 'these will set up based on the numbers
>> 'options chosen.
>>
>> PasteStartCell = Range("B2").Address
>>
>> 'Set first paste cell and column header for
Commented
>> Cells
>>
>> If ComChkBx = True Then
>> Set Comrng = .Worksheets("Audit Results").Range
>> (PasteStartCell).Offset(0, ChkbxArray(0, 1) * 2 - 2)
>> Comrng.Offset(-1, 0) = "Cell Comments"
>> End If
>>
>> 'Set first paste cell and column header for Hard
Coded
>> Cells
>> If HardCodedChkBx = True Then
>> Set Hardrng = .Worksheets("Audit Results").Range
>> (PasteStartCell).Offset(0, ChkbxArray(1, 1) * 2 - 2)
>> Hardrng.Offset(-1, 0) = "Hard Coded Cells"
>> End If
>>
>> 'Set first paste cell and column header for Cells
with
>> Errors
>> If ErrorChkBx = True Then
>> Set Errrng = .Worksheets("Audit Results").Range
>> (PasteStartCell).Offset(0, ChkbxArray(2, 1) * 2 - 2)
>> Errrng.Offset(-1, 0) = "Errors"
>> End If
>>
>> 'Set first past cell for data validation cells
>> If DataValChkBx = True Then
>> Set Validrng = .Worksheets("Audit
Results").Range
>> (PasteStartCell).Offset(0, ChkbxArray(3, 1) * 2 - 2)
>> Validrng.Offset(-1, 0) = "Validation"
>> End If
>>
>> 'Set first past cell for data validation cells
>> If DataValErrChkBx = True Then
>> Set ValidErrrng = .Worksheets("Audit
>> Results").Range(PasteStartCell).Offset(0, ChkbxArray(4,
1)
>> * 2 - 2)
>> ValidErrrng.Offset(-1, 0) = "Validation Errors"
>> End If
>>
>> 'This should equal the entire number of Audit Types
>> 'in the userfor. It should be the total amount
>> 'not only the ones that were chosen
>>
>> 'Note these numbers feed are compared to a Select
>> 'Case stmt in the main module. So they cannot start
>> 'with 0 (i.e. 0 to 5 must be 1 to 6)
>> For Each sh In .Worksheets
>> If LCase(sh.Name) <> "Audit Results" Then
>> 'After its been determined that the sheet is not
the
>> 'comments sheet, code checks various conditions
>> 'For AuditTypes = 0 To 5
>> For Each CurObj In ObjCollArray(1)
>> 'Debug.Print CurObj.Parent.Name,
CurObj.Address
>> Debug.Print sh.Name, ObjCollArray
>> (AuditTypes).Parent.Name
>> ObjType = TypeName(CurObj)
>> CollType = TypeName(ObjCollArray(1))
>> Call MainAudit(2)
>> Next
>> 'Next
>> End If
>> Next
>> End With
>>
>.
>