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!

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!

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!

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!

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!

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!

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!

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!

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!