Solved

Comment transfer

Posted on 2011-09-23
28
194 Views
Last Modified: 2012-06-21
Can a comment only in a cell on one sheet be transferred to a cell on another sheet in the same workbook? The code looks up a customer address. The comment will be in the customer's
name cell associated with this record. I need to do it in a cell formula.


Thanks
0
Comment
Question by:Crh61663
  • 14
  • 11
  • +1
28 Comments
 
LVL 10

Expert Comment

by:Tony Barkdull
ID: 36589325
=worksheetname!celladdress

Like:
=Sheet1!A1
0
 

Author Comment

by:Crh61663
ID: 36589575
That just seems to give me the contents of the cell and not the comment associated with the cell.
The cell code attached looks up an address for the customer. I have a comment in the customer name cell that I need moved to an adjacent cell on the Order sheet.
=VLOOKUP(C8,OFFSET(CUSTOMERS!$A$3,0,0,COUNTA(CUSTOMERS!$A:$A),2),1)

Open in new window

0
 
LVL 41

Expert Comment

by:dlmille
ID: 36590636
Unfortunately, you need a UDF function for this..  I tried to see if there were any XLM Excel 4 macro functions you could use, but believe the below solution is simpler...

One like: http://chandoo.org/wp/2009/09/03/get-cell-comments/

Function getComment(incell) As String
 ' aceepts a cell as input and returns its comments (if any) back as a string
 On Error Resume Next
 getComment = incell.Comment.Text
 End Function

Open in new window


Put this (hit Alt-F11 and insert a module, unless you already have a public module) in a public module

then to use it, just go to a cell:

[A5]:  =getComment(A1)

to get the comment in cell A1 into A5.

For your example (into another sheet) see the attached...

I created a sheet of addresses (with comments in them) and names.  Then in another sheet, I have a dropdown to select an address.  The name is looked up, and also the comment is addressed, then obtained via the getComment UDF (userdefined function).

Enjoy!

Dave
lookupComment-r1.xls
0
 

Author Comment

by:Crh61663
ID: 36968147
Once the comment has been moved to the new cell, I need the cell to have the comment indicator in the corner. Then put mouse over the cell to view the comment in that cell in the pop up comment window.
Using the above VLOOKUP command that looks up the customer information, is there a way to put a formula in the adjacent cell that will look up the comment associated with that customer and copies it to that cell for viewing if needed?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36969358
Is this a new question?  Did the last post not work for you?

Can you provide a brief sample spreadsheet indicating what you're trying to do?

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36969699
@Crh61163

>>Your original request:  Can a comment only in a cell on one sheet be transferred to a cell on another sheet in the same workbook? The code looks up a customer address. The comment will be in the customer's name cell associated with this record. I need to do it in a cell formula.

This work was completed, according to your request.  Unless you're having a bug or issues with this solution and your original request, my recommendation is you accept the appropriate posting for this question, submitted 3 weeks ago.

Please respond within 24 hours prior to automated moderator attention request.
---------------------------
@Crh61163

>>Once the comment has been moved to the new cell, I need the cell to have the comment indicator in the corner. Then put mouse over the cell to view the comment in that cell in the pop up comment window.

This is a revision of scope, and would require a rethink (your original question wanted the work done in a cell formula - this question requires a macro as a solution)

>>Using the above VLOOKUP command that looks up the customer information, is there a way to put a formula in the adjacent cell that will look up the comment associated with that customer and copies it to that cell for viewing if needed?

This solution, I believe was already provided - please provide additional information.  See tab "destination sheet", cell E5 pulls the comment information based on the VLOOKUP command already.

HAVE YOU REVIEWED the prior postings?
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36972514
Is this something like what you're looking for?

Hit the button to put comments in the corner.  The comments are now in the corner of the green cells.

Here's the code:
 
