+ Reply to Thread
Results 1 to 39 of 39

Macro to find sheet name with next button option in case of multiple sheet with same name.

  1. #1
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Macro to find sheet name with next button option in case of multiple sheet with same name.

    Hi All,

    Pls help in macro to find sheet name in excel with next button option in case of multiple sheet with same name. Found similar macro in below link but it is not showing next option in case of multiple sheet with same name. Can anyone help me in resolving the same?


    http://www.excelforum.com/excel-prog...e-2-print.html
    Last edited by DEEPAK AGGARWAL; 04-02-2016 at 06:39 AM.

  2. #2
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Unable to open link. Perhaps you should upload sample workbook with code in question.
    Alan עַם יִשְׂרָאֵל חַי


    Change an Ugly Report with Power Query
    Database Normalization
    Complete Guide to Power Query
    Man's Mind Stretched to New Dimensions Never Returns to Its Original Form

  3. #3
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Hi,

    Thanks for response. Pls find attach sample workbook with code in question sheet.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Bump no response.

  5. #5
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Pls revert.

  6. #6
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    I have looked at your workbook and honestly, I have no idea from your explanation and the workbook what you wish to happen. Please explain in simple English as if were standing in line for tickets to the theatre what you have and what you wish to accomplish.

  7. #7
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    How to execute below code i.e. "Macro to find the sheet name"? Can you pls add Forum Guru "HaHoBe" in this conversation?


    Code behind the Userform:
    Code:
    Option Explicit

    Private Sub CommandButton1_Click()
    With ListBox1
    If .ListIndex = -1 Then Exit Sub
    Sheets(.List(.ListIndex)).Activate
    .RemoveItem .ListIndex
    End With
    TextBox1.Value = vbNullString
    End Sub

    Private Sub CommandButton2_Click()
    Dim ws As Worksheet
    If Len(TextBox1.Value) > 0 Then
    With ListBox1
    .Clear
    For Each ws In Worksheets
    If InStr(1, ws.Name, TextBox1.Value) > 0 Then
    .AddItem ws.Name
    End If
    Next ws
    End With
    Else
    With ListBox1
    .Clear
    For Each ws In Worksheets
    .AddItem ws.Name
    Next ws
    End With
    End If
    End Sub

    Private Sub CommandButton3_Click()
    Unload Me
    End Sub

    Private Sub UserForm_Initialize()
    Dim ws As Worksheet
    With ListBox1
    For Each ws In Worksheets
    .AddItem ws.Name
    Next ws
    End With
    End Sub


    Code is started by opening the workbook via Workbook_Open:
    Code:
    Private Sub Workbook_Open()
    UserForm1.Show vbModeless
    End Sub

  8. #8
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Bump no response.

  9. #9
    Forum Moderator alansidman's Avatar
    Join Date
    02-02-2010
    Location
    Steamboat Springs, CO
    MS-Off Ver
    MS Office 365 Version 2403 Win 11 Home 64 Bit
    Posts
    23,810

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

    Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

    Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



    (This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)


    Posting code without an explanation does not help. I have asked that you explain in simple English what you want to happen. See my post #6 and try again.

  10. #10
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Dear,
    My query is regarding macro to find sheet name with next button option in case one workbook is having multiple sheets with similar name. I found two solutions (refer below code#1 and code#2 in trail mail) on excel forum matching to my requirement but my query is not fully solved. Hence requesting some further support in this regard.

    Issue in Code#1: When we search sheet using this code it shows search result one by one not in one go. I want that all search result should be reflecting in one dialogue box so that we may select out of it.

    Issue in Code#2: Not able to execute this code. Pls share the procedure how to execute this code.

    I hope my query is clear now.


    Code # 1:
    Please Login or Register  to view this content.

    Code # 2:
    Please Login or Register  to view this content.

    Code is started by opening the workbook via Workbook_Open:

    Please Login or Register  to view this content.

  11. #11
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    How would like us to debug the code without file ...?
    "multiple sheet with same name" ... how is it possible ??
    - Battle without fear gives no glory - Just try

  12. #12
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Refer attachment in my post#3.

  13. #13
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Bump no response.

  14. #14
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Code #1 is reviewing all sheets' name in a workbook and checking if the name is matching a string entered in a message box:
    Then after what do you want to do?
    Code #2 is working with a userform with buttons to remove sheet, to make the list of sheets.
    Do you have a sample of the userform for Code#2
    Else explain step by step what you to do

  15. #15
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Code#1: When we search sheet using this code it shows search result one by one not in one go. I want that all search result should
    be reflecting in one dialogue box so that we may select out of it without exit.

    Code#2: Pls refer attach sample file. Not able to execute this code. Pls share the procedure how to execute this code.
    Attached Files Attached Files

  16. #16
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    See file attached: is it what you want ???
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by PCI; 04-26-2016 at 04:53 PM. Reason: Code added

  17. #17
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Thanks for response but below changes are required:

    1) Instead of auto open, use shortcut key to call dialogue box

    2) then search required sheet name in search field,

    3) Select required sheet from given option in a dialogue box.

    4) Instead of delete, pls add "select sheet" option
    Last edited by DEEPAK AGGARWAL; 04-27-2016 at 10:55 AM.

  18. #18
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    "use shortcut key to call dialogue box" yes which one?

  19. #19
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    See next macro
    USe Control + a to launch the macro
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by PCI; 04-27-2016 at 12:12 PM.

  20. #20
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Instead of "Select Sheet" pls use one Textbox (for the search) and instead of "Your Choice" pls use one Listbox for the display of all searched sheetnames.

  21. #21
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    "all searched sheetnames. "
    today it can be done only one selection => how would like to access to several sheets
    Can you make a sketch, a drawing how you see the tool

  22. #22
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Pls refer attach screen shot.
    Attached Files Attached Files

  23. #23
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    This is similar as we use "Ctrl+F" to find something in excel sheet and it shows all the result in listbox so that we can select anyone out of all option. Here only difference is that we need to search sheet name instead of data in sheet.

  24. #24
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Thank you for the display we are on the right direction.
    You want to be able to enter a string in the window "Search Field" and then to display all sheets' name which could match partially the string.
    And also to mention if the sheet is hidden or not, is it ?
    The window with the "All" selection is to ask for a complete search or just the first one is it ?
    Also you want to be able to select several sheets in the list and after to make the selection of the sheets concerned ?? Of course only visible sheets !

  25. #25
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    You want to be able to enter a string in the window "Search Field" and then to display all sheets' name which could match partially the string. - Required
    And also to mention if the sheet is hidden or not, is it ? - Not required
    The window with the "All" selection is to ask for a complete search or just the first one is it ? - Not required
    Also you want to be able to select several sheets in the list and after to make the selection of the sheets concerned ?? Of course only visible sheets ! -Yes need to select required sheet out of search result

  26. #26
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Try next code


    Please Login or Register  to view this content.
    Attached Files Attached Files

  27. #27
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Instead of “Select sheets” tab we should have search option so that when we search it will only reflect only those sheet which are matching with search string. Pls refer attach file.
    Attached Files Attached Files
    Last edited by DEEPAK AGGARWAL; 04-29-2016 at 09:31 AM.

  28. #28
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Pls refer attach revise code. Only issue is that when we search it is searching and matching only from second character.
    Attached Files Attached Files

  29. #29
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    See next code
    Please Login or Register  to view this content.
    Attached Files Attached Files

  30. #30
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Search option is not working. It is showing nothing at the time of search.

  31. #31
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    The search option gives the possibility to display sheets'name in theListBox according to the string entered in the TextBox
    Try in the file attached with "SSh"

  32. #32
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Attachment not found. Pls check and re-send.

  33. #33
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    This is the same file as the one sent before
    Attached Files Attached Files

  34. #34
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    No data is reflecting in list box as per string enter in search text box.

  35. #35
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Did you clic search button ?
    See attached a display in sheet User
    Attached Files Attached Files

  36. #36
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Thanks it's working but every time we click "Search" button it adds the same list again and again in list box.
    Also when I am searching with 'ssh' it is showing all sheet name instead of only showing "SSheet1" and "SSheet7".
    Last edited by DEEPAK AGGARWAL; 05-01-2016 at 05:00 AM.

  37. #37
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    " every time we click "Search" button it adds the same list again and again in list box"
    Yes it waiting from you , you do the selection of the sheet where you want to go
    "searching with 'ssh' it is showing all sheet name"
    No, it is showing previous display: See here update

    If this don't fit to your needs, it will good that you explain step by step what you want to achieve

  38. #38
    Registered User
    Join Date
    04-02-2016
    Location
    India
    MS-Off Ver
    MS Office 2010
    Posts
    37

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    "searching with 'ssh' it is showing all sheet name"
    No, it is showing previous display: See here update : Where is update?

  39. #39
    Forum Expert
    Join Date
    12-24-2007
    Location
    Alsace - France
    MS-Off Ver
    MS 365 Office Suite
    Posts
    5,066

    Re: Macro to find sheet name with next button option in case of multiple sheet with same n

    Oups the file attached is missing
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Option Button to Pull Data from Another Sheet
    By markusvirus in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-05-2016, 12:16 PM
  2. Option Button LinkedCell using VBA - will not reference different sheet
    By DwinZly in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-22-2015, 12:56 PM
  3. [SOLVED] Sort sheet when option button selected
    By Macfool in forum Excel General
    Replies: 6
    Last Post: 01-28-2014, 01:08 PM
  4. [SOLVED] Data to be copied from one sheet to another sheet by Find option
    By mohandeck in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 06-18-2013, 12:34 AM
  5. Replies: 0
    Last Post: 03-13-2013, 03:14 PM
  6. Populating an Excel 2010 Sheet using a macro button which will find the correct data?
    By dewhirstn in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-18-2012, 09:14 AM
  7. [SOLVED] Option Button on sheet changes size in 1024 by 768 resolution
    By Michael Tress in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 07-13-2006, 05:10 PM

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