Comment box listing sheet names from active cell

[vinny2984]

I'd really appreciate someone looking over my failing code and telling me where I'm going wrong. I'm very new to writing code so expect its full of basic errors.
The quest is for the acive cell in the sheet "Pump" to display a comment box listing the sheet names in the range "WMB:FFPT2" that contain a value of "1" in the same cell range as the active cell on the "Pump" sheet. I hope that makes sense.
many thanks
Vinny

Private Sub Riders_Box()
Dim c As String
Dim cv As Object
Dim ws As Worksheet
Dim myString As String

c = ActiveCell.Address

For Each ws In ActiveWorkbook.Worksheets("WMB:FFPT2")
If ws.Range(c).Value = 1 Then
myString = myString & ws.Name & Chr(10)
Else
End If
Next ws
Comments(2).Text "Riders:" & Chr(10) & myString
End Sub

[Demosthine]

Hey Vinny.

I'm not quite following the logic here. From reading the code, this is my understanding:

In each Worksheet, search Range WMB:FFPT2 for a value of "1".
If a value of "1" is found in Range WMB:FFPT2, you want to add the Worksheet's name to a Comment Box for the active cell on Worksheet "Pump."

Now, assuming this is correct, can I assume that Range WMB:FFPT2 is a Named Range? Valid Ranges are only only Columns A:AZ and Rows 1:65356.

[GTO]

Vinny,

The below and attached example are only good if "WMB:FFPT2" was supposed to refer to a range of sheets...

Sub Riders_Box()
Dim c As String
'Dim cv As Object <not referred to
Dim ws As Worksheet
Dim myString As String
c = ActiveCell.Address
'// Presuming "WMB:FFPT2" was supposed to refer to an array of sheets, this does not//
'// work. //
'For Each ws In ActiveWorkbook.Worksheets("WMB:FFPT2")
'// You can though use the Array function to return the worksheets you want. //
For Each ws In ActiveWorkbook.Worksheets(Array("WMB", "Sheet2", "FFPT2"))
If ws.Range(c).Value = 1 Then
myString = myString & ws.Name & Chr(10)
End If
Next ws
'Comments(2).Text "Riders:" & Chr(10) & myString
'// It wasn't clear as to what the above referred to, so just substitution for demo.//
Worksheets("Pump").Range("C3").Comment.Text "Riders: " & Chr(10) & myString
End Sub

[rbrhodes]

Hi Vinny,

Normal practice would be to exclude the sheets you do not want the code to operate on (as in my example below)

It probably is possible to address a collection of sheets in other ways but I haven't seen your workbook so I can't recommend any other way at this point.




Private Sub Riders_Box()
Dim c As String
Dim ws As Worksheet
Dim myString As String
' Get current cell address
c = ActiveCell.Address

' Allow error if comment not present
On Error Resume Next

Range(c).ClearComments

' No errors
On Error GoTo 0

' Do all
For Each ws In ActiveWorkbook.Worksheets
'Except 'Exclude' sheets: Bob & Phred
If ws.Name <> "Bob" And ws.Name <> "Phred" Then
If ws.Range(c).Value = 1 Then
myString = myString & ws.Name & Chr(10)
End If
End If
Next ws

With Range(c)
.AddComment
.Comment.Text Text:="Riders:" & Chr(10) & myString
End With

' Destroy object
Set ws = Nothing

End Sub

[vinny2984]

Many thanks for the replies. I can see from the replies where i have gone wrong. i wrongly assumed the a range can be applied to a range of sheets. having seen i can't do this and seeing that the way to do it is to include the whole workbook and exclude shets i don't want, i'm confident i can now acheive what i need to.
Many thanks
Vinny