Refering to a link in a cell

[Pasty]

Hi there,

I have a column which has lots of difference references to statements on a
different sheet and when click on it fires up user forms and stuff for them
to fill in which is all fine and dandy. Problem I'm having is I want to add
another one asking them whether the statement is correct if yes then it exits
the sub and if its no then it will take them to the workbook and the relevant
cell in order to let them edit the source.

This is an example of one of the links it is sat in E2:
='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195

How would I get it to read this as a link and then load it up to the
relevant cell?

 

[Tom Ogilvy]

You would parse out the various parts of the reference, particularly the
workbook name and the path which are delineated with the square brackets

s = Replace(Cell.formula,"=","")
iRightBrac = Instr(1,s,"]",vbTextcompare)
sName = Replace(left(s,iRightBrac-1),"[","")

here is a demo from the immediate window:

s1 = "='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195"
s = replace(s1,"=","")
iRightBrack = Instr(1,s,"]",vbTextCompare)
sName = Replace(Replace(left(s,irightBrack-1),"[",""),"'","")
? sName
http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement
of Applicability.xls

Continue to get the remainder of the information you need using a similar
approach.

 

[Pasty]

Ah I see - I'm still very much a novice and you've baffled me with names I
have never heard of.

You would parse out the various parts of the reference, particularly the
workbook name and the path which are delineated with the square brackets

s = Replace(Cell.formula,"=","")
iRightBrac = Instr(1,s,"]",vbTextcompare)
sName = Replace(left(s,iRightBrac-1),"[","")

here is a demo from the immediate window:

s1 = "='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195"
s = replace(s1,"=","")
iRightBrack = Instr(1,s,"]",vbTextCompare)
sName = Replace(Replace(left(s,irightBrack-1),"[",""),"'","")
? sName
http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement
of Applicability.xls

Continue to get the remainder of the information you need using a similar
approach.

--
Regards,
Tom Ogilvy

Hi there,

I have a column which has lots of difference references to statements on a
different sheet and when click on it fires up user forms and stuff for them
to fill in which is all fine and dandy. Problem I'm having is I want to add
another one asking them whether the statement is correct if yes then it exits
the sub and if its no then it will take them to the workbook and the relevant
cell in order to let them edit the source.

This is an example of one of the links it is sat in E2:
='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195

How would I get it to read this as a link and then load it up to the
relevant cell?

 

[Tom Ogilvy]

Put them in a module, highlight the name are not familiar with, hit F1.

--
Regards,
Tom Ogilvy

Ah I see - I'm still very much a novice and you've baffled me with names I
have never heard of.

You would parse out the various parts of the reference, particularly the
workbook name and the path which are delineated with the square brackets

s = Replace(Cell.formula,"=","")
iRightBrac = Instr(1,s,"]",vbTextcompare)
sName = Replace(left(s,iRightBrac-1),"[","")

here is a demo from the immediate window:

s1 = "='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195"
s = replace(s1,"=","")
iRightBrack = Instr(1,s,"]",vbTextCompare)
sName = Replace(Replace(left(s,irightBrack-1),"[",""),"'","")
? sName
http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement
of Applicability.xls

Continue to get the remainder of the information you need using a similar
approach.

--
Regards,
Tom Ogilvy

Hi there,

I have a column which has lots of difference references to statements on a
different sheet and when click on it fires up user forms and stuff for them
to fill in which is all fine and dandy. Problem I'm having is I want to add
another one asking them whether the statement is correct if yes then it exits
the sub and if its no then it will take them to the workbook and the relevant
cell in order to let them edit the source.

This is an example of one of the links it is sat in E2:
='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195

How would I get it to read this as a link and then load it up to the
relevant cell?

 

[Tom Ogilvy]

You might want to look at the follow hyperlink command as well.

--
Regards,
Tom Ogilvy

Ah I see - I'm still very much a novice and you've baffled me with names I
have never heard of.

You would parse out the various parts of the reference, particularly the
workbook name and the path which are delineated with the square brackets

s = Replace(Cell.formula,"=","")
iRightBrac = Instr(1,s,"]",vbTextcompare)
sName = Replace(left(s,iRightBrac-1),"[","")

here is a demo from the immediate window:

s1 = "='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195"
s = replace(s1,"=","")
iRightBrack = Instr(1,s,"]",vbTextCompare)
sName = Replace(Replace(left(s,irightBrack-1),"[",""),"'","")
? sName
http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement
of Applicability.xls

Continue to get the remainder of the information you need using a similar
approach.

--
Regards,
Tom Ogilvy

Hi there,

I have a column which has lots of difference references to statements on a
different sheet and when click on it fires up user forms and stuff for them
to fill in which is all fine and dandy. Problem I'm having is I want to add
another one asking them whether the statement is correct if yes then it exits
the sub and if its no then it will take them to the workbook and the relevant
cell in order to let them edit the source.

This is an example of one of the links it is sat in E2:
='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195