Sub putComment()
Dim wkb As Workbook
Dim wks As Worksheet
Dim firstAddress As String
Dim fRange As Range
Dim allComments As Range

    Set wkb = ActiveWorkbook
    Set wks = wkb.ActiveSheet
    
    Set fRange = wks.Cells.SpecialCells(xlCellTypeFormulas).Find(what:="getComment", LookIn:=xlFormulas, lookat:=xlPart) 'find where the comment functions are
    If Not fRange Is Nothing Then
        firstAddress = fRange.Address
        
        Do
            fRange.ClearComments
            fRange.AddComment
            fRange.Comment.Visible = False
            fRange.Comment.Text Text:=fRange.Value
            Set fRange = wks.Cells.FindNext(after:=fRange)
        
        Loop While Not fRange Is Nothing And firstAddress <> fRange.Address
    End If
End Sub

Open in new window



I'm not sure I got quite what you mean.  Please modify the attached workbook with some statements to demonstrate what you'd like to see.

See attached workbook.

Cheers,

Dave
lookupComment-r2.xls
0
 

Author Comment

by:Crh61663
ID: 36973180
I changed the example you sent to what I was thinking of. Sorry I wasn't very plain in what I needed.
Just needed the comment and indicator associated with the record selected to appear so the operator can review any special notes for that customer. Not all customers have comments associated with them.
Copy-of-lookupComment-NEEDED.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36974525
ok - like this?  

Dave


lookupComment-r3.xls
0
 

Author Comment

by:Crh61663
ID: 36992769
I have attached a listing of the program I'm working on. The order sheets 1-3 are examples of what I need. I need the comment indicator to appear in cell B8 on the ORDER sheets when data validation selects the customer name from the "Customers" sheet. That way the comment can be viewed by placing mouse over B8. But when printed. Sheets 1-3 have a password of "SAVED".

Thanks for your help
FG.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 36994094
Its very useful to have an example of what you're trying to do - spreadsheet mockups are great!  It helps you get your solutions faster.

This was a very good example and thanks.

I created code in ThisWorkbook, which looks at the sheet for the word ORDER - you can screen on just about anything, but this was in common with all 3 sheets.  Then, if the change was in cell C8 (that's what the Not Intersect(Target,Range("C8")) Is Nothing is about), it finds the customer in the CUSTOMERS tab, and fetches the comment, if any - then posts that comment to the left of the dropdown.

Here's the code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim fRange As Range
Dim custWks As Worksheet
Dim myComment As String

    Set custWks = ThisWorkbook.Sheets("CUSTOMERS")
    myComment = ""
    
    If Not Sh.Cells.Find(what:="ORDER SHEET", LookIn:=xlValues, lookat:=xlPart) Is Nothing Then 'look for the word ORDER on the worksheet
        If Target.Count = 1 Then  'only on single cell changes
            If Not Intersect(Target, Range("C8")) Is Nothing Then 'something changed in the drop down list
                'lookup the comment for the customer
                If Target.Value <> "" Then
                    With custWks.Range("A:A")
                        Set fRange = .Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole, after:=[A1], MatchCase:=False, matchbyte:=False, searchformat:=False)
                        If Not fRange Is Nothing Then 'customer is found, now get comment
                            myComment = fRange.Comment.Text
                        End If
                    End With
                End If
                With Target.Offset(0, -1)
                    .ClearComments
                    If myComment <> "" Then
                        .AddComment
                        .Comment.Visible = False
                        .Comment.Text Text:=myComment
                    End If
                End With
            End If
        End If
    End If
End Sub

Open in new window


See attached workbook - working example.

PS - YOU MUST allow Edit Objects in your worksheet protection, for the macro to work - otherwise, the macro itself would have to unprotect/reprotect the sheet.  If you need help on this (beyond just unprotect/reprotect sequence) ask a related question so you can get a thorough response on how all that works.


Cheers,

Dave
fg-r1.xls
0
 

Author Comment

by:Crh61663
ID: 37031608
Sorry for a lag in response, been out of town. Can this be put directly on the sheet? Even if there is another sheet change? Wouldn't that keep from having to check to see that it was the right sheet?
I added it to sheet-1, the password is "CHUCK". If cell "B8" is left locked to the user, the PW can be added to the SUB to unlock for change, then relocked? I have included file as is used.

Thanks
FG.xls
0
 

