Solved: Splitting all addresses in a formula
Hi All,
I am currently, as a personal project to understand Userforms better, I am trying to develop the following as a userForm thru VBA:
Develop a shortcut whereby clicking on any cell, a Userform opens up to show all internal and external link addresses.
The Userform allows one to go directly to those displayed addresses (whether they be in internal or external workbooks).
For example, Consider a a sample workbook called C:\Documents\Test.xls (Which contains 3 sheets - Sheet1, Sheet2 and Sheet3)
In Cell A1 of Sheet1 of this workbook, contans the following formula (an intentionally random and long formula is chosen below, but only the various types of internal/ external addresses are important):
Quote:
=IF(ISERROR(VLOOKUP($A$2,$B$2:$C$10,$D$2,FALSE)),Sheet2!A1,IF(ISNA(Sheet3!$A$1),VLOOKUP('C:\Documents and Settings\ExcelTestFiles\[Book2]Sheet1!$A$1,'C:\Documents and Settings\VBAX\[Book2]Sheet1!$B$1:$D$17,'C:\Important\Results\[Book2]Sheet2!A$1,FALSE)))+'C:\TestFiles\[Book3]Sheet1!$A$1
Out of this example, I would like to split out the links as follows ( I have colour coded as per the formula above):
Internal Links
i.e. links within the Activecell in Activeworkbook (C:\Documents\Test.xls)- C:\Documents\[Test.xls]Sheet1'!$A$2
- C:\Documents\[Test.xls]Sheet1'!$B$2:$C$10
- C:\Documents\[Test.xls]Sheet1'!$D$2
- C:\Documents\[Test.xls]Sheet2'!A1
- C:\Documents\[Test.xls]Sheet3'!$A$1
- C:\Documents\[Test.xls]Sheet3'!$A$1
External Links i.e. from External workbook sources in the Activecell in Activeworkbook (C:\Documents\Test.xls)- 'C:\Documents and Settings\ExcelTestFiles\[Book2]Sheet1!$A$1
- 'C:\Documents and Settings\VBAX\[Book2]Sheet1!$B$1:$D$17
- 'C:\Important\Results\[Book2]Sheet2!A$1
- 'C:\TestFiles\[Book3]Sheet1!$A$1
THUS, as per my original question, is there any way to easily extract all the internal and external addresses from an active cell and in particular from the above example i.e. treating the formula as a string and parsing internal/ external links accordingly.
(There will be other problems to consider e.g. if the links are open then only short path will be visible i.e. formula will read '[Book2]Sheet1!$B$1:$D$17 instead of 'C:\Documents and Settings\VBAX\[Book2]Sheet1!$B$1:$D$17), but for now assuming that all external links are closed (as above) is there any way to split out the links and paths?
Any VBA Guru help would be much appreciated :).
regards
Edit: Corrected spelling errors and modified final question for more clarity.