How would I get it to read this as a link and then load it up to the
relevant cell?

 

[Pasty]

This is brilliant thankyou, it appears to be working but I can't get it to
add " either side of sName in order to make it open the workbook,

Here is the code I am using if it helps.

Sub goToSoa()

Dim s As String, iRightBrac As String, sName As String, s1 As String

s = Replace(ActiveCell.Formula, "=", "")
iRightBrac = InStr(1, s, "]", vbTextCompare)
sName = Replace(Left(s, iRightBrac - 1), "[", "")
frmYesNo2.Show

If frmYesNo2.rdNo = True Then Exit Sub

If frmYesNo2.rdYes = True Then
Workbooks.Open Filename:=sName


End If

End Sub

You might want to look at the follow hyperlink command as well.

--
Regards,
Tom Ogilvy

Ah I see - I'm still very much a novice and you've baffled me with names I
have never heard of.

You would parse out the various parts of the reference, particularly the
workbook name and the path which are delineated with the square brackets

s = Replace(Cell.formula,"=","")
iRightBrac = Instr(1,s,"]",vbTextcompare)
sName = Replace(left(s,iRightBrac-1),"[","")

here is a demo from the immediate window:

s1 = "='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195"
s = replace(s1,"=","")
iRightBrack = Instr(1,s,"]",vbTextCompare)
sName = Replace(Replace(left(s,irightBrack-1),"[",""),"'","")
? sName
http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement
of Applicability.xls

Continue to get the remainder of the information you need using a similar
approach.

--
Regards,
Tom Ogilvy



:

Hi there,

I have a column which has lots of difference references to statements on a
different sheet and when click on it fires up user forms and stuff for them
to fill in which is all fine and dandy. Problem I'm having is I want to add
another one asking them whether the statement is correct if yes then it exits
the sub and if its no then it will take them to the workbook and the relevant
cell in order to let them edit the source.

This is an example of one of the links it is sat in E2:
='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195

How would I get it to read this as a link and then load it up to the
relevant cell?

 

[Tom Ogilvy]

Try it without the form to see that it works:

Sub goToSoa()
Dim s As String, iRightBrac As String, sName As String, s1 As String
s = Replace(ActiveCell.Formula, "=", "")
iRightBrac = InStr(1, s, "]", vbTextCompare)
sName = Replace(Left(s, iRightBrac - 1), "[", "")
Workbooks.Open Filename:=sName
End Sub

Usually if you open a useform, code execution stops until the form is
dropped. If you unload the userform, then you can' t access the value of any
of the controls. If you hide the userform, then you can. I can't see/know
anything associated with your userform, so I can not suggest how to read and
interpret the decisions made by your user using the userform - but if you are
not getting the expected reaction to their choices in your code, then you
probably need to relook the logic of your code.

--
Regards,
Tom Ogilvy

This is brilliant thankyou, it appears to be working but I can't get it to
add " either side of sName in order to make it open the workbook,

Here is the code I am using if it helps.

Sub goToSoa()

Dim s As String, iRightBrac As String, sName As String, s1 As String

s = Replace(ActiveCell.Formula, "=", "")
iRightBrac = InStr(1, s, "]", vbTextCompare)
sName = Replace(Left(s, iRightBrac - 1), "[", "")
frmYesNo2.Show

If frmYesNo2.rdNo = True Then Exit Sub

If frmYesNo2.rdYes = True Then
Workbooks.Open Filename:=sName


End If

End Sub

You might want to look at the follow hyperlink command as well.

--
Regards,
Tom Ogilvy

Ah I see - I'm still very much a novice and you've baffled me with names I
have never heard of.

:

You would parse out the various parts of the reference, particularly the
workbook name and the path which are delineated with the square brackets

s = Replace(Cell.formula,"=","")
iRightBrac = Instr(1,s,"]",vbTextcompare)
sName = Replace(left(s,iRightBrac-1),"[","")

here is a demo from the immediate window:

s1 = "='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195"
s = replace(s1,"=","")
iRightBrack = Instr(1,s,"]",vbTextCompare)
sName = Replace(Replace(left(s,irightBrack-1),"[",""),"'","")
? sName
http://knet/sites/fapas/risk/InformationSecurity/IS Documents/Plan/Statement
of Applicability.xls

Continue to get the remainder of the information you need using a similar
approach.

--
Regards,
Tom Ogilvy



:

Hi there,

I have a column which has lots of difference references to statements on a
different sheet and when click on it fires up user forms and stuff for them
to fill in which is all fine and dandy. Problem I'm having is I want to add
another one asking them whether the statement is correct if yes then it exits
the sub and if its no then it will take them to the workbook and the relevant
cell in order to let them edit the source.

This is an example of one of the links it is sat in E2:
='http://knet/sites/fapas/risk/InformationSecurity/IS
Documents/Plan/[Statement of Applicability.xls]Annex A and New SOA'!I195

How would I get it to read this as a link and then load it up to the
relevant cell?