selecting a single sheet from a volume of sheets in a workbook
 

Hi All,

I have 30 and above sheets in a work book and like that I am having 5 such
books. The sheets are named as 201, 202, 203 ....etc as per the contents in
that particular sheets. (201, 202 .....are the P.O nos.). all the work
sheets are of having similar format of datas.

Now what I need is if I want to look the details of one single sheet (say
324) I have go all the sheets one by one and it is hard to find out.

If any body give me a solution so that if I type a particular no. (forms
part of the name of the sheet) that sheet should appear for me.

Help please.

TT

selecting a single sheet from a volume of sheets in a workbook
 

No News wrote:

If any body give me a solution so that if I type a particular no. (forms
part of the name of the sheet) that sheet should appear for me.

Hi No News

If your really want to do it this way, add this code to each sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo nosheet
If Target.Address = "$A$1" Then
Sheets(Range("A1").Value).Activate
End If
Exit Sub

nosheet:
MsgBox ("No sheet with the name " & Range("A1").Value & " found")
End Sub

Amend the $A$1 to the cell you want to use.

Alternatively, you could right-click the navigation arrows at the
bottom left of the screen (next to Sheet1 in a new workbook) and select
your sheet from there.

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

Dear Steeve.

Thanks for your immediate reply.

For your 2nd option. Since I have more than 100 Sheets It is difficult for
me to select

For your 1st option:- Since I was not so familier with excel, I do not know
where to add these codes to each sheet. Can you explain please.

Thanks in advance.


"Scoops" wrote in message
ups.com...

No News wrote:

If any body give me a solution so that if I type a particular no. (forms
part of the name of the sheet) that sheet should appear for me.

Hi No News

If your really want to do it this way, add this code to each sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo nosheet
If Target.Address = "$A$1" Then
Sheets(Range("A1").Value).Activate
End If
Exit Sub

nosheet:
MsgBox ("No sheet with the name " & Range("A1").Value & " found")
End Sub

Amend the $A$1 to the cell you want to use.

Alternatively, you could right-click the navigation arrows at the
bottom left of the screen (next to Sheet1 in a new workbook) and select
your sheet from there.

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

No News wrote:
Dear Steeve.

For your 1st option:- Since I was not so familier with excel, I do not
know
where to add these codes to each sheet. Can you explain please.


Hi No News

Pasting the code into 100 sheets would be painful itself, so:

Press Alt+F11 to open the vb editor.

Click Insert Module.

Paste the following code into the window that opens:

Sub SheetFind()
Dim mySheet As String

On Error GoTo nosheet
mySheet = InputBox("Please enter the Sheet name to activate", "GoTo
Sheet")
Sheets(mySheet).Activate
Exit Sub

nosheet:
MsgBox ("No sheet with the name " & mySheet & " found")
End Sub


***End of code***

The Input box isn't very elegant but functional.

To run the macro:

From the Excel toolbar click Tools Macro Macros SheetFind.

You might want to investigate placing a custom button on your toolbar
and assigning the macro to it so that it runs with a single click
(right-click a toolbar and Customize...)

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

Dear Steve,

When running the macro it stops at the following line with error

Dim mySheet As String

Please advise what todo




"Scoops" wrote in message
ups.com...

No News wrote:
Dear Steeve.

For your 1st option:- Since I was not so familier with excel, I do not
know
where to add these codes to each sheet. Can you explain please.


Hi No News

Pasting the code into 100 sheets would be painful itself, so:

Press Alt+F11 to open the vb editor.

Click Insert Module.

Paste the following code into the window that opens:

Sub SheetFind()
Dim mySheet As String

On Error GoTo nosheet
mySheet = InputBox("Please enter the Sheet name to activate", "GoTo
Sheet")
Sheets(mySheet).Activate
Exit Sub

nosheet:
MsgBox ("No sheet with the name " & mySheet & " found")
End Sub


***End of code***

The Input box isn't very elegant but functional.

To run the macro:

From the Excel toolbar click Tools Macro Macros SheetFind.

You might want to investigate placing a custom button on your toolbar
and assigning the macro to it so that it runs with a single click
(right-click a toolbar and Customize...)

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

No News wrote:
Dear Steve,

When running the macro it stops at the following line with error

Dim mySheet As String

Please advise what todo

Hi No News

The error is in this line (Isuspect that it is in red in your code):

mySheet = InputBox("Please enter the Sheet name to activate",
"GoTo
Sheet")

The problem has come from the formatting of the line in the posting.

Make sure that "GoTo Sheet") is on the same line, not split as in the
original post and remove the " (double quote) that the vba editor will
have added after the last close bracket - it should be "GoTo Sheet")
not "GoTo Sheet ") "

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

