+ Reply to Thread
Results 1 to 13 of 13

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

  1. #1
    No News
    Guest

    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



  2. #2
    Scoops
    Guest

    Re: 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


  3. #3
    No News
    Guest

    Re: 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" <britwiz@hotmail.com> wrote in message
    news:1151664602.260028.32250@y41g2000cwy.googlegroups.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
    >




  4. #4
    Scoops
    Guest

    Re: 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


  5. #5
    No News
    Guest

    Re: 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" <britwiz@hotmail.com> wrote in message
    news:1151667000.364610.83560@m73g2000cwd.googlegroups.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
    >




  6. #6
    Scoops
    Guest

    Re: 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


  7. #7
    No News
    Guest

    Re: 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" <britwiz@hotmail.com> wrote in message
    news:1151672738.732355.274790@h44g2000cwa.googlegroups.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
    >




  8. #8
    Scoops
    Guest

    Re: 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


  9. #9
    No News
    Guest

    Re: 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" <britwiz@hotmail.com> wrote in message
    news:1151675541.163003.83620@p79g2000cwp.googlegroups.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
    >




  10. #10
    Scoops
    Guest

    Re: 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


  11. #11
    No News
    Guest

    Re: 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" <britwiz@hotmail.com> wrote in message
    news:1151677889.148516.68340@b68g2000cwa.googlegroups.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
    >




  12. #12
    Gord Dibben
    Guest

    Re: 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" <nonews@nonews.com> 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" <britwiz@hotmail.com> wrote in message
    >news:1151675541.163003.83620@p79g2000cwp.googlegroups.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

  13. #13
    No News
    Guest

    Re: 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
    news:6emaa2ps00os2mtf2u3mp4rubr6f8cd198@4ax.com...
    > 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" <nonews@nonews.com> 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" <britwiz@hotmail.com> wrote in message
    > >news:1151675541.163003.83620@p79g2000cwp.googlegroups.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




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1