Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink coding in VBA
I want to create a hyperlink to a cell I hae just put some updated text in,
here is my code... it runs down the first collumn looking to match a name, when found then puts the values in the cells. All fine, but now I want to add a hyperlink to that cell. This code is within a WITH block for the destination sheet. ActiveSheet.Hyperlinks.Add Anchor:=Sheets("Changes").Cells(y, 1), _ Address:=.Cells(yds, xds), TextToDisplay:=name I ended up with the link looking for file called EX0009 which is actually the contents of cell .Cells(yds,xds) What I actually want is a link to this cell. I may create hundreds of such links. TIA |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink coding in VBA
Graham,
Take a look at this sample code and you can adjust according to your needs. This will create a hyperlink in A1 of the activesheet that will take you to A1 of Sheet2. With ActiveSheet .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="Sheet2!A1", TextToDisplay:="Click Me!" End With -- Hope that helps. Vergel Adriano "Graham Y" wrote: I want to create a hyperlink to a cell I hae just put some updated text in, here is my code... it runs down the first collumn looking to match a name, when found then puts the values in the cells. All fine, but now I want to add a hyperlink to that cell. This code is within a WITH block for the destination sheet. ActiveSheet.Hyperlinks.Add Anchor:=Sheets("Changes").Cells(y, 1), _ Address:=.Cells(yds, xds), TextToDisplay:=name I ended up with the link looking for file called EX0009 which is actually the contents of cell .Cells(yds,xds) What I actually want is a link to this cell. I may create hundreds of such links. TIA |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink coding in VBA
Thanks
Am I going to have to convert my .cells() to a string value for the address? Is there an easy way to do that? In the past I have used... a = Asc("@") b = Chr(a + (x \ 26)) & Chr(a + (x Mod 26)) where x was my column number. ....to get the letters of the colum part of the address. and then concatenated this to the rest of the address. "Vergel Adriano" wrote: Graham, Take a look at this sample code and you can adjust according to your needs. This will create a hyperlink in A1 of the activesheet that will take you to A1 of Sheet2. With ActiveSheet .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="Sheet2!A1", TextToDisplay:="Click Me!" End With -- Hope that helps. Vergel Adriano "Graham Y" wrote: I want to create a hyperlink to a cell I hae just put some updated text in, here is my code... it runs down the first collumn looking to match a name, when found then puts the values in the cells. All fine, but now I want to add a hyperlink to that cell. This code is within a WITH block for the destination sheet. ActiveSheet.Hyperlinks.Add Anchor:=Sheets("Changes").Cells(y, 1), _ Address:=.Cells(yds, xds), TextToDisplay:=name I ended up with the link looking for file called EX0009 which is actually the contents of cell .Cells(yds,xds) What I actually want is a link to this cell. I may create hundreds of such links. TIA |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink coding in VBA
You can use the Address property:
..Cells(yds, xds).Address To make it include the workbook and sheet name, you can use: ..Cells(yds,xds).address(External:=True) -- Hope that helps. Vergel Adriano "Graham Y" wrote: Thanks Am I going to have to convert my .cells() to a string value for the address? Is there an easy way to do that? In the past I have used... a = Asc("@") b = Chr(a + (x \ 26)) & Chr(a + (x Mod 26)) where x was my column number. ...to get the letters of the colum part of the address. and then concatenated this to the rest of the address. "Vergel Adriano" wrote: Graham, Take a look at this sample code and you can adjust according to your needs. This will create a hyperlink in A1 of the activesheet that will take you to A1 of Sheet2. With ActiveSheet .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="Sheet2!A1", TextToDisplay:="Click Me!" End With -- Hope that helps. Vergel Adriano "Graham Y" wrote: I want to create a hyperlink to a cell I hae just put some updated text in, here is my code... it runs down the first collumn looking to match a name, when found then puts the values in the cells. All fine, but now I want to add a hyperlink to that cell. This code is within a WITH block for the destination sheet. ActiveSheet.Hyperlinks.Add Anchor:=Sheets("Changes").Cells(y, 1), _ Address:=.Cells(yds, xds), TextToDisplay:=name I ended up with the link looking for file called EX0009 which is actually the contents of cell .Cells(yds,xds) What I actually want is a link to this cell. I may create hundreds of such links. TIA |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink coding in VBA
I had tried using .Address, but failed. The cell is on a different worksheet
is in the same file. just using .Cells(yds,xds).Address created a link the right cell but on the same sheet as the hyperlink, not quite what I was after. even using SubAddress:=Sheets("Detail Sheet").Cells(yds, xds).Address points to a cell on the "Changes" sheet wher ethe hyperlink is. Using External:=True works, but it seems a bit excessive to make it think it is an external link. Thanks, for the help and quick responses. "Vergel Adriano" wrote: You can use the Address property: .Cells(yds, xds).Address To make it include the workbook and sheet name, you can use: .Cells(yds,xds).address(External:=True) -- Hope that helps. Vergel Adriano "Graham Y" wrote: Thanks Am I going to have to convert my .cells() to a string value for the address? Is there an easy way to do that? In the past I have used... a = Asc("@") b = Chr(a + (x \ 26)) & Chr(a + (x Mod 26)) where x was my column number. ...to get the letters of the colum part of the address. and then concatenated this to the rest of the address. "Vergel Adriano" wrote: Graham, Take a look at this sample code and you can adjust according to your needs. This will create a hyperlink in A1 of the activesheet that will take you to A1 of Sheet2. With ActiveSheet .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="Sheet2!A1", TextToDisplay:="Click Me!" End With -- Hope that helps. Vergel Adriano "Graham Y" wrote: I want to create a hyperlink to a cell I hae just put some updated text in, here is my code... it runs down the first collumn looking to match a name, when found then puts the values in the cells. All fine, but now I want to add a hyperlink to that cell. This code is within a WITH block for the destination sheet. ActiveSheet.Hyperlinks.Add Anchor:=Sheets("Changes").Cells(y, 1), _ Address:=.Cells(yds, xds), TextToDisplay:=name I ended up with the link looking for file called EX0009 which is actually the contents of cell .Cells(yds,xds) What I actually want is a link to this cell. I may create hundreds of such links. TIA |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink coding in VBA
For your purpose, you'll need to set External=True. If you don't like doing
it that way, you can try: SubAddress:= "'" & .Name & "'!" & .Cells(yds, xds).Address or like this SubAddress:="'Detail Sheet'!" & .Cells(yds, xds).Address -- Hope that helps. Vergel Adriano "Graham Y" wrote: I had tried using .Address, but failed. The cell is on a different worksheet is in the same file. just using .Cells(yds,xds).Address created a link the right cell but on the same sheet as the hyperlink, not quite what I was after. even using SubAddress:=Sheets("Detail Sheet").Cells(yds, xds).Address points to a cell on the "Changes" sheet wher ethe hyperlink is. Using External:=True works, but it seems a bit excessive to make it think it is an external link. Thanks, for the help and quick responses. "Vergel Adriano" wrote: You can use the Address property: .Cells(yds, xds).Address To make it include the workbook and sheet name, you can use: .Cells(yds,xds).address(External:=True) -- Hope that helps. Vergel Adriano "Graham Y" wrote: Thanks Am I going to have to convert my .cells() to a string value for the address? Is there an easy way to do that? In the past I have used... a = Asc("@") b = Chr(a + (x \ 26)) & Chr(a + (x Mod 26)) where x was my column number. ...to get the letters of the colum part of the address. and then concatenated this to the rest of the address. "Vergel Adriano" wrote: Graham, Take a look at this sample code and you can adjust according to your needs. This will create a hyperlink in A1 of the activesheet that will take you to A1 of Sheet2. With ActiveSheet .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="Sheet2!A1", TextToDisplay:="Click Me!" End With -- Hope that helps. Vergel Adriano "Graham Y" wrote: I want to create a hyperlink to a cell I hae just put some updated text in, here is my code... it runs down the first collumn looking to match a name, when found then puts the values in the cells. All fine, but now I want to add a hyperlink to that cell. This code is within a WITH block for the destination sheet. ActiveSheet.Hyperlinks.Add Anchor:=Sheets("Changes").Cells(y, 1), _ Address:=.Cells(yds, xds), TextToDisplay:=name I ended up with the link looking for file called EX0009 which is actually the contents of cell .Cells(yds,xds) What I actually want is a link to this cell. I may create hundreds of such links. TIA |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Hyperlink coding in VBA
Thank you,
"Vergel Adriano" wrote: For your purpose, you'll need to set External=True. If you don't like doing it that way, you can try: SubAddress:= "'" & .Name & "'!" & .Cells(yds, xds).Address or like this SubAddress:="'Detail Sheet'!" & .Cells(yds, xds).Address -- Hope that helps. Vergel Adriano "Graham Y" wrote: I had tried using .Address, but failed. The cell is on a different worksheet is in the same file. just using .Cells(yds,xds).Address created a link the right cell but on the same sheet as the hyperlink, not quite what I was after. even using SubAddress:=Sheets("Detail Sheet").Cells(yds, xds).Address points to a cell on the "Changes" sheet wher ethe hyperlink is. Using External:=True works, but it seems a bit excessive to make it think it is an external link. Thanks, for the help and quick responses. "Vergel Adriano" wrote: You can use the Address property: .Cells(yds, xds).Address To make it include the workbook and sheet name, you can use: .Cells(yds,xds).address(External:=True) -- Hope that helps. Vergel Adriano "Graham Y" wrote: Thanks Am I going to have to convert my .cells() to a string value for the address? Is there an easy way to do that? In the past I have used... a = Asc("@") b = Chr(a + (x \ 26)) & Chr(a + (x Mod 26)) where x was my column number. ...to get the letters of the colum part of the address. and then concatenated this to the rest of the address. "Vergel Adriano" wrote: Graham, Take a look at this sample code and you can adjust according to your needs. This will create a hyperlink in A1 of the activesheet that will take you to A1 of Sheet2. With ActiveSheet .Hyperlinks.Add Anchor:=.Range("A1"), Address:="", SubAddress:="Sheet2!A1", TextToDisplay:="Click Me!" End With -- Hope that helps. Vergel Adriano "Graham Y" wrote: I want to create a hyperlink to a cell I hae just put some updated text in, here is my code... it runs down the first collumn looking to match a name, when found then puts the values in the cells. All fine, but now I want to add a hyperlink to that cell. This code is within a WITH block for the destination sheet. ActiveSheet.Hyperlinks.Add Anchor:=Sheets("Changes").Cells(y, 1), _ Address:=.Cells(yds, xds), TextToDisplay:=name I ended up with the link looking for file called EX0009 which is actually the contents of cell .Cells(yds,xds) What I actually want is a link to this cell. I may create hundreds of such links. TIA |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
"=ROW()-1" type of coding doesn't appear in a filter / is there coding that does? | Excel Programming | |||
Implant macro coding into ASP coding | Excel Programming | |||
Coding a button or hyperlink to insert a predfined row | Excel Programming | |||
Hyperlink coding? | Excel Programming | |||
Coding a HyperLink | Excel Programming |