Well Steve. That works well. Thanks.

But now I want to have a menu button on the toolbar. But I could not follow
the information provided by you as below. I can get - Right click on the
tool bar - Cutomise - Then what to do

"
You might want to investigate placing a custom button on your toolbar
and assigning the macro to it so that it runs with a single click
(right-click a toolbar and Customize...)
"
Help me pls.



"Scoops" wrote in message
oups.com...

No News wrote:
Dear Steve,

When running the macro it stops at the following line with error

Dim mySheet As String

Please advise what todo

Hi No News

The error is in this line (Isuspect that it is in red in your code):

mySheet = InputBox("Please enter the Sheet name to activate",
"GoTo
Sheet")

The problem has come from the formatting of the line in the posting.

Make sure that "GoTo Sheet") is on the same line, not split as in the
original post and remove the " (double quote) that the vba editor will
have added after the last close bracket - it should be "GoTo Sheet")
not "GoTo Sheet ") "

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

No News wrote:
Help me pls.

Hi No News

With the sheet containing the macro open:

Right-click the toolbar Customize

Click the Command tab

Scroll down the Categories and click Macros

Drag the Smiley face onto the toolbar of your choice

Right-click the Smiley Assign Macro

Click SheetFind, click OK, click Close.

Click the Smiley to run the macro.

If you want to remove the Smiley, Customize and drag it back into the
dialog box.

You can also change the way it looks: Customize, right-click Change
Button Image.

Have fun.

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

Fantastic Steve.
It has helped ver..................ry much. and thanks a lot.

If any further help is needed I can contact you. and Can I introduce your
name to my various friends of beginers to learn more.

Thanks and regards,
No News.


"Scoops" wrote in message
ups.com...

No News wrote:
Help me pls.

Hi No News

With the sheet containing the macro open:

Right-click the toolbar Customize

Click the Command tab

Scroll down the Categories and click Macros

Drag the Smiley face onto the toolbar of your choice

Right-click the Smiley Assign Macro

Click SheetFind, click OK, click Close.

Click the Smiley to run the macro.

If you want to remove the Smiley, Customize and drag it back into the
dialog box.

You can also change the way it looks: Customize, right-click Change
Button Image.

Have fun.

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

No News wrote:
Fantastic Steve.
It has helped ver..................ry much. and thanks a lot.

If any further help is needed I can contact you. and Can I introduce your
name to my various friends of beginers to learn more.

Thanks and regards,
No News.

Hi No News

Thanks for the feedback.

I'm not very often able to view and help out on these newsgroups (it's
a slow Friday afternoon at work) but there are always plenty of people
here who willing to help (and a lot of them with much greater expertise
than me), so just keep posting and practising and you'll be fine.

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

Thank you steve.

Well noted your advise.

And let GOD's grace be with all of us.

Thanks and regards,
No News

"Scoops" wrote in message
ups.com...

No News wrote:
Fantastic Steve.
It has helped ver..................ry much. and thanks a lot.

If any further help is needed I can contact you. and Can I introduce
your
name to my various friends of beginers to learn more.

Thanks and regards,
No News.

Hi No News

Thanks for the feedback.

I'm not very often able to view and help out on these newsgroups (it's
a slow Friday afternoon at work) but there are always plenty of people
here who willing to help (and a lot of them with much greater expertise
than me), so just keep posting and practising and you'll be fine.

Regards

Steve

selecting a single sheet from a volume of sheets in a workbook
 

Now that you know how/where to place a macro, try this one from Bob Phillips

Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption


Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add
With thisDlg
.Name = sID
.Visible = xlSheetHidden
'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If
iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i
.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24
CurrentSheet.Activate
With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If
Application.DisplayAlerts = False
.Delete
End With
End Sub


Gord Dibben MS Excel MVP




On Fri, 30 Jun 2006 19:42:44 +0530, "No News" wrote:

Fantastic Steve.
It has helped ver..................ry much. and thanks a lot.

If any further help is needed I can contact you. and Can I introduce your
name to my various friends of beginers to learn more.

Thanks and regards,
No News.


"Scoops" wrote in message
oups.com...

No News wrote:
Help me pls.

Hi No News

With the sheet containing the macro open:

Right-click the toolbar Customize

Click the Command tab

Scroll down the Categories and click Macros

Drag the Smiley face onto the toolbar of your choice

