Pass worksheet object name to a class module

[aquadulce]

Hello
I am hoping to get some insight or help into a problem I have using
class modules.

At the moment i am passing some data to the class via a loop which
skips through all the data in my list and performs an action on it.
The problem i have is that my list is set out on different sheet tabs
in Excel, so that I can run through the first tab but I then need to
switch to the next tab so that my results from the class module can
then be referenced to this tab.

My loop is here........

For Each Worksheet In Worksheets
If Worksheet.Name Like "Outstanding*" Then
sName = Worksheet.CodeName
Set sName = Worksheet
Set mycell = sName.Range("A4")
j = mycell.CurrentRegion.Rows.Count + 2
aRng = sName.Cells.Range("A4:A" & j)
For Each mycell In aRng
oEvent.rngnum = i
oEvent.name = mycell
oEvent.New_Event
i = i + 1
Next mycell
End If
Next Worksheet

Feeding ino the class module here............


Public Sub New_Event()
Dim sURL As String
Dim i As Integer
Dim mycell As Excel.Range
Dim j As Integer, iOffset As Integer

With Sheet3

Sheet3.Cells.Range("A3:A250").ClearContents

sURL = "http://brain.fserver.federal.com"
With .QueryTables.Add(Connection:= _
"URL;" & sURL, Destination:=Range("DA3"))
.FieldNames = False
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = False
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlOverwriteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebFormatting = xlWebFormattingNone
.WebSelectionType = xlAllTables
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.Refresh BackgroundQuery:=False
End With

Set mycell = .Range("DB6")
iOffset = -1
If mycell.Value <> "Event" Then
Set mycell = .Range("DC6")
iOffset = -2
If mycell.Value <> "Event" Then
Set mycell = .Range("DD6")
iOffset = -3
End If
End If

j = mycell.CurrentRegion.Rows.Count

For i = 0 To j
If mycell.Offset(i, 0).Value = "" Then GoTo
datanotavail
If InStr(mycell.Offset(i, 0), SrchText) <> 0 Then
.Cells.Range("R4").Offset(iRow, 0).Value =
mycell.Offset(i, 0)
.Cells.Range("Q4").Offset(iRow, 0).Value =
mycell.Offset(i, iOffset)
GoTo datanotavail
End If
Next i

End With

datanotavail:
End Sub


In a nutshell, what I want to do is change the Sheet3 to any sheet tab
depending on where the list of data is held.
Does anyone have any ideas on how to pass the sheet tab name as a
variable, I have tried many ways but always erroring with missing
object variable etc.

Thanks very much

 

[paul.robinson]

Hi
At the top of the code module containing the loop put:

Public wST as Worksheet

This can now be seen by the class module.
Rewrite your loop as

For Each Worksheet In Worksheets
If Worksheet.Name Like "Outstanding*" Then
sName = Worksheet.CodeName
Set wSH = Worksheets(sName)
Set mycell = sName.Range("A4")
j = mycell.CurrentRegion.Rows.Count + 2
aRng = sName.Cells.Range("A4:A" & j)
For Each mycell In aRng
oEvent.rngnum = i
oEvent.name = mycell
oEvent.New_Event
i = i + 1
Next mycell
End If
Next Worksheet

In your class module do:
With wSH
.Cells.Range("A3:A250").ClearContents
etc

regards
Paul