Excel Macro to create Hyperlinks

Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

No Doll i

Thread Starter
Joined
Jun 26, 2005
Messages
8
I am building a spreadsheet for a data center move project and I want to build a macro that will automatically create a hyperlink each time I add a persons name into any event they are assigned to. There will be about 150 people on the project so it may have to be a very long macro. If anyone has any thoughts please let me know.

Thanks in advance!
 
Joined
Sep 4, 2003
Messages
4,915
Writing this macro should not be difficult at all. Can you please describe in a little more detail how you want this macro to work. Be sure to include whether the macro will be run automatically from a particular event or whether you plan to run it manually. Please give a detailed example of what you want to happen and how.


Rollin
 

No Doll i

Thread Starter
Joined
Jun 26, 2005
Messages
8
It is actually a really simple thing,

I have a master list titled "Contact" with a list of resources with all their information.

I have 34 sheets each representing days each. Each day is broken into hours. (I can send you a sample if you like) Each resource assigned to the move group is put into their time slot and that is where I want the link back to the contact sheet.

There is another sheet showing the weeks with resources and an X in the cells for the move groups they are assigned to. Ideally I would like to click the cell for the move group and jump to where they are listed on that sheet.

I am probably not explaining this as well as I should. Does this make any sense?

Thanks!
 
Joined
Sep 4, 2003
Messages
4,915
Can you attach a sample workbook to your next post? It's kinda hard to visualize what you are saying. Once I am able to see your sample it will be easier to help. Just click the "Manage Attachments" button at the bottom of your next post.

Rollin
 
Joined
Sep 4, 2003
Messages
4,915
OK, I'm working on the macro right now. What exactly do you want the macro to do? Do you want the macro to actually create the hyperlinks or do you want the macro to perform the same action as what the hyperlink would do? In other words, the macro can actually actually do the same thing as clicking on a hyperlink without actually creating a link. The code to perform these actions would be associated with the cell's Double Click event. Does that make sense?

Also, will your workbook ever change? Will the layout ever change or will the workbook always be exactly the same? This is important because small changes may affect the functionality of it. These changes could include changing the worksheet names, changing the selection character (X) on the assignments workbook, changing the abbreviations for the days of the week, deleting/inserting columns, etc.

The last thing to know is where the code will be stored. Will this code be attached to the actual workbooks or will it be attached to a second "Personal" workbook and executed on a seperate workbook?

Rollin
 

No Doll i

Thread Starter
Joined
Jun 26, 2005
Messages
8
Wow thanks! The macro needs to create the hyperlink and will be stored in the same workbook. I think I understand what you mean about the double click but if the macro actually creates the hyperlink rather than performing the same funtion as the hyperlink is this a moot point I am not certain or maybe I don't understand it as well as I think I do?

I do not forsee any changes to the sheets as far as the layout goes so hopefully that will not be an issue.

Thanks so much for helping me out with this!!
 
Joined
Sep 4, 2003
Messages
4,915
It's like this.....When you click on one of the cells containing the name or the symbol "X" you are taken to another location in the workbook because of the hyperlink associated with that cell. You don't really need the hyperlink when VBA code will essentially do the same thing when you double click on one of those same cells instead. If it doesn't matter to you, I would just have the code perform the actual action instead of creating a hyperlink.


Rollin
 

No Doll i

Thread Starter
Joined
Jun 26, 2005
Messages
8
Makes sense to me. How difficult will it be to write that code. I may be able to do it with some pointers although I am by no means someone that writes code.
 
Joined
Sep 4, 2003
Messages
4,915
It's not terribly difficult to write the code although the merged cells on some of the sheets will make things a little more challenging. I have already written most of the code and will re-post your workbook later today.

One last question, Will the merged cells containing the names on the Move Group worksheets (MG 1, MG2, etc.) always have a light blue background color? I need to know so that I can include logic to not perform any action if one of the non-colored cells are double-clicked.

Rollin
 
Joined
Sep 4, 2003
Messages
4,915
I've finished your macro but did not recieve a reply to my last post. I need to know about the cell coloring before I post the final solution.

Rollin
 

No Doll i

Thread Starter
Joined
Jun 26, 2005
Messages
8
Sorry it took so long to get back to you, been slammed at work.

I had intended to color code the spread sheets by job function but if it will make the macro more complicated I can do without the color or just make them all one color like the light blue.

I am open to suggestions.

Thanks again for your help!
 
Joined
Sep 4, 2003
Messages
4,915
Here is the modified sample workbook. I have removed all hyperlinks and added the VBA code to accomplish the same thing. Like I said before, any changes to the layout of the workbook will affect the functionality. Try it out and tell me if it does what you want.

Rollin
 

Attachments

No Doll i

Thread Starter
Joined
Jun 26, 2005
Messages
8
Thanks! It does just what I need it to. Now I have to make sure I don't make any changes to the various sheets and I should be good to go.

I owe ya dinner! Let me know if you are anywhere near North Carolina or plan to be on the east coast any time soon!

You can contact me via [email protected] and drop me your contact info.

Thanks again and I will let you know how this works out in the actual execution of this project!

Regards
Leslie aka no doll i
 
Status
This thread has been Locked and is not open to further replies. Please start a New Thread if you're having a similar issue. View our Welcome Guide to learn how to use this site.

Users Who Are Viewing This Thread (Users: 0, Guests: 1)

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 807,865 other people just like you!

Latest posts

Members online

Top