Results 1 to 15 of 15
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Need update on formula from Hans (Excel 2003; Win XP)

    Good afternoon, Hans....last week you gave me a couple of VBA formulae, as follows:

    Public Sub InitializeComments()
    Dim intCol As Integer
    Dim lngRow As Long
    Dim strAbsentees As String
    For lngRow = 2 To 366 ' modify as needed
    strAbsentees = ""
    For intCol = 6 To Range("IV" & lngRow).End(xlToLeft).Column ' modify as needed
    strAbsentees = strAbsentees & ", " & Cells(lngRow, intCol)
    Next intCol
    If strAbsentees = "" Then
    strAbsentees = " "
    Else
    strAbsentees = Mid(strAbsentees, 3)
    End If
    If Cells(lngRow, 1).Comment Is Nothing Then
    Cells(lngRow, 1).AddComment Text:=strAbsentees
    Else
    Cells(lngRow, 1).Comment.Text strAbsentees
    End If
    Next lngRow
    End Sub

    The second one was:

    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCell As Range
    Dim intCol As Integer
    Dim lngRow As Long
    Dim strAbsentees As String
    If Not Intersect(Target, Range("F2:AZ7")) Is Nothing Then
    For Each rngCell In Intersect(Target, Range("F2:AZ7")).Cells
    lngRow = rngCell.Row
    strAbsentees = ""
    For intCol = 6 To Range("IV" & lngRow).End(xlToLeft).Column
    strAbsentees = strAbsentees & ", " & Cells(lngRow, intCol)
    Next intCol
    If strAbsentees = "" Then
    strAbsentees = " "
    Else
    strAbsentees = Mid(strAbsentees, 3)
    End If
    If Cells(lngRow, 1).Comment Is Nothing Then
    Cells(lngRow, 1).AddComment Text:=strAbsentees
    Else
    Cells(lngRow, 1).Comment.Text strAbsentees
    End If
    Next rngCell
    End If
    End Sub

    I need these to run on a protected worksheet and I have figured out that the worksheet must be unprotected for these to 'update'....where can I insert the ActiveSheet.Unprotect and ActiveSHeet.Protect commands within the VBA modules....I tried to do it at the beginning but the worksheet was not 'unprotecting' and permitting the VBA to run........thanks

  2. #2
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need update on formula from Hans (Excel 2003;

    ....ps: and I gather that these will not work on cells that are in colums that are hidden ??...at least, the one that updates the comments form doesn't seem to run if the columns are hidden....

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need update on formula from Hans (Excel 2003; Win XP)

    1) Add the line

    ActiveSheet.Unprotect

    at the beginning of each of the procedures, and

    ActiveSheet.Protect

    at the end of each of the procedures (not at the beginning/end of the module). If you have protected the worksheet with a password, you must supply it:

    ActiveSheet.Unprotect "Topsecret"

    and

    ActiveSheet.Unprotect "Topsecret"

    2) The code takes hidden columns (or rows) in the range F2:AZ7 into account.

  4. #4
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need update on formula from Hans (Excel 2003;

    ...[img]/forums/images/smilies/smile.gif[/img]....I know very little about VBA so I don't ebven know where the procedures begin or end...[img]/forums/images/smilies/smile.gif[/img].....and I will recheck the hidden column problem, b/c I could nt' get the comment boxes to display data in cells that were in hidden columns.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need update on formula from Hans (Excel 2003;

    A procedure begins with the line

    Sub ...

    and ends with the line

    End Sub

    (Why you would have hidden columns escapes me, but never mind)

  6. #6
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need update on formula from Hans (Excel 2003;

    Thank you, Hans....got the Protect/Unprotect working.....on my spreadsheet, I still can't 'see' data in cells that are in hidden columns (BA:CZ).....when the columns are not hidden, the comment boxes are full of commas (that go between names) but when the columns are hidden, the comment boxes are empty...almost as if the columns being read are the ones that follow the hidden ones (eg: DA:EZ)...??.??

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need update on formula from Hans (Excel 2003;

    Can you post a small sample workbook with dummy data that demonstrates the problem?

  8. #8
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need update on formula from Hans (Excel 2003;

    I haven't been able to compress it any smaller than 162KB.....

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Need update on formula from Hans (Excel 2003;

    Have you tried removing elements not relevant to the problem, then zipping it?

  10. #10
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need update on formula from Hans (Excel 2003;

    I have removed pretty much everything...even zipped it is 163KB ...and it is only 7 columns and 5 rows (from A3:G8)...it must be some module in it that is making it so bulky, b/c it is almost 1.45MB

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need update on formula from Hans (Excel 2003;

    I think your problem is the <code>End(xlToLeft)</code> method - this ignores hidden columns just as if you pressed Ctrl+left arrow on the keyboard.
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need update on formula from Hans (Excel 2003;

    PS Try changing the code to this:

    <pre>Public Sub InitializeComments()
    Dim intCol As Integer
    Dim lngRow As Long
    Dim strAbsentees As String
    For lngRow = 2 To 366 ' modify as needed
    strAbsentees = ""
    For intCol = 6 To Activesheet.UsedRange.Column + activesheet.usedrange.columns.count - 1
    strAbsentees = strAbsentees & ", " & Cells(lngRow, intCol)
    Next intCol
    If strAbsentees = "" Then
    strAbsentees = " "
    Else
    strAbsentees = Mid(strAbsentees, 3)
    End If
    If Cells(lngRow, 1).Comment Is Nothing Then
    Cells(lngRow, 1).AddComment Text:=strAbsentees
    Else
    Cells(lngRow, 1).Comment.Text strAbsentees
    End If
    Next lngRow
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need update on formula from Hans (Excel 2003;

    Thanks you Rory...I tried your suggested change....it seems to work, altho it is 'reading' absentees from many more columns than I am using....the absentees are listed in columns BB:CO, but it is reading columns BB:IV....I need it to 'read' no further than column CO.....

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Need update on formula from Hans (Excel 2003;

    OK then - change this line:
    <code>For intCol = 6 To Activesheet.UsedRange.Column + activesheet.usedrange.columns.count - 1</code>

    to this:

    <code>For intCol = 6 To 93</code>

    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Need update on formula from Hans (Excel 2003;

    ...seems to work like a charm......thank you, Rory and Hans.....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •