Code not getting triggered by application control

[Manish Sharma]

Dear friends,

I am facing a strange problem with excel VBA. I have this below code in a workbook which moves the userform along with the excel window, either you minimize it or maximize it.
When i tries this in my personal system it worked fine, but when i tries the same file in my office system; then this code is not getting triggered.

When i tried to find the reason, then i found that the code is getting triggered by the excel restore window (highlighted in red color), but in my personal system in the same file code gets triggered with the application control (highlighted in blue color).
Screenshot of the controls:-
http://s21.postimg.org/5t4npi9on/Excel_Controls.jpg


I want the below code to be triggered by using the application control (the middle button on top right hand side in between close and minimize button. Do i need to change any settings in excel or i need to change the code?

Private Sub Workbook_WindowResize(ByVal Wn As Window)
            With UserForm1
                .StartUpPosition = 0
                .Top = Application.Top + 175
                .Left = Application.Left + 40
            End With
          
            With UserForm2
                .StartUpPosition = 0
                .Top = Application.Top + 175
                .Left = Application.Left + 40
            End With
End Sub

I have uploaded the same file for your help.

Thanks, friends

 

[NARAYANK991]

Hi Manish ,

I am not able to understand how the userform can be controlled by the control which is coloured BLUE.

The control which is coloured RED is the workbook window , and this generates the Workbook_WindowResize event ; the control which is coloured BLUE , is the Application ( Excel ) window , and this does not generate the Workbook_WindowResize event.

In fact , in your above event procedure , you should use the following statements :

.Top = Wn.Top + 175
.Left = Wn.Left + 40

instead of :

.Top = Application.Top + 175
.Left = Application.Left + 40

Narayan

 

[Manish Sharma]

Hi Manish ,

I am not able to understand how the userform can be controlled by the control which is coloured BLUE.

The control which is coloured RED is the workbook window , and this generates the Workbook_WindowResize event ; the control which is coloured BLUE , is the Application ( Excel ) window , and this does not generate the Workbook_WindowResize event.

In fact , in your above event procedure , you should use the following statements :

.Top = Wn.Top + 175
.Left = Wn.Left + 40

instead of :

.Top = Application.Top + 175
.Left = Application.Left + 40

Narayan

Thanks Narayan, for your reply. May be we have to change some settings in Excel option, because the same code worked in my personal system by using the application control. I tried to find it on google also but didn't get any help.