• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Code not getting triggered by application control

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?

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
 

Attachments

  • Userform_position_dynamic_based_on_window_position.xlsm
    21.5 KB · Views: 0
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
 
Last edited:
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.
 
Back
Top