Hyperlink Help!

[Pug_canada]

I'm really hoping this hasn't been asked already, but I've searched everywhere and can't find an answer. I've reached the "ready to throw my computer out the window" stage because I know this can't be as difficult as it seems.

Basically, I'm building a spreadsheet for our sales team to be able to keep track of their prospects. I have a main page which has a column of prospect names, and some other columns with various different types of information.

I'd like to create a hyperlink that when you click the prospect name on the main worksheet (called "Prospect List"), it links to a cell on another sheet (called "Notes") in which you can enter notes (the Notes sheet has an identical list of prospects, and the note cell would be adjacent to the corresponding prospect) . The catch is that there are over 500 entries, so the code has to be dynamic so I can just drag it down the column. That's what I can't seem to get to work.

I hope my explanation wasn't too complicated. Any help would be GREATLY appreciated.

-Jamie

 

[Analyze_This]

Wouldn't it be easier to just create a section for them to write notes and then write a macro to take the notes and add them to appropriate section?

 

[Pug_canada]

That was actually my ideal solution, but I have no idea how to do it. I'm pretty good with the functions built into excel, but when it comes to writing macros, I'm useless. I've just started teaching myself VBA for the purposes of this project, and it's pretty tough to start from scratch.

I know how to build userforms, I'm just not sure what to do once I've got it built, and how to get it to drop the info into the appropriate cell.

 

[Analyze_This]

Instead of using forms, try using Modules. This is much easier and if you don't know how to do somthing, just hit the "Record Macro" button. It will record in a module and you can watch the code being written as you perform action in Excel.

This way, they can enteract with your spreadsheet normally and, once the macro is written, just assgin the macro to a button that they click when they want to perform the action.

 

[Analyze_This]

Your code might be something like this:

Dim ProspectName As String
Dim Notes As String
ProspectName = Range("A1")
Notes = Range("B2")
Sheets("Notes").Select
Range("A2").Select
Do
If ActiveCell = ProspectName Then
Range(Cells(ActiveCell.Row, 4)) = Notes
Else: ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)

 

[Pug_canada]

So bascially that code will make it such that if I perform an action to a given cell on the Prospect List sheet (Double Click/Right Click, etc...), I'll be taken to the appropriate cell on the Comments Page sheet. (it's not actually called notes... my mistake), is that correct?

So here's what I've done:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Dim ProspectList As String
Dim CommentsPage As String
ProspectList = Range("A4")
CommentsPage = Range("B2")
Sheets("Comments Page").Select
Range("B2").Select
Do
If ActiveCell = ProspectList Then
Range(Cells(ActiveCell.Row, 4)) = CommentsPage
Else: ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell)
End Sub

The problem is that when I right click on A4 in ProspectList, I get the following VBA error: "Select Range of Class Failed" and the debugger highlights the line

Range("B2").Select

Given my minimal VBA skills, I have no idea what that means.

Also, I'm assuming that with this code, it will only work with one particular cell on the ProspectList sheet. Is there a way to make it work for every cell in the range A4:A457?

Also, I need to make sure it works even if the cells are changed (i.e. if a row is inserted)

 

[Analyze_This]

I'm not sure why it would give that error unless it is trying to select merged cells from the middle. But this can be set to repeat as often as it is needed. Just set everything up to repeat in a DO loop until your range is empty. If you've written this much, you've got the skills to make it happen.

 

[Pug_canada]

Haha... you overestimate my skills. All I did was take what you did and modify it to fit my spreadsheet.

I'm pretty sure its nothing to do with merged cells since I have a pathological aversion to merging cells. (tends to screw up models)

I don't even know what a DO loop is.

Anyway, I'm sure I'm frustrating you with my denseness on this... thanks very much for your help. I don't want to waste too much of your time.

 

[Pug_canada]

Anyone else have any suggestions?