Error code "run time error 9: subscript out of range

[iBennett93]

Hello all!
I am having the following problem
"Set wsh = ThisWorkbook.Worksheets(sh.Cells(2, i).Value)"

I have included below the full code:

Please Login or Register  to view this content.

Any help would be greatly appreciated!

Thank you

[iBennett93]

Or if anyone can link me to a forum post which shows how to fix this error then that would be appreciated.

Thank you so much.

[sintek]

Please Login or Register  to view this content.

[iBennett93]

I have just tried that instead of "Set wsh = ThisWorkbook.Worksheets(sh.Cells(2, i).Value)" and it did not work

[Marc L]

Hi,

try Text property rather than Value and far above all check the cell content and if a worksheet exists accordingly !

[iBennett93]

Could you give me a sample please?
Just so I know i'm going to do the right thing

Thank you

[sintek]

See Top banner regarding sample file...

[ByteMarks]

Are you sure that a worksheet exists that is named as whatever sh.Cells(2, i).Value is?

[iBennett93]

I really am not to sure, it just came across my desk this morning. And this was the first problem with it

[iBennett93]

Sheet 2 is an admin board

[sintek]

Look at this sample....See what i mean...

Please Login or Register  to view this content.

[iBennett93]

Yeah I know what you mean. Just still dont know the method to fix it.

Its baffling me haha

[sintek]

Then upload your sample file...Either should work...

Please Login or Register  to view this content.

[ByteMarks]

With this line
"Set wsh = ThisWorkbook.Worksheets(sh.Cells(2, i).Value)"

if sh.Cells(2, i).Value isn't the name of any worksheet then you get the error you describe.

[iBennett93]

Which sample file? I am getting a bit muddled up now -.-

Is it now the file I have attached above?

[sintek]

Look at all the comments...Are you sure a sheet with this name is present...

[iBennett93]

I have attached the file below which should be linked.
Which is called Sheet1 (User Management)
Attachment 734496

[iBennett93]

I have just set it to this;
Set wsh = ThisWorkbook.Sheets("User Management")
And it is giving me an error on this line now.
wsh.Visible = xlSheetVeryHidden

Or is the first line wrong?

[torachan]

eighteen posts !!!!!!!!!!!!!!!!
look at the big yellow banner and upload your workbook.
crystal ball gazing and guessing wastes time.
torachan.

[iBennett93]

I have attached code and document. Sorry about that!

Please Login or Register  to view this content.

[sintek]

Nope...That upload has no reference to your actual code...
No User Management Sheet etc etc...You are not going to get results here if you cannot be more transparent...

[torachan]

what you have just posted does not seem to have any relevance to your opening posts.
however I have only looked at the last posting in any detail.
it would appear that if a password 'x' is entered you want the sheet to be invisible ?????
this would not work with your attached file for the reason that there has to be at least one visible sheet - you only have one sheet so the process would fail.
Also the cell referencing in the code did not make sense - you had rows confused with columns - in the attached there is an added empty sheet - if you put a password 'x' in the sheet1 will then become invisible - I do not pretend to follow your logic for wanting to perform this action.
torachan.

[Greg M]

Hi there,

There was a lot of mind-reading involved, but take a look at the attached version of your workbook and see if it gets you moving in the right direction. It uses the following code:

Please Login or Register  to view this content.

The highlighted values may be altered to suit your requirements.

Note: I had to change your "accented" letters to normal ones so that the code could be uploaded here.


Hope this helps - please let me know how you get on.

Regards,

Greg M

[Greg M]

Hi again,

Thanks for your private message.

Thank you for your help.. Which file do I add this to?

It's not really possible for me to say as I don't have access to your workbook(s?). Remember that I had to do a lot of mindreading before I came up with this suggestion.

Your requirement seemed to be to provide a facility for displaying/hiding and protecting/unprotecting various worksheets depending on the identity and/or category of the User. The workbook I posted meets such a requirement, so maybe the most straightforward thing for you to do is to take your various "real" worksheets and copy them into the workbook I posted, rather than trying to put some or all of my workbook into the ones you have.

We can talk more about this if you need.

Regards,

Greg M

[Greg M]

Hi again,

Thanks for your private messages about this - glad to hear that things seem to be working the way you want now.

As discussed, I'm posting a sanitised version of your workbook so that anyone who might have been following this thread can see the solution we arrived at.

I was very pleased that you considered my assistance worthy of the donation you made to The Dogs Trust.

Regards,

Greg M