hyperlink

LadyHarper

New Member
Joined
Jun 28, 2017
Messages
30
I have a spreadsheet with over 2000 comments in it. I have used VBA coding to create a new sheet that has all the comments on it. I now want to make it so when I click on a cell in the main spreadsheet that has a comment in it, it takes me to the matching cell number listed in the column that states all the cell's with comments in them.
 

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,615
Hello,

you need to have a Worksheet_SelectionChange macro. However, not entirely clear what cell you are trying to go to (from you last sentence). Can you clarify please. I get that if you click on say B6, and it has a comment you want to go somewhere, but cannot follow your logic.
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
LadyHarper,

You can setup an event on your Main Worksheet to run every time you select a cell with your comment in it. In my code, I set my range of comments to "A2:A8", so you would change that range to whatever columns and rows your comments are in. Also, change "NewSheet" to the name of your sheet with all the comments on it.
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheets("NewSheet").Activate
    If Worksheets("NewSheet").Range("A2:A8").Find(Target) Is Nothing Then   'The Range should be your comment range on the other sheet
                                                                            'No comment matched
    Else
        Worksheets("NewSheet").Cells(Application.WorksheetFunction.Match(Target, Worksheets("NewSheet").Range("A2:A8"), 0) + 1, 1).Select
                                                                                              'The +1 to account for a header
    End If
End Sub
Once again, this code is an object in your Main Worksheet only
 
Last edited:

LadyHarper

New Member
Joined
Jun 28, 2017
Messages
30
Ok so the Current VBA code I am using took all 2885 comments and created "Sheet1" tab the headers are Sheet, Address, Value, Comment.
So when you select lets say cell R7, and it has a comment in it, I want it to goto "Sheet!" search ColumnB (Address) for R7 (the sell I selected). This way it takes me right to that comment.
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
Edited code with your sheet name and the correct range of comments
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Worksheets("Sheet1").Activate
    If Worksheets("Sheet1").Range("D2:D2886").Find(Target) Is Nothing Then   'The Range should be your comment range on the other sheet
                                                                            'No comment matched
    Else
        Worksheets("Sheet1").Cells(Application.WorksheetFunction.Match(Target, Worksheets("Sheet1").Range("D2:D2886"), 0) + 1, 1).Select
                                                                                              'The +1 to account for a header
    End If
End Sub
 

LadyHarper

New Member
Joined
Jun 28, 2017
Messages
30
Nickred18

to clarify ("newsheet") is the original sheet or (Sheet1) the second tab I created? and the range would be my B2:B2885 on the second tab (Sheet1) and yes there are headers on sheet1.
 

LadyHarper

New Member
Joined
Jun 28, 2017
Messages
30
NickRed18, thank you for re-writing the code, do I need to remove your green comments? or do anything else because right now if I click on a cell on main worksheet that has a comment it is not doing anything. Thank you so much for your continued help.
 

NickRed18

Board Regular
Joined
May 25, 2017
Messages
76
No you do not need to remove the green comments, unless you want to. The reason it may not be working is it's located in the wrong spot. Open up VBA and in the top left window, it lists your "Projects". In this window, find your main sheet name, and double click it. Paste the exact code into this location and it should work.

Let me know if it doesn't.

-Nick
 
Last edited:

LadyHarper

New Member
Joined
Jun 28, 2017
Messages
30
Run-time error '1004':
Unable to get the match property of the worksheetfunction class
A step closer, it is going to that sheet now, just not to the M11 in cell B2:B2885
 

LadyHarper

New Member
Joined
Jun 28, 2017
Messages
30
When I select debug it highlights this line
Worksheets("Sheet1").Cells(Application.WorksheetFunction.Match(Target, Worksheets("Sheet1").Range("D2:D2886"), 0) + 1, 1).Select
 

Forum statistics

Threads
1,077,827
Messages
5,336,613
Members
399,092
Latest member
jbwatkins

Some videos you may like

This Week's Hot Topics

Top