Author Comment

by:Crh61663
ID: 37037382
Well, after trying it in the place you suggested, I got it to work, except it has an error. If you change from a customer with a comment to one without a comment, it has a run error. And it doesn't work on every sheet. What am I doing wrong?

Thanks
FG.xls
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:Crh61663
ID: 37037458
I changed the look for characters to "-1-", since that is on every order sheet.
0
 

Author Comment

by:Crh61663
ID: 37038889
This is the line that causes the problem, only if there is not a comment to be found.
myComment = fRange.Comment.Text
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37039240
I know I put in a response yesterday and was surpeised its not here.  I won't be near my computer for a few hours and am aware of the error you identified.  Glad you are keeping the code at the workbook vs sheet lecel.

If memory serves, this is what needs to be done:

Before the with statement when frange comments are getting cleared then written:

Put this:

On error resume next
Activesheet.unprotect "pswd"
Then after the end with:

On error goto 0
Activeaheet protect "pswd"
That should make you good to go.  Ill monitor when back to my office and upload my latest if needed

Cheers

Dave
0
 

Author Comment

by:Crh61663
ID: 37039333
I figured it out. Thanks for your help.
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37039362
Very cool

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37039408
Ps. I just remembered you don't need the error trap

Just wrap all the comment related stuff in the with with

If not frange.comment is nothing then
'Comment stmts
End if


I likle to avoid using error traps generically if there's a better or more precise way to manage error

Cheers

Dave
0
 

Author Comment

by:Crh61663
ID: 37041695
Well, I have a problem when it goes to print with the 3-part option. When the order is finished, and ready to print, it copies each cell to the "PRINTQUE" sheet. When it gets to row-3, col-31, where the
"-1-" is that it looks for on the order pages, it has an error. What needs to be changed to get it to go through the whole page? Below is the line it stops on. I don't know if any other errors will occur.
Check out sheet2
If Not Intersect(Target, Range("C8")) Is Nothing Then 'something changed in the drop down list

Thanks
FG.xls
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37041729
For completeness, here's the work I did yesterday (I thought perhaps worksheets with name starting as "Sheet" would be good to key on - your keying on -1- works as you say, as well.

Here's the code:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim fRange As Range
Dim custWks As Worksheet
Dim myComment As String

    Set custWks = ThisWorkbook.Sheets("CUSTOMERS")
    myComment = ""
    
    If UCase(Left(Sh.Name, 5)) = "SHEET" Then 'look for the word ORDER on the worksheet
        If Target.Count = 1 Then  'only on single cell changes
            If Not Intersect(Target, Range("C8")) Is Nothing Then 'something changed in the drop down list
                'lookup the comment for the customer
                If Target.Value <> "" Then
                    With custWks.Range("A:A")
                        Set fRange = .Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole, after:=[A1], MatchCase:=False, matchbyte:=False, searchformat:=False)
                        If Not fRange Is Nothing Then 'customer is found, now get comment
                            If Not fRange.Comment Is Nothing Then
                                myComment = fRange.Comment.Text
                            End If
                        End If
                    End With
                End If
                With Target.Offset(0, -1)
                Sh.Unprotect "CHUCK"
                    .ClearComments
                    If myComment <> "" Then
                        .AddComment
                        .Comment.Visible = False
                        .Comment.Text Text:=myComment
                    End If
                Sh.Protect "CHUCK"
                End With
            End If
        End If
    End If
End Sub

Open in new window

Please advise if there's anything else, or close out the question as you see appropriate.

Cheers,

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37041766
Looks like our posts crossed.
Dave
0
 
LVL 41

Accepted Solution

by:
dlmille earned 125 total points
ID: 37041862
PS - I recommend one global variable dimensioned in one public module outside of a subroutine for your password (rather than on each sheet/ThisWorkbook codepage)..

E.g.,

Public myPW as string
or
Const myPW = "your password"

---------------------------------------------
There's some stuff to digest - as you need to make the decision about how you want things to behave - there are several ways to deal with this problem - I could just give you one, but the benefit of multiple I think will add richness to your app, and help head-off future problems - at least in this general arena - because this type of error CAN BE COMMON with the type of app you have - having lots of event triggers, etc...

