Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
Is there any way to accomplish the following?
I have a workbook containing multiple worksheets. Col A in all worksheets contains a series of numbers that are unique: Wksht 1 Wksht 2 Col A Col A 126345 995346 678910 798394 How can I get Excel to prompt the user for a # in Col A and, based on a match, open JUST that worksheet. Thanks for any/all help! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
I suppose sample of my code can be helpful:
Sub FindInWorkbook(ColA_WantedValue As Long) 'Sample code - Mario C. (MaC) 'use it free everywhere 'this subroutine seeks wanted value in all worksheets of your workbook Dim Wst As Worksheet Dim ColA_Counter As Long For Each Wst In ThisWorkbook.Worksheets For ColA_Counter = 2 To Wst.Range("A65536").End(xlUp).Row If Wst.Range("A" & ColA_Counter).Value = ColA_WantedValue Then MsgBox "Wanted value (" & ColA_WantedValue & ") exists in worksheet " & Wst.Name Wst.Activate Exit Sub End If Next Next End Sub Sub AskUserAndFind() 'now we can call above sub with user prompt as a argument Call FindInWorkbook(InputBox("What is the wanted value in column A?")) End Sub Good luck Mario C (MaC) Użytkownik "JN" napisał w wiadomości ... Is there any way to accomplish the following? I have a workbook containing multiple worksheets. Col A in all worksheets contains a series of numbers that are unique: Wksht 1 Wksht 2 Col A Col A 126345 995346 678910 798394 How can I get Excel to prompt the user for a # in Col A and, based on a match, open JUST that worksheet. Thanks for any/all help! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
I take it that you have sheets named those numbers. I also take it that you
want the user to select from the list in the sheet he is looking at. There are several ways you can do this. One way is to setup a Data Validation cell (also called a drop-down cell) in each sheet that displays the list on that sheet. It would be convenient if the cell address of that cell is the same in each sheet. For the following macro, I chose E1 as that cell address. The user clicks the down-arrow on that cell and the list is displayed. The user scrolls the list until he finds the number he wants and clicks on that number. The following macro will then fire by itself and the appropriate sheet is selected. This macro is a workbook macro and belongs in the workbook module. To access that module, right-click on the Excel icon that is to the left of the "File" in the menu across the top of the screen, select View Code, and paste this macro into the displayed module. Click on the "X" at the top right of the screen and you will be returned to your spreadsheet. HTH Otto Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Address(0, 0) = "E1" Then Sheets(CStr(Target.Value)).Select End Sub "JN" wrote in message ... Is there any way to accomplish the following? I have a workbook containing multiple worksheets. Col A in all worksheets contains a series of numbers that are unique: Wksht 1 Wksht 2 Col A Col A 126345 995346 678910 798394 How can I get Excel to prompt the user for a # in Col A and, based on a match, open JUST that worksheet. Thanks for any/all help! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
Actually, the worksheets are named Wksht1 & Wksht2. I was looking for a user
prompt (similar to Access query user prompt) that would ask for the #s found in Col A and then, based on that information, open just that worksheet. Example: Before spreadsheet opens: User prompt: (user types: 995346) Wksht 2 would open. Didn't know if I could do this in Excel or if I would have to use Access. "Otto Moehrbach" wrote: I take it that you have sheets named those numbers. I also take it that you want the user to select from the list in the sheet he is looking at. There are several ways you can do this. One way is to setup a Data Validation cell (also called a drop-down cell) in each sheet that displays the list on that sheet. It would be convenient if the cell address of that cell is the same in each sheet. For the following macro, I chose E1 as that cell address. The user clicks the down-arrow on that cell and the list is displayed. The user scrolls the list until he finds the number he wants and clicks on that number. The following macro will then fire by itself and the appropriate sheet is selected. This macro is a workbook macro and belongs in the workbook module. To access that module, right-click on the Excel icon that is to the left of the "File" in the menu across the top of the screen, select View Code, and paste this macro into the displayed module. Click on the "X" at the top right of the screen and you will be returned to your spreadsheet. HTH Otto Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Address(0, 0) = "E1" Then Sheets(CStr(Target.Value)).Select End Sub "JN" wrote in message ... Is there any way to accomplish the following? I have a workbook containing multiple worksheets. Col A in all worksheets contains a series of numbers that are unique: Wksht 1 Wksht 2 Col A Col A 126345 995346 678910 798394 How can I get Excel to prompt the user for a # in Col A and, based on a match, open JUST that worksheet. Thanks for any/all help! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
I assume you haven't tried my code, have you?
MaC Użytkownik "JN" napisał w wiadomości ... Actually, the worksheets are named Wksht1 & Wksht2. I was looking for a user prompt (similar to Access query user prompt) that would ask for the #s found in Col A and then, based on that information, open just that worksheet. Example: Before spreadsheet opens: User prompt: (user types: 995346) Wksht 2 would open. Didn't know if I could do this in Excel or if I would have to use Access. "Otto Moehrbach" wrote: I take it that you have sheets named those numbers. I also take it that you want the user to select from the list in the sheet he is looking at. There are several ways you can do this. One way is to setup a Data Validation cell (also called a drop-down cell) in each sheet that displays the list on that sheet. It would be convenient if the cell address of that cell is the same in each sheet. For the following macro, I chose E1 as that cell address. The user clicks the down-arrow on that cell and the list is displayed. The user scrolls the list until he finds the number he wants and clicks on that number. The following macro will then fire by itself and the appropriate sheet is selected. This macro is a workbook macro and belongs in the workbook module. To access that module, right-click on the Excel icon that is to the left of the "File" in the menu across the top of the screen, select View Code, and paste this macro into the displayed module. Click on the "X" at the top right of the screen and you will be returned to your spreadsheet. HTH Otto Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Address(0, 0) = "E1" Then Sheets(CStr(Target.Value)).Select End Sub "JN" wrote in message ... Is there any way to accomplish the following? I have a workbook containing multiple worksheets. Col A in all worksheets contains a series of numbers that are unique: Wksht 1 Wksht 2 Col A Col A 126345 995346 678910 798394 How can I get Excel to prompt the user for a # in Col A and, based on a match, open JUST that worksheet. Thanks for any/all help! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
I think you're saying you have 2 sheets. Each sheet has a set of numbers
that are peculiar to that sheet. You want the user to type in a number and you want Excel to activate (select) the sheet that holds that number. Is that correct? When do you want this to happen? That is, when do you want this query to pop up? When you open the file? When you click on a button? HTH Otto "JN" wrote in message ... Actually, the worksheets are named Wksht1 & Wksht2. I was looking for a user prompt (similar to Access query user prompt) that would ask for the #s found in Col A and then, based on that information, open just that worksheet. Example: Before spreadsheet opens: User prompt: (user types: 995346) Wksht 2 would open. Didn't know if I could do this in Excel or if I would have to use Access. "Otto Moehrbach" wrote: I take it that you have sheets named those numbers. I also take it that you want the user to select from the list in the sheet he is looking at. There are several ways you can do this. One way is to setup a Data Validation cell (also called a drop-down cell) in each sheet that displays the list on that sheet. It would be convenient if the cell address of that cell is the same in each sheet. For the following macro, I chose E1 as that cell address. The user clicks the down-arrow on that cell and the list is displayed. The user scrolls the list until he finds the number he wants and clicks on that number. The following macro will then fire by itself and the appropriate sheet is selected. This macro is a workbook macro and belongs in the workbook module. To access that module, right-click on the Excel icon that is to the left of the "File" in the menu across the top of the screen, select View Code, and paste this macro into the displayed module. Click on the "X" at the top right of the screen and you will be returned to your spreadsheet. HTH Otto Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Address(0, 0) = "E1" Then Sheets(CStr(Target.Value)).Select End Sub "JN" wrote in message ... Is there any way to accomplish the following? I have a workbook containing multiple worksheets. Col A in all worksheets contains a series of numbers that are unique: Wksht 1 Wksht 2 Col A Col A 126345 995346 678910 798394 How can I get Excel to prompt the user for a # in Col A and, based on a match, open JUST that worksheet. Thanks for any/all help! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
The following macro will do what you want. For this macro to work, you must
name the range of numbers in WkSht1 "Range1" and the range of numbers in WkSht2 "Range2". This macro will run when the file is opened. It will prompt the user to enter a number. If the entry is not a valid number a message box will say so. This will be repeated until a valid number is entered. This macro will activate (select) that sheet that contains the number entered. If the number entered cannot be found, a message box will so state and ask the user if he wants to enter another number. If the user clicks on the Yes button, the original query for a number will be repeated. If he clicks on the No button, the file will open on the page that was active when the file was last saved. Note that this macro is a Workbook event macro and must be placed in the Workbook module. To access that module, right-click on the Excel icon that is immediately left of the word "File" in the menu across the top of the sheet. Select View Code. Paste this macro into that module. Save the file. Open the file to fire the macro. HTH Otto Private Sub Workbook_Open() Dim Num As Double Dim Ans As Long TryAgain: Num = Application.InputBox(Prompt:="Enter a number.", Title:="Enter Number", Type:=1) If Not Range("Range1").Find(What:=Num, LookAt:=xlWhole) Is Nothing Then Sheets("WkSht1").Activate Exit Sub End If If Not Range("Range2").Find(What:=Num, LookAt:=xlWhole) Is Nothing Then Sheets("WkSht2").Activate Exit Sub End If Ans = MsgBox("The number " & Num & " could not be found." & Chr(13) & _ "Do you wish to enter another number?", 4, "Number Not Found") If Ans = vbYes Then GoTo TryAgain End Sub "JN" wrote in message ... Actually, the worksheets are named Wksht1 & Wksht2. I was looking for a user prompt (similar to Access query user prompt) that would ask for the #s found in Col A and then, based on that information, open just that worksheet. Example: Before spreadsheet opens: User prompt: (user types: 995346) Wksht 2 would open. Didn't know if I could do this in Excel or if I would have to use Access. "Otto Moehrbach" wrote: I take it that you have sheets named those numbers. I also take it that you want the user to select from the list in the sheet he is looking at. There are several ways you can do this. One way is to setup a Data Validation cell (also called a drop-down cell) in each sheet that displays the list on that sheet. It would be convenient if the cell address of that cell is the same in each sheet. For the following macro, I chose E1 as that cell address. The user clicks the down-arrow on that cell and the list is displayed. The user scrolls the list until he finds the number he wants and clicks on that number. The following macro will then fire by itself and the appropriate sheet is selected. This macro is a workbook macro and belongs in the workbook module. To access that module, right-click on the Excel icon that is to the left of the "File" in the menu across the top of the screen, select View Code, and paste this macro into the displayed module. Click on the "X" at the top right of the screen and you will be returned to your spreadsheet. HTH Otto Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Address(0, 0) = "E1" Then Sheets(CStr(Target.Value)).Select End Sub "JN" wrote in message ... Is there any way to accomplish the following? I have a workbook containing multiple worksheets. Col A in all worksheets contains a series of numbers that are unique: Wksht 1 Wksht 2 Col A Col A 126345 995346 678910 798394 How can I get Excel to prompt the user for a # in Col A and, based on a match, open JUST that worksheet. Thanks for any/all help! |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
It works well, but it will work only if you have two worksheets (or other
known number of sheets). Of course if you add next worksheet you can rebuild your macro, but it's rather not flexible. Check sample code I gave a few posts before. MaC Użytkownik "Otto Moehrbach" napisał w wiadomości ... The following macro will do what you want. For this macro to work, you must name the range of numbers in WkSht1 "Range1" and the range of numbers in WkSht2 "Range2". This macro will run when the file is opened. It will prompt the user to enter a number. If the entry is not a valid number a message box will say so. This will be repeated until a valid number is entered. This macro will activate (select) that sheet that contains the number entered. If the number entered cannot be found, a message box will so state and ask the user if he wants to enter another number. If the user clicks on the Yes button, the original query for a number will be repeated. If he clicks on the No button, the file will open on the page that was active when the file was last saved. Note that this macro is a Workbook event macro and must be placed in the Workbook module. To access that module, right-click on the Excel icon that is immediately left of the word "File" in the menu across the top of the sheet. Select View Code. Paste this macro into that module. Save the file. Open the file to fire the macro. HTH Otto Private Sub Workbook_Open() Dim Num As Double Dim Ans As Long TryAgain: Num = Application.InputBox(Prompt:="Enter a number.", Title:="Enter Number", Type:=1) If Not Range("Range1").Find(What:=Num, LookAt:=xlWhole) Is Nothing Then Sheets("WkSht1").Activate Exit Sub End If If Not Range("Range2").Find(What:=Num, LookAt:=xlWhole) Is Nothing Then Sheets("WkSht2").Activate Exit Sub End If Ans = MsgBox("The number " & Num & " could not be found." & Chr(13) & _ "Do you wish to enter another number?", 4, "Number Not Found") If Ans = vbYes Then GoTo TryAgain End Sub "JN" wrote in message ... Actually, the worksheets are named Wksht1 & Wksht2. I was looking for a user prompt (similar to Access query user prompt) that would ask for the #s found in Col A and then, based on that information, open just that worksheet. Example: Before spreadsheet opens: User prompt: (user types: 995346) Wksht 2 would open. Didn't know if I could do this in Excel or if I would have to use Access. "Otto Moehrbach" wrote: I take it that you have sheets named those numbers. I also take it that you want the user to select from the list in the sheet he is looking at. There are several ways you can do this. One way is to setup a Data Validation cell (also called a drop-down cell) in each sheet that displays the list on that sheet. It would be convenient if the cell address of that cell is the same in each sheet. For the following macro, I chose E1 as that cell address. The user clicks the down-arrow on that cell and the list is displayed. The user scrolls the list until he finds the number he wants and clicks on that number. The following macro will then fire by itself and the appropriate sheet is selected. This macro is a workbook macro and belongs in the workbook module. To access that module, right-click on the Excel icon that is to the left of the "File" in the menu across the top of the screen, select View Code, and paste this macro into the displayed module. Click on the "X" at the top right of the screen and you will be returned to your spreadsheet. HTH Otto Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Address(0, 0) = "E1" Then Sheets(CStr(Target.Value)).Select End Sub "JN" wrote in message ... Is there any way to accomplish the following? I have a workbook containing multiple worksheets. Col A in all worksheets contains a series of numbers that are unique: Wksht 1 Wksht 2 Col A Col A 126345 995346 678910 798394 How can I get Excel to prompt the user for a # in Col A and, based on a match, open JUST that worksheet. Thanks for any/all help! |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Query
Where is the sample code? I am bombing on the other macro - I have many
worksheets that I will have to deal with (all contained within the same workbook) - The worksheets contain lists of unique #'s - each worksheet has a different list of unique #'s. the macro needs to prompt the user for a # (unique #) that resides in Col G of one of these worksheets and, based on that unique #, open just that worksheet where the unique # resides. Sorry about the problems! "MaC" wrote: It works well, but it will work only if you have two worksheets (or other known number of sheets). Of course if you add next worksheet you can rebuild your macro, but it's rather not flexible. Check sample code I gave a few posts before. MaC UÂżytkownik "Otto Moehrbach" napisaÂł w wiadomoÂści ... The following macro will do what you want. For this macro to work, you must name the range of numbers in WkSht1 "Range1" and the range of numbers in WkSht2 "Range2". This macro will run when the file is opened. It will prompt the user to enter a number. If the entry is not a valid number a message box will say so. This will be repeated until a valid number is entered. This macro will activate (select) that sheet that contains the number entered. If the number entered cannot be found, a message box will so state and ask the user if he wants to enter another number. If the user clicks on the Yes button, the original query for a number will be repeated. If he clicks on the No button, the file will open on the page that was active when the file was last saved. Note that this macro is a Workbook event macro and must be placed in the Workbook module. To access that module, right-click on the Excel icon that is immediately left of the word "File" in the menu across the top of the sheet. Select View Code. Paste this macro into that module. Save the file. Open the file to fire the macro. HTH Otto Private Sub Workbook_Open() Dim Num As Double Dim Ans As Long TryAgain: Num = Application.InputBox(Prompt:="Enter a number.", Title:="Enter Number", Type:=1) If Not Range("Range1").Find(What:=Num, LookAt:=xlWhole) Is Nothing Then Sheets("WkSht1").Activate Exit Sub End If If Not Range("Range2").Find(What:=Num, LookAt:=xlWhole) Is Nothing Then Sheets("WkSht2").Activate Exit Sub End If Ans = MsgBox("The number " & Num & " could not be found." & Chr(13) & _ "Do you wish to enter another number?", 4, "Number Not Found") If Ans = vbYes Then GoTo TryAgain End Sub "JN" wrote in message ... Actually, the worksheets are named Wksht1 & Wksht2. I was looking for a user prompt (similar to Access query user prompt) that would ask for the #s found in Col A and then, based on that information, open just that worksheet. Example: Before spreadsheet opens: User prompt: (user types: 995346) Wksht 2 would open. Didn't know if I could do this in Excel or if I would have to use Access. "Otto Moehrbach" wrote: I take it that you have sheets named those numbers. I also take it that you want the user to select from the list in the sheet he is looking at. There are several ways you can do this. One way is to setup a Data Validation cell (also called a drop-down cell) in each sheet that displays the list on that sheet. It would be convenient if the cell address of that cell is the same in each sheet. For the following macro, I chose E1 as that cell address. The user clicks the down-arrow on that cell and the list is displayed. The user scrolls the list until he finds the number he wants and clicks on that number. The following macro will then fire by itself and the appropriate sheet is selected. This macro is a workbook macro and belongs in the workbook module. To access that module, right-click on the Excel icon that is to the left of the "File" in the menu across the top of the screen, select View Code, and paste this macro into the displayed module. Click on the "X" at the top right of the screen and you will be returned to your spreadsheet. HTH Otto Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) If Target.Count 1 Then Exit Sub If IsEmpty(Target.Value) Then Exit Sub If Target.Address(0, 0) = "E1" Then Sheets(CStr(Target.Value)).Select End Sub "JN" wrote in message ... Is there any way to accomplish the following? I have a workbook containing multiple worksheets. Col A in all worksheets contains a series of numbers that are unique: Wksht 1 Wksht 2 Col A Col A 126345 995346 678910 798394 How can I get Excel to prompt the user for a # in Col A and, based on a match, open JUST that worksheet. Thanks for any/all help! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Convert hard coded query criteria to Parameter Query | Excel Discussion (Misc queries) | |||
Excel 2007 / MS Query - editing existing query to another sheet | Excel Discussion (Misc queries) | |||
Importing Data via Web Query - Can values be passed to query? | Excel Discussion (Misc queries) | |||
Microsoft Query rejects "nz" function in Access Query | Excel Discussion (Misc queries) | |||
How to use a Access Query that as a parameter into Excel database query | Excel Discussion (Misc queries) |