looping through worksheets from addin

[strataguru]

I am trying to loop through all the worksheets from an addin.

When I created my addin - I had only one worksheet - Sheet1 - which I
didn't care about. Whereas, the workbook I'm running the addin against
has 10 worksheets.

When I execute the following:
For Each ws In Worksheets
If ws.Name <> "Trans" Then
For i = 1 To Columns.Count
If (ws.Cells(1, i).Value) = caseNumText Then
ws.Cells(Target.Row, i).Value = caseNum
ElseIf (ws.Cells(1, i).Value) = debtorIDText Then
ws.Cells(Target.Row, i).Value = DebtorID
End If
Next i
End If
Next ws
***********
The loop looks in Sheet1 - then exits .... it never gets into the
worksheets in the workbook.

What piece am I missing?
Thanks!

 

[Cliff Myers]

Try changing, For Each ws In Worksheets to For Each ws In Workbook

 

[BrianB]

Can't really tell without seeing the workbook

Text evaluation is case sensitive so perhaps :-
If UCASE(ws.Cells(1, i).Value) = UCASE(caseNumText) Then ....


Regards
BrianB
============================================================

 

[jason]

Is it looking at the addin's worksheets by any chance?
Does the add-in definitely specify the code to look at the worksheets
in the activeworkbook (rather than Thisworkbook)?

J

 

[strataguru]

think i got it with the following:


Private Sub XLapp_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
' code
For Each ws In Sh.Parent.Worksheets

' code

Next ws
End Sub

Thanks for the pointers!
-Robin

 

[Paul Robinson]

I am trying to loop through all the worksheets from an addin.

When I created my addin - I had only one worksheet - Sheet1 - which I
didn't care about. Whereas, the workbook I'm running the addin against
has 10 worksheets.

When I execute the following:
For Each ws In Worksheets
If ws.Name <> "Trans" Then
For i = 1 To Columns.Count
If (ws.Cells(1, i).Value) = caseNumText Then
ws.Cells(Target.Row, i).Value = caseNum
ElseIf (ws.Cells(1, i).Value) = debtorIDText Then
ws.Cells(Target.Row, i).Value = DebtorID
End If
Next i
End If
Next ws
***********
The loop looks in Sheet1 - then exits .... it never gets into the
worksheets in the workbook.

What piece am I missing?

Hi
Try
For Each ws In ActiveWorkbook.Worksheets

The AddIn might be assuming you want to look at ITS sheets, not the
sheets in the ActiveWorkbook. It's usually safest to refer to the
AddIn's sheets by ThisWorkbook.Worksheets and specify
ActiveWorkbook.Worksheets for the workbook you can see.
If that doesn't fix it, your problem could be related to the variables
in your code. Are you sure they have the values you expect etc? Also,
what Range does Columns refer to, maybe there is only 1 Column??
regards
Paul