Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refering to a link in a cell
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refering to a link in a cell
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 "Pasty" wrote: 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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refering to a link in a cell
Ah I see - I'm still very much a novice and you've baffled me with names I
have never heard of. "Tom Ogilvy" wrote: 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 "Pasty" wrote: 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? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refering to a link in a cell
Put them in a module, highlight the name are not familiar with, hit F1.
-- Regards, Tom Ogilvy "Pasty" wrote: Ah I see - I'm still very much a novice and you've baffled me with names I have never heard of. "Tom Ogilvy" wrote: 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 "Pasty" wrote: 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? |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refering to a link in a cell
You might want to look at the follow hyperlink command as well.
-- Regards, Tom Ogilvy "Pasty" wrote: Ah I see - I'm still very much a novice and you've baffled me with names I have never heard of. "Tom Ogilvy" wrote: 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 "Pasty" wrote: 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? |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refering to a link in a cell
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 "Tom Ogilvy" wrote: You might want to look at the follow hyperlink command as well. -- Regards, Tom Ogilvy "Pasty" wrote: Ah I see - I'm still very much a novice and you've baffled me with names I have never heard of. "Tom Ogilvy" wrote: 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 "Pasty" wrote: 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? |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Refering to a link in a cell
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 "Pasty" wrote: 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 "Tom Ogilvy" wrote: You might want to look at the follow hyperlink command as well. -- Regards, Tom Ogilvy "Pasty" wrote: Ah I see - I'm still very much a novice and you've baffled me with names I have never heard of. "Tom Ogilvy" wrote: 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 "Pasty" wrote: 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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refering cell to the worksheet name | Excel Worksheet Functions | |||
Refering to a sheet in a cell | Excel Discussion (Misc queries) | |||
REFERING TO CELL IN DIFFERENT SHEET | Excel Discussion (Misc queries) | |||
Refering a cell | Excel Worksheet Functions | |||
Refering to an empty cell | Excel Discussion (Misc queries) |