hyperlink

LadyHarper

New Member
Joined
Jun 28, 2017
Messages
48
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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.
 
Upvote 0
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:
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
When I select debug it highlights this line
Worksheets("Sheet1").Cells(Application.WorksheetFunction.Match(Target, Worksheets("Sheet1").Range("D2:D2886"), 0) + 1, 1).Select
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,245
Members
448,555
Latest member
RobertJones1986

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top