PDA

View Full Version : Solved: Find Sheets in a Workbook



almouchie
01-18-2006, 08:04 AM
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
01-18-2006, 08:48 AM
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
01-18-2006, 08:57 AM
My changed file

Bob Phillips
01-18-2006, 09:04 AM
Your message is too long, it is exactly 256 characters. If you take out just one character, it works fine.

lucas
01-18-2006, 09:17 AM
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
01-19-2006, 06:30 AM
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
01-19-2006, 06:36 AM
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
01-19-2006, 07:08 AM
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
01-19-2006, 07:09 AM
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
01-19-2006, 08:27 AM
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
01-19-2006, 08:35 AM
Try this

almouchie
01-19-2006, 09:36 AM
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
01-19-2006, 12:31 PM
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
01-19-2006, 02:07 PM
yes
my bad i will mark it as solved
thanks