Key Binding Problem or FreezePanes problem?

[drj30026abanba]

So this is a lengthier question. I don't understand what is going on here so I wanted to ask you guys what is going on. So I wrote an add-in that would allow me to "reset" a workbook that I had. What it does is take a workbook and goes through each page to select cell A1, remove freeze panes, and then scroll to the top.

I have a class module named "wbStuff" with the following code:

<code>
Public WithEvents App As Application

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)

Application.OnKey "^r", "resetTime"

End Sub
</code>

Then, I have a standard module with the following code:

<code>
Dim Dummy As New wbStuff

Sub startUp()

Set Dummy.App = Application

End Sub
</code>

There is a workbook module that has the following code:

<code>
Private Sub Workbook_Open()

Call startUp

End Sub
</code>

And finally a standard module with the following code:

<code>
Sub resetTime()

For i = 1 To Worksheets.Count

With Worksheets(i)
.Activate
.Cells(1, 1).Select
End With

With Application.ActiveWindow
.FreezePanes = False
.ScrollRow = 1
.ScrollColumn = 1
End With

Next i

Worksheets(1).Activate

End Sub


Initially the code in the last standard module didn't have the ScrollRow and ScrollColumn methods and looked like this:

<code>
</code><code>Sub resetTime()

For i = 1 To Worksheets.Count

With Worksheets(i)
.Activate
.Cells(1, 1).Select
End With

Application.ActiveWindow.FreezePanes = False

Next i

Worksheets(1).Activate

End Sub
<code>

I already saved the add-in and added it with the original code but when I changed it to include the scrolling I got weird behavior. When the freeze panes were below the original viewing area, the keyboard shortcut "Crtl+R" doesn't scroll up. If I step through the code manually from the VB Editor, the screen scrolls. Also, if I hit "Crtl+R" twice it would scroll. If i change the name "resetTime" to something else in both the class module and the standard module it is written in, "Crtl+R" works on the first time and scrolls. If i change the name back to "resetTime" it doesn't scroll anymore. I wondered if this has anything to do with the binding of "Crtl+R" on the WorkbookOpen event. I've also tried saving and closing out of the Excel application and then opening it up again and it doesn't seem to affect anything.

Another weird sidenote is that if i modify the standard module to set FreezePanes to false twice, it also fixes the problem, like so:


Sub resetTime()

For i = 1 To Worksheets.Count

With Worksheets(i)
.Activate
.Cells(1, 1).Select
End With

With Application.ActiveWindow
.FreezePanes = False
.FreezePanes = False
.ScrollRow = 1
.ScrollColumn = 1
End With

Next i

Worksheets(1).Activate

End Sub


[FONT=Verdana, Arial, Tahoma, Calibri, Geneva, sans-serif]I don't understand why this behavior is the way it is. Does this have to do with how FreezePanes works or does it have to do with the fact that I changed the function that a key is bound to in an add-in? Thanks for reading all this![/FONT]</code></code></code>

 

[drj30026abanba]

Actually the adding the ".FreezePanes = False" twice doesn't do anything. I must have done it in combination with changing the name of the subroutine "resetTime".

 

[drj30026abanba]

I've also realized that I can change the name of the subroutine and it will still execute the subroutine as i had it before without scrolling. Is there anything special going on with how add-ins work?

 

[drj30026abanba]

I just read this and it seems to point in the right direction:

VBA Tips: How to Use Your Excel Add-In Functions in VBA

I think that this has something to do with it. I experimented with changing the names in the class module and I think that the add-in files are hidden from regular view and that all the workbooks can't access them explicitly. I'm going to investigate further. Does anyone know how this works?

 

[drj30026abanba]

I think this might have something to do with it. I created the add-in and stored a function somewhere in this "hidden" space. So now when i change the function, this "hidden" place has precedence. I'm going to investigate further. If anyone understands how this stuff works, any help would be appreciated. Thanks!

Hidden Name Space