Worksheet scrolling with scroll wheel is broken! (1 sheet in book)

[Cicatrize]

Here's the scenario...

I have a workbook with 8 sheets, one of which is the "main" sheet that holds all of my user data for logins at my job (I'm in IT). There are about 4300 users and 23 fields for each user, so there are 4300 rows in the sheet. Other sheets in the book are important, but not as much as this one.

The workbook has a total of over 1000 lines of VBA code right now, as I'm building a tool that finds users, adds users, modifies users, etc. (I know, Access would be smarter for this, but for some reason they want me to use an xls). Most of this code works with the sheet that I'm having the problem on.

This ONE sheet has seemed to disable the functionality of the scroll wheel, and I can't use Page Up or Page Down while in the sheet either. Also, if I select cells with VBA (Sheet2.Range(<range>).Select), it will select the cell, but it will not draw focus to that cell on screen (the sheet will not move at all). The other 7 sheets in the workbook have all regular functionality.

This just started happening a couple days ago, and I've been building the code for about a week, so I'm wondering if it's something in the code...?

Does anyone have any idea what might cause this?

Thanks in advance if anyone can figure this out.

[jasoncw]

It sounds like the Scroll Area properties of the sheet have been set. You can see this in the sheet properties in VBE.

[Cicatrize]

I've tried that with no luck. The scrollbars do still work, too.

[royUK]

That seems like a huge amount of code.

You don't need to select cells, etc in VBA to work with them as a general rule.

Have you checked the WorkBook events to see if there is any code there locking the ScrollArea?

[Cicatrize]

That seems like a huge amount of code.

You don't need to select cells, etc in VBA to work with them as a general rule.

Have you checked the WorkBook events to see if there is any code there locking the ScrollArea?

The only reason I select cells is in order to bring the selected user on screen in the worksheet, and to select the entire row in order to paste into an e-mail (e.g. new user). I have so much code because the tool logs all changes done to the worksheet, adds users, modifies users, searches existing accounts, removes accounts, copies users, etc. Each user has several accounts for different applications, which is another reason for all the code too.

None of the workbook events lock the scroll area.

[royUK]

Have you tried copying the shet to a new workbook & seeing if you can select then?

[Cicatrize]

Have you tried copying the shet to a new workbook & seeing if you can select then?

Yes, that enables me to scroll until I double click a user to load the form and make changes to him/her. Then the scrolling is disabled again.

[Cicatrize]

Also, something to note:

The worksheet is locked for scrolling with pgup/dn and scroll wheel before I even access the UserForm.

[royUK]

It will be disabled whilst the userform is loaded unless it is a modeless userform. Try changing the userform's ShowModal property to False

[Cicatrize]

It will be disabled whilst the userform is loaded unless it is a modeless userform. Try changing the userform's ShowModal property to False

That didn't help either.

Up until a couple days ago, I was able to scroll, even with all of my code, as long as the user form was not showing.

The sheet will not accept scrolling even when doing a raw edit of the data now.

[royUK]

I can't think of anything else without seeing the workbook.

Maybe if you export all the code to a new workbook, then copy the sheets across to the new workbook.

[Cicatrize]

I wish I could attach the workbook, but there's important information for each user that I can't post on a public forum like this. Besides, the thing is like 3MB anyway, haha.

[royUK]

I would try rebuilding the workbook as I added in my last post. Sometimes they can be corrupted when working with a lot of code. Try Rob Bovey's Code Cleaner

http://www.appspro.com/Utilities/CodeCleaner.htm

[Cicatrize]

Couldn't use the code cleaner because I wasn't allowed to check "Trust access to VB Project", but I did copy all the code over to a new workbook and everything seems to work fine now. Thanks.

I really would like to know what happened, though.

[royUK]

Probably just a case of the workbook getting corrupted This can happen with large amounts of code & frequent working on the code.

Please mark this thread solved.