Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Open a worksheet by refrencing a number in 1 cell
Is it opssible to write a formula that will link to open a tab without
clicking on a hyperlink.? For instance I will have 3 pages in my workbook. I will be using a program that will scan a number into cell A1. Once the number is in A1 there will be a matching page with that number and I want that page to automatically open. So when 15556453 is entered in A1 it will open page 15556453 and display what is on that sheet. I will have page 3 that refrences a differnt number 16987543, when this number is entered into A1 on the first page it will then open 16987543. Is there a way to have a function open sheet and display what is on it? Thanks! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Open a worksheet by refrencing a number in 1 cell
Not possible with a formula to activate another worksheet.
You could possibly do it with VBA event code but I don't know if scanning a value into a cell triggers a change event Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" Then Sheets(Target.Value).Activate End If End Sub This is worksheet event code. Right-click on the sheet tab and "View Code" Copy/paste the code into that module. Edit to suit then Alt + q to return to Excel. Gord Dibben MS Excel MVP On Thu, 5 Nov 2009 12:24:01 -0800, HAD wrote: Is it opssible to write a formula that will link to open a tab without clicking on a hyperlink.? For instance I will have 3 pages in my workbook. I will be using a program that will scan a number into cell A1. Once the number is in A1 there will be a matching page with that number and I want that page to automatically open. So when 15556453 is entered in A1 it will open page 15556453 and display what is on that sheet. I will have page 3 that refrences a differnt number 16987543, when this number is entered into A1 on the first page it will then open 16987543. Is there a way to have a function open sheet and display what is on it? Thanks! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Open a worksheet by refrencing a number in 1 cell
Select the sheet tab which you want to work with. Right click the sheet tab
and click on 'View Code'. This will launch VBE. Paste the below code to the right blank portion. Get back to to workbook and try out entering sheet names in cell A1 of this sheet. 'the below code is to be copied to the sheet module Private Sub Worksheet_Change(ByVal Target As Range) If Target.Address = "$A$1" And Trim(Target.Text) < "" Then If SheetExists(Target.Text) Then Sheets(Target.Text).Activate End If End Sub Function SheetExists(ShName As String) As Boolean On Error Resume Next Set varSheet = Sheets(ShName) If Err.Number < 0 Then SheetExists = False Else SheetExists = True End Function 'upto here If this post helps click Yes --------------- Jacob Skaria "HAD" wrote: Is it opssible to write a formula that will link to open a tab without clicking on a hyperlink.? For instance I will have 3 pages in my workbook. I will be using a program that will scan a number into cell A1. Once the number is in A1 there will be a matching page with that number and I want that page to automatically open. So when 15556453 is entered in A1 it will open page 15556453 and display what is on that sheet. I will have page 3 that refrences a differnt number 16987543, when this number is entered into A1 on the first page it will then open 16987543. Is there a way to have a function open sheet and display what is on it? Thanks! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Refrencing cell formulas in a new worksheet | Excel Worksheet Functions | |||
ASSIGNING A NEW NUMBER TO A WORKSHEET EVERY TIME IT IS OPEN | Excel Worksheet Functions | |||
Refrencing a variable cell in a formula | Excel Discussion (Misc queries) | |||
Refrencing an entire worksheet? | Excel Discussion (Misc queries) | |||
Refrencing another cell in a worksheet that "could" exist | Excel Worksheet Functions |