So here we go...

When you run a macro that makes changes to a sheet and there's risk that an event might be triggered that's an unintended consequence, the general practice is to do is this:

Application.EnableEvents = False
'do your magic
Application.EnableEvents = True

And even better - to have an error trap such that if there is an error, you can re-enable events as part of the recovery, automatically.

You could also create a public variable (Public runningMacro as boolean) that each of your macros turn to TRUE when they run and back to FALSE when they're done.  Then just put that test in the Workbook_SheetChange event - to not do anything if runningMacro = TRUE.
For this particular error, this is not requred, though may be desired (you might actually be making changes to the sheet and not want this check for comments to be running.

Here's the most direct fix, but may not be the best fix for you - see the two additional proposals, above (as you're making a worksheet change on a DIFFERENT sheet than the active sheet - so Target is on the PRINTQUE sheet, while Range("C8") is on the active (or Sh) sheet of the Workbook_SheetChange event.  so I changed the reference to Range("C8") to Sh.Range("C8") - which ensures the INTERSECT command is working with two ranges on the same sheet (it errors if that is not the case, an unfortunate "feature" of Intersect, and actually Union as well).

 
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Dim fRange As Range
Dim custWks As Worksheet
Dim myComment As String
Dim MyPW As String
MyPW = "CHUCK"

    On Error GoTo errhandler 'to ensure sheet gets protected in case there's a failure after unprotecting for any reason
    
    Set custWks = ThisWorkbook.Sheets("CUSTOMERS")
    myComment = ""
    
    If Not Sh.Cells.Find(what:="-1-", LookIn:=xlValues, lookat:=xlPart) Is Nothing Then 'look for the word ORDER on the worksheet
        If Target.Count = 1 Then  'only on single cell changes
            If Not Intersect(Target, Sh.Range("C8")) Is Nothing Then 'something changed in the drop down list
                'lookup the comment for the customer
                If Target.Value <> "" Then
                    With custWks.Range("A:A")
                        Set fRange = .Find(what:=Target.Value, LookIn:=xlValues, lookat:=xlWhole, after:=[A1], MatchCase:=False, matchbyte:=False, searchformat:=False)
                            If Not fRange.Comment Is Nothing Then
                                myComment = fRange.Comment.Text
                            End If
                    End With
                End If
                ActiveSheet.Unprotect MyPW
                With Target.Offset(0, -1)
                    .ClearComments
                    If myComment <> "" Then
                        .AddComment
                        .Comment.Visible = False
                        .Comment.Text Text:=myComment
                    End If
                End With
                ActiveSheet.Protect MyPW
            End If
        End If
errhandler:
        If Not ActiveSheet.ProtectionMode Then ActiveSheet.Protect MyPW 'if its not protected, then protect before bailing
        On Error GoTo 0
    End If
End Sub

Open in new window


See attached,

Dave
FG-r2.xls
0
 

Author Comment

by:Crh61663
ID: 37044246
Looks like "On Error resume Next" fixed the problem.

Thanks again
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37045068
The problem was due to comparing Target and Range("C8") in the Intersect when Range("C8") was in a different worksheet - correcting the context with the prefix like:  Sh.Range("C8") should have fixed the problem, and I tested that.  No on error resume next should have been needed.

Did you copy my code over or just make mods to yours?

At any rate, looks like you've got it well in hand.

Dave
0
 
LVL 41

Expert Comment

by:dlmille
ID: 37045086
turning events off and back on when you'r running automation macros might be better as well (already mentioned) - and much better than setting an error trap - you really don't want workbook sheet_change events runnin if you're just printing sheets, etc., do you?

;)

Cheers,

Dave
0
 
LVL 50

Expert Comment

by:teylyn
ID: 37412260
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Drop Down List with Unique/Distinct Values (enhancing the Combo-Box with a few steps and a little code) David miller (dlmille) Intro Have you ever created a data validation list from a database field or spreadsheet column (e.g., Zip Codes or Co…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now