Solved: Find Sheets in a Workbook

[almouchie]

i have a workbook with over 50 sheets
I wanted a macro to find the sheet without scrolling to find it

I have found this macro (from a helpful member)
it has worked fine in the past
but now the sheets are many & it sometimes gives me error
& returns "you didnt enter a sheet number or press cancel"
when i delete a sheet it works?!

one more thing how can i increase the size of the inputbox so all the sheets appear so i can type which one i want

in this workbook when i delete any sheet
the inputbox shows till sheet 13 only (i cannt see the rest)

any help is appreciated

[austenr]

I tested your macro with sheets named Sheet1, sheet2, sheet3 etc. works fine. Probably because you have a mix of names. Ex. Testsheet, Sheet1, mysheet. Might be best to rename all of your sheets as Sheet1, sheet2 etc. or modify your code.

[austenr]

My changed file

[Bob Phillips]

Your message is too long, it is exactly 256 characters. If you take out just one character, it works fine.

[lucas]

I use the attached for sheet navigation. It uses a userform. To be fair, the original code came from XL-Logic.com I think.....

Form comes up when book is opened, just click on the sheet you wish to navigate to.

[almouchie]

thanks for ur replies
the reason I cannt change the sheet names to : sheet1, sheet2, etc
is becoz the sheets are clients names & i want to research for the client names (austenr)
the copy u provided still cannt show me all the sheets in the inputbox it stops at sheet 14. can i increase the size of the inputbox to show all the sheets?

[almouchie]

Your message is too long, it is exactly 256 characters. If you take out just one character, it works fine.

what do u mean
take what character? from where
I dont understand

[Bob Phillips]

what do u mean
take what character? from where
I dont understand

You are building a MsgBox string. That string is 256 characters. If you want to continue this approach, you will need to reduce that string. You will have to decide where you reduce it, I can't do it all for you.

[almouchie]

i am still trying to get around the xcel book (lucas) u provided
I dont know much how to create a navigator
so i am trying to copy my sheets to that workbook
thanks for all ur help

[lucas]

i am still trying to get around the xcel book (lucas) u provided
I dont know much how to create a navigator
so i am trying to copy my sheets to that workbook
thanks for all ur help

almouchie,
you can easily change the size of the form and then add the sheets to the nav sheet....see attached

[Bob Phillips]

Try this

[almouchie]

both ways (attached versions) work great
i aa beginner as far as coding is concerned & i learn more everyday

thanks for both of u XLD,& LUCAS
Your help is very much appreciated

[lucas]

Hi almouchie,
Well at least you have options.....if your question has been answered be sure to mark your thread solved using the thread tools at the top of the page.

[almouchie]

yes
my bad i will mark it as solved
thanks