how do I loop through every comment on all of my sheets?

[Christos79]

Hey!

I have created a dvdlist in excel with all of my movies... every sheet is a category with just one column per sheet. On the cells on that column I have my dvd movies in a-b-c order. each movie has a comment where I describe the plot of the movie, the actors who star in it etc.

how do I loop through the comments on every sheet and show in a specifik textbox which movies a specific actor stars in?

[Simon Lloyd]

Here you go!

Sub All_Cell_Comments()
Dim MyCell As Range
Dim strNewList As String
On Error GoTo Nxt
Sheets("sheet1").ListBox1.Clear
For Each Worksheet In Sheets
For Each MyCell In Range("A1:A10").SpecialCells(xlCellTypeComments)
With Sheets("Sheet1").ListBox1
.AddItem MyCell.Comment.Text
End With
Next MyCell
Next WorkSheet
Nxt:
MsgBox "No Further Comments Found"
End Sub

[Christos79]

Thanx!

Is this for all the sheets or jsut for sheet1?

[Simon Lloyd]

No, the Sheet1 reference is just for the list box change it to whichever sheet has the list box or to your userform listbox, the actual code loops through all the worksheets in the workbook!

[Christos79]

Ok. I see. :)

[tstav]

Hi Christos and Simon,

There are two points I'd like to mention on Simon's code.

First: in the outer "For...each" loop the Range is staticly pointing to the ActiveSheet and not to the loop's current Worksheet. In other words it is only checking the Comments on the Activesheet.

Second: The error handling code will stop the execution whenever a Worksheet without any comments is encountered. In case this is a middle Worksheet, the loop will not finish looping and there may be Worksheets left unchecked.

[tstav]

This is the code that'll do the job in case we are working on a Userform.
I have written it on the basis that:
-there's a textbox in the userform (txtActor) where the name of the actor is input.
-there's a listbox in the userform (lstResult) where the DVD titles are added in case the Actor is found in this DVD's comments.
-there's a button in the userform (btnFind) which we press after entering the name of the Actor we are seeking

Private Sub btnFind_Click()
If Me.txtActor.Value <> "" Then
Dim Sht As Worksheet, R As Range, cell As Range
Me.lstResult.Clear
On Error Resume Next

'For each Sheet
For Each Sht In Worksheets

'Find the cells that contain comments
Set R = Sht.Columns("A").SpecialCells(xlCellTypeComments)

'If no comments found, skip this Sheet
If Err Then
Err.Clear

'If comments were found
Else

'Search in each comment for the actor
For Each cell In R
'If actor found, add them to the list
If InStr(1, cell.Comment.Text, Me.txtActor.Value, vbTextCompare) > 0 Then
Me.lstResult.AddItem cell.Value
End If
Next

End If
Next
End If
End Sub

[Bob Phillips]

I have to ask, why would anyone ever store data in comments, why not just have extra columns with those details.

Personally, I never use commenst, the Excel control over where they show is flawed, but I would certainly never use it to add information, much simpler to use cells.

[Christos79]

Well, I use comments ?cause there to much info to store in a second column... I mean. not everyone will use the macro. My GF will just go to a cell and want to see the info of the movie in the activecell of her choice without any macro. So I want the excel file to be presented in a userfriendly way and at the same time have the option to use macro to find for example an actor through the comments field. that is the reason for using comments

[Christos79]

Hey!

I tried your code but the FOR loop only works on the activated sheet... it doesn?t search the next sheet or the one after that. Just the same sheet over and over again. What am I missing?

[Simon Lloyd]

Maybe this slight variation in tstav's code will work! untested!

Private Sub btnFind_Click()
If Me.txtActor.Value <> "" Then
Dim Sht As Worksheet, R As Range, cell As Range
Dim i As Integer
Me.lstResult.Clear
On Error Resume Next

'For each Sheet
For i = 1 To Sheets.Count
With Sheets(i)
'Find the cells that contain comments
Set R = Sht.Columns("A").SpecialCells(xlCellTypeComments)

'If no comments found, skip this Sheet
If Err Then
Err.Clear

'If comments were found
Else

'Search in each comment for the actor
For Each cell In R
'If actor found, add them to the list
If InStr(1, cell.Comment.Text, Me.txtActor.Value, vbTextCompare) > 0 Then
Me.lstResult.AddItem cell.Value
End If
End With
Next

End If
Next
End If
End Sub

[Christos79]

Thanx Simon But still same thing...it just goes through the active sheet over and over...

[Bob Phillips]

The original code works fine for me, goes through all sheets and picks up the values fine.

[Simon Lloyd]

Christos, are your other sheets hidden or perhaps protected?
Are all the worksheets you want it to work on in the same book?

If you cannot get it to work you will need to post a dummy workbook with exactly the same setup as your original!

[Simon Lloyd]

Before the last NEXT in the code add Msgbox sh.name this will tell you the name of the sheet it is working on.

[Simon Lloyd]

