Solved

Insert Cell Link in Excel

Posted on 2009-05-04
7
806 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
  • 3
  • 3
7 Comments
 
LVL 20

Expert Comment

by:pari123
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 20

Expert Comment

by:pari123
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:
pari123 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
excel pivot question 4 40
Excel - list cell contents that are not duplicated 4 30
Child Form in front 4 35
Excel 2016 - Black cell borders 11 26
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

930 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now