Solved

Insert Cell Link in Excel

Posted on 2009-05-04
7
807 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
ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

 
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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

770 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