Private Sub btnFind_Click()
Dim i As Integer
If Me.txtActor.Value <> "" Then
Dim Sht As Worksheet, R As Range, cell As Range
Me.lstResult.Clear
On Error Resume Next
For i = 1 To Sheets.Count
Set R = Sheets(i).Columns("A").SpecialCells(xlCellTypeComments)
If Err Then
Err.Clear
Else
For Each cell In R
If InStr(1, cell.Comment.Text, Me.txtActor.Value, vbTextCompare) > 0 Then
Me.lstResult.AddItem cell.Value
End If
Next

End If
Next i
End If
End Sub

[Christos79]

Hey Simon!

No Sheets are hidden.
How do I see if the sheets are protected?
Yes, the are all in the same workbook.

I?ll try the Msg thing and get back to you

[Christos79]

Hey Simon!

The MsgBox says that the loop goes through all of the sheets but how come that it only writes out the first sheet ... I mean the movies where the actor stars in over and over again. And when I search for a actor on a another sheet no movies appear... I know that I have movies with that actor

[Simon Lloyd]

Did you try my last posted code? it worked perfect for me!

[Christos79]

I tried... the strange part is that I have another sheet loop on a nother module and that one works fine.. but when I move that one where want the commetns search to be this happens... same with every other for loop code as well... maybe there?s something wrong in the code that i use, what do u think?

[Simon Lloyd]

Thats a definate!

[tstav]

the strange part is that I have another sheet loop on a nother module and that one works fine... but when I move that one where want the commetns search to be this happens... same with every other for loop code as well...

Hi Christos,
I can see that the code you're using is still practically the code I posted, back in post #7. This code works fine whatsoever.

It is not clear to me what you mean when you say "...when I move that one where want the comments search to be this happens...". What do you mean "you MOVE code where the comments are"?

1. Would you please post the code you are actually using?
2. Please confirm the following:
You have a userform with a button (btnFind), a textbox (txtActor) and a listbox (lstResult).

[Christos79]

Sorry, I was in a hurry when I wrote.

what I meant was that I have a for-loop on a another module in the same workbook that loops through the sheets. I tried to copy that one to the module I have a problem with to see it worked there but I have the same problem there with that for loop that I have with the once you guys gave me. And I don?t udnerstand why, your code work fine for you and the other for loop that I have works fine in its original module but when I try everything in this one nothing works like it should.

[Simon Lloyd]

Then don't try and add it all together you can use the term Call in your code to start another macro running so when running the code supplied here at this forum at the top under the Dim statements put:

Call MyMacro
where mymacro is the name of your other procedure once it has finished running it will then go on to run the one supplied here!

Other than that without you posting a sample workbook with all your code in we can not help further!

[Christos79]

I can give it a try...

my code is no more different then yours. I tried yours and I tried the other code... so far I?ve tried 3-4 different but still the same... this is my version of it:

Private Sub Sokskadespelare2_Click()
Dim sh As Worksheet
Dim rngA As Range
Dim strA As String

If hittaskadespelare8.text = "" Then
Exit Sub
End If

infoomskadespelare4.MultiLine = True

For Each sh In Worksheets
For Each rngA In ActiveSheet.Columns(1).Cells.SpecialCells(xlCellTypeComments)
If InStr(1, rngA.Comment.text, hittaskadespelare8) > 0 Then
strA = strA & rngA.Value & vbCr & vbCr
End If
Next
Next sh

infoomskadespelare4 = "Kategori: " & Range("A1") & vbCr & "-----------------------------------------------------------------------------------------------" & vbCr & vbCr & strA

End Sub

[tstav]

Ok I got your code.
I changed the Activesheet to sh and I changed the Instr line.
Please add the necessary "-----------" to the final line again.
Try it.

Private Sub Sokskadespelare2_Click()
Dim sh As Worksheet
Dim rngA As Range
Dim strA As String
If hittaskadespelare8.Text = "" Then
Exit Sub
End If
infoomskadespelare4.MultiLine = True
For Each sh In Worksheets
For Each rngA In sh.Columns(1).Cells.SpecialCells(xlCellTypeComments)
If InStr(1, rngA.Comment.Text, hittaskadespelare8.Value, vbTextCompare) > 0 Then
strA = strA & rngA.Value & vbCr & vbCr
End If
Next rngA
Next sh
infoomskadespelare4.value = "Kategori: " & Range("A1") & vbCr & "--" & vbCr & vbCr & strA
End Sub

[Christos79]

Hey Simon ! This time it worked... when I tried to call the fucntion instead. But one thing remains. How do I enter the kategori part so it tells me in which category the movie is in?

[Christos79]

Hey and thanx for the code update. it works but only one thing remains. I wrote Simon the same thing... how do I enter the kategori part so it tells me each category the movie. right now it tells me the movies but it doesn?t tell me all the categories... just the category on the sheet that is activated, in other words the sheet Im already on

[tstav]

Hey and thanx for the code update

NO. That was not an UPDATE. That was a CORRECTION. Your code was WRONG, that's why I took the trouble to CORRECT it.

I'm done with this thread.

[Christos79]

Thanx once again =)

[Christos79]

Hey Simon!

I solved the last part.
Thanx man for everything. I would never have solved it without you guys thanx a lot!!!