Solved

Insert Cell Link in Excel

Posted on 2009-05-04
7
810 Views
Last Modified: 2013-12-26
Hello all.

I have an excel spreadsheet with 3 columns of information.  I have another column with hyperlinks that execute macros.  Is it possible to select an area inside the Information cell range that I want to insert a row and push the 3 cells of information down a cell?

0
Comment
Question by:SchMoke
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 3
7 Comments
 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 24299027
>>> Is it possible to select an area inside the Information cell range that I want to insert a row and push the 3 cells of information down a cell?

Yes, it is absolutely possible and there are also many ways to get this done. Here's one way that you can try:

After you select the desired cells you can use something similar.


Range("A2:C2".select
Selection.Insert Shift:=xlDown

This will move the cells down. To add a hyperlink you can use something like this:

    Range("A1").Select
    ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "http://www.yahoo.com", TextToDisplay:="LINK"

- Ardhendu

0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 24299033
Highlight the three cells you want to move down and right mouse click.  On the sub menu that displays select Insert.  In the dialog box that displays select Move Cells Down.
0
 

Author Comment

by:SchMoke
ID: 24299255
Hey pari123, that is close to what i need but lets say I have in A4 the value "Dog" and in A6 the value "Cat".  I want to be able to click on A5 and then click the link and it will push "Cat" to A7.  Can the link do that?
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 20

Expert Comment

by:Ardhendu Sarangi
ID: 24299335

If you want to insert a link in A5, then your code will look something similar to this...

   Range("A5").select
   Selection.Insert Shift:=xlDown
   ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
        "http://www.yahoo.com", TextToDisplay:="LINK"

This will shift only the values in Column A down and insert a link in A5.

- Ardhendu
0
 

Author Comment

by:SchMoke
ID: 24299388
Ahhhh so how do I pass the Active cell to


Range("whatever cell i click on").select

because I may want to insert a space in another row in the column
0
 
LVL 20

Accepted Solution

by:
Ardhendu Sarangi earned 250 total points
ID: 24299535
If you want to click a cell, then you can try something like this .....

But remember that this will work only on one cell to insert the hyperlink.

- Ardhendu

Sub Newcode()
Dim rngX As Range
 
Set rngX = Application.InputBox(Prompt:="Please click on a cell with your mouse to insert the link.", _
                    Title:="SPECIFY RANGE", Type:=8)
rngX.Select
  Selection.Insert Shift:=xlDown
  ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:= _
       "http://www.yahoo.com", TextToDisplay:="LINK"
End Sub

Open in new window

0
 

Author Comment

by:SchMoke
ID: 24299601
Worked like a charm!
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

729 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