looping through worksheets from addin

  • Thread starter Thread starter strataguru
  • Start date Start date
S

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!
 
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
============================================================
 
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
 
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
 
strataguru said:
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?
Click to expand...

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
 
Back
Top