Right-click the Smiley Assign Macro

Click SheetFind, click OK, click Close.

Click the Smiley to run the macro.

If you want to remove the Smiley, Customize and drag it back into the
dialog box.

You can also change the way it looks: Customize, right-click Change
Button Image.

Have fun.

Regards

Steve



Gord Dibben MS Excel MVP

selecting a single sheet from a volume of sheets in a workbook
 

Dear Gord Dibben

There you are.

As I am in India, I could able to see your message today only and it works
for me to meed my needs in a highly sophisticated angle and it is somthing
wonderfull.

Welldone. and thank you for your help.

These type of messages are the root of the newgroups.

Thanks.



"Gord Dibben" <gorddibbATshawDOTca wrote in message
...
Now that you know how/where to place a macro, try this one from Bob
Phillips

Sub BrowseSheets()
Const nPerColumn As Long = 38 'number of items per column
Const nWidth As Long = 13 'width of each letter
Const nHeight As Long = 18 'height of each row
Const sID As String = "___SheetGoto" 'name of dialog sheet
Const kCaption As String = " Select sheet to goto"
'dialog caption


Dim i As Long
Dim TopPos As Long
Dim iBooks As Long
Dim cCols As Long
Dim cLetters As Long
Dim cMaxLetters As Long
Dim cLeft As Long
Dim thisDlg As DialogSheet
Dim CurrentSheet As Worksheet
Dim cb As OptionButton
Application.ScreenUpdating = False
If ActiveWorkbook.ProtectStructure Then
MsgBox "Workbook is protected.", vbCritical
Exit Sub
End If
On Error Resume Next
Application.DisplayAlerts = False
ActiveWorkbook.DialogSheets(sID).Delete
Application.DisplayAlerts = True
On Error GoTo 0
Set CurrentSheet = ActiveSheet
Set thisDlg = ActiveWorkbook.DialogSheets.Add
With thisDlg
.Name = sID
.Visible = xlSheetHidden
'sets variables for positioning on dialog
iBooks = 0
cCols = 0
cMaxLetters = 0
cLeft = 78
TopPos = 40
For i = 1 To ActiveWorkbook.Worksheets.Count
If i Mod nPerColumn = 1 Then
cCols = cCols + 1
TopPos = 40
cLeft = cLeft + (cMaxLetters * nWidth)
cMaxLetters = 0
End If
Set CurrentSheet = ActiveWorkbook.Worksheets(i)
cLetters = Len(CurrentSheet.Name)
If cLetters cMaxLetters Then
cMaxLetters = cLetters
End If
iBooks = iBooks + 1
.OptionButtons.Add cLeft, TopPos, cLetters * nWidth, 16.5
.OptionButtons(iBooks).text = _
ActiveWorkbook.Worksheets(iBooks).Name
TopPos = TopPos + 13
Next i
.Buttons.Left = cLeft + (cMaxLetters * nWidth) + 24
CurrentSheet.Activate
With .DialogFrame
.Height = Application.Max(68, _
Application.Min(iBooks, nPerColumn) * nHeight + 10)
.Width = cLeft + (cMaxLetters * nWidth) + 24
.Caption = kCaption
End With
.Buttons("Button 2").BringToFront
.Buttons("Button 3").BringToFront
Application.ScreenUpdating = True
If .Show Then
For Each cb In thisDlg.OptionButtons
If cb.Value = xlOn Then
ActiveWorkbook.Worksheets(cb.Caption).Select
Exit For
End If
Next cb
Else
MsgBox "Nothing selected"
End If
Application.DisplayAlerts = False
.Delete
End With
End Sub


Gord Dibben MS Excel MVP




On Fri, 30 Jun 2006 19:42:44 +0530, "No News" wrote:

Fantastic Steve.
It has helped ver..................ry much. and thanks a lot.

If any further help is needed I can contact you. and Can I introduce your
name to my various friends of beginers to learn more.

Thanks and regards,
No News.


"Scoops" wrote in message
oups.com...

No News wrote:
Help me pls.

Hi No News

With the sheet containing the macro open:

Right-click the toolbar Customize

Click the Command tab

Scroll down the Categories and click Macros

Drag the Smiley face onto the toolbar of your choice

Right-click the Smiley Assign Macro

Click SheetFind, click OK, click Close.

Click the Smiley to run the macro.

If you want to remove the Smiley, Customize and drag it back into the
dialog box.

You can also change the way it looks: Customize, right-click Change
Button Image.

Have fun.

Regards

Steve



Gord Dibben MS Excel MVP