excel sheet wont scroll after adding userforms

1
votes

I have buttons for going to other sheets in my workbook so I can hide other tabs to keep it cleaner. Everything worked fine until I added some userforms into the mix. Now when I use my button to activate another sheet it goes there but sometimes won't allow me to scroll. It happens when I change sheets after about 6 times, then it hangs. Seems like the userform isn't unloading properly.

What I do to get excel to respond is to change sheets with the defualt tab at the bottom (some sheets show 2 tabs some show only 1).

I have my userforms load as vbmodeless

Properties for each userform modal is False

One of my userforms

Private Sub CancelButton_Click()
    Me.Hide
    Unload Me
End Sub

I've been trying a bunch of things to get my worksheet to acts as normal with the userforms and nothing.

Any suggestions are greatly appreciated.

2
vbaexcel
Aside from a code fix, I've found that by double clicking on any cell on the sheet you regain your ability to scroll. - dwirony
Shot in the dark: Add Application.screenupdating=true in the CancelButton_Click() Also you do not need to hide and then unload it. you can directly unload it. - Siddharth Rout
@dwirony double clicking doesn't work on mine. @siddharth Rout screenupdating = true didn't do anything. - Matt Taylor
Try my solution below. I tested it a couple times and it worked for me (on a document that I have the same issue). - dwirony
Can I see your workbook? If yes then feel free to upload it in a free file sharing site and share the link here - Siddharth Rout

2 Answers

0
votes

Plug this in after Unload Me and see if that fixes it.

ActiveWindow.ScrollRow = 1

0
votes

So it helped me with 2013 Excel Verison. Just when you assign a macro to a button to open a Userform just type this

Userform.Show False

What it does when you transition to another sheet you can still do stuff with user form open, so you just close it and everything must work fine