VBA ActiveWindow problem

[cgmojoco]

I'm using this code to update a userform (this is code that is part of a progress bar):

Private Sub UserForm_Activate()
Dim lTop As Long, lLeft As Long
Dim lRow As Long, lCol As Long
    With ActiveWindow.VisibleRange
        lRow = .Rows.Count / 2
        lCol = .Columns.Count / 2
    End With
    With Cells(lRow, lCol)
        lTop = .Top
        lLeft = .Left
    End With
    With Me
        .Top = lTop
        .Left = lLeft
    End With
End Sub

Is there a way to do the same thing without having to focus on the activewindow (if I go to surf the net while this script is called in one of my subroutines I get an error---I'm assuming excel can't find the activewindow?)

 

[RoryA]

You could try ActiveWorkbook.Windows(1) instead of ActiveWindow?

 

[cgmojoco]

rorya-

Still getting Unable to get the Top property of the Range class.

 

[cgmojoco]

It seems the userform has to be the "active window"

 

[cgmojoco]

Apologies if this is simple....but I cannot figure this out.

If my routine looses focus of my active window which apparentley is my userform named frame_progress

How can I rewrite so that I don't receive this error when outside of excel while it runs?

 

[erik.van.geit]

Hi,

I can not reprocude your problem.
While Excel is not active, the code runs without any problem.
There could be another reason. Feel free to email me your workbook - delete all irrelevant parts - so I can test it.

This is how I tested.
Run "test" then got to another programme before "test2" is launched by the OnTime.

Sub test()
Application.OnTime Now + 5 / 24 / 60 / 60, "test2"
End Sub
Sub test2()
UserForm1.Show
End Sub

kind regards,
Erik

 

[cgmojoco]

Odd-

The workbook I was preparing to send to you I was also unable to reproduce and this is just copying pasting code and form...

Alas, that means it lies somewhere other than what is apparent.

I cannot seem to remove enough to make it so that I would be allowed to send to you...

 

[Joe Was]

On your form's proprties set the "Modal" property to FALSE, this will let the form operate when it does not have the focus.

 

[erik.van.geit]

on what line does the error appear and what is the message?
(sleeping now)

 

[cgmojoco]

Private Sub UserForm_Activate()
Dim lTop As Long, lLeft As Long
Dim lRow As Long, lCol As Long
    With ActiveWindow.VisibleRange
        lRow = .Rows.Count / 2
        lCol = .Columns.Count / 2
    End With
    With Cells(lRow, lCol)
        lTop = .Top
        lLeft = .Left
    End With
    With Me
        .Top = lTop
        .Left = lLeft
    End With
 
End Sub

Run-Time error '1004':
Unable to get the Top property of the Range class

Changing modal = False did not resolve for whatever reason

The odd thing is that when I switch back over to excel and even select the active window then try to resume the code (via the VBA screen), the code still errors out so I must start the routines over. Perhaps this is because when I select the VBA screen it deselects the active window the routine is looking for?

I am puzzled...