vba to change the position of comment boxes on mouse-over

[cjcass]

Hello,

I have found code that you can use to change the position of comment boxes when then comments are permanently visible, but not when the comments are displayed on mouse-over only. Does anyone know of a code solution to achieve this? It's to resolve the classic situation where comment boxes at the periphery of the screen are only partly visible. I'm really looking for a 'comment box' solution (if achievable) as my workbook is already heavily geared towards using comments.

Any help would be much appreciated.

 

[TheURL]

@Jaafar - this is great and works well, though the comment popup sometimes does not move until I click the a sheet cell to deactivate a modeless userform. But more importantly, I cannot make it work in a workbook that was opened from a VB6 program - and I think this is because in Sub cmndbars_OnUpdate(), the line

If GetActiveWindow <> Application.Hwnd Then Exit Sub

causes it to Exit. If I try to outwit it by commenting it out it still does not fire. So I'm a bit lost here now as to how to make it work in this scenario. Can you help please, or am I asking too much of this method?

Gratefully yours
Simon

 

[Jaafar Tribak]

@Jaafar - this is great and works well, though the comment popup sometimes does not move until I click the a sheet cell to deactivate a modeless userform. But more importantly, I cannot make it work in a workbook that was opened from a VB6 program - and I think this is because in Sub cmndbars_OnUpdate(), the line

If GetActiveWindow <> Application.Hwnd Then Exit Sub

causes it to Exit. If I try to outwit it by commenting it out it still does not fire. So I'm a bit lost here now as to how to make it work in this scenario. Can you help please, or am I asking too much of this method?

Gratefully yours
Simon

Try replacing Workbook_Activate with Workbook_Open and see what you get.

 

[TheURL]

Hi Jaafar - thanks for replying - I now this thread has been around for some time now. I tried that but it made no difference. I think the problem stems from the fact that my VB6 program creates an instance of Excel, the opens the workbook in that instance, then hangs around waiting for it to close before doing some other stuff. In the workbook the GetActiveWindow returns 0 so the comment repositioner never fires. But if I open the workbook from Windows, NOT via the VB6 program, GetActiveWindow has a valid non-zero value and it all works great! It's not critical, but your code works so beautifully it would be lovely to be able to implement it in this application.

 

[Jaafar Tribak]

Hi Jaafar - thanks for replying - I now this thread has been around for some time now. I tried that but it made no difference. I think the problem stems from the fact that my VB6 program creates an instance of Excel, the opens the workbook in that instance, then hangs around waiting for it to close before doing some other stuff. In the workbook the GetActiveWindow returns 0 so the comment repositioner never fires. But if I open the workbook from Windows, NOT via the VB6 program, GetActiveWindow has a valid non-zero value and it all works great! It's not critical, but your code works so beautifully it would be lovely to be able to implement it in this application.

Hi,

By default, excel comments do not popup unless excel is the active window. So for example, If you open a modal userform and point with the mouse over a cell with a comment, the comment won't show up.

Do not know why the code doesn't work when the workbook is opened by the VB6 program. If you place a MsgBox in the Workbook_Activate or the Workbook_Open events, does the MsgBox show up when opening the workbook from VB6 ? This is just to check if the events are actually firing.

 

[TheURL]

I understand that. My workbook uses a modeLESS form, and the comments pop-up - but always in the default position IF opened by VB6. I'll try to create the problem in a slimmed down version the VB6/VBA solution - the act of doing so often narrows it down, or sometimes even solves the problem! Maybe I can then upload the demo here (or elsewhere). Might not get a chance to do this until the weekend though...I'll keep you posted! Thanks - S

 

[Jaafar Tribak]

I understand that. My workbook uses a modeLESS form, and the comments pop-up - but always in the default position IF opened by VB6. I'll try to create the problem in a slimmed down version the VB6/VBA solution - the act of doing so often narrows it down, or sometimes even solves the problem! Maybe I can then upload the demo here (or elsewhere). Might not get a chance to do this until the weekend though...I'll keep you posted! Thanks - S

Do the Workbook_Activate and\or the Workbook_Open events fire when the workbook is opened via the vb6 program ?

 

[TheURL]

Yes they do - not least because the modeless form appears which forms a toolbar, and which is .SHOWn by Workbook_Open.

 

[Jaafar Tribak]

Yes they do - not least because the modeless form appears which forms a toolbar, and which is .SHOWn by Workbook_Open.

What happens if you introduce a small delay before setting the EnableCommentsRepositioner Property to TRUE.

Something along these lines:

Private Sub Workbook_Open()
    Application.OnTime Now, Me.CodeName & ".Delay_CommentsRepositioner"
End Sub

Private Sub Delay_CommentsRepositioner()
    If oCommentsRepositioner Is Nothing Then
        EnableCommentsRepositioner = True
    End If
End Sub

 

[TheURL]

I tried adding the timer but it made no difference. I changed my VB6 program to open a small demo workbook containing your code, and that worked too. Your code in my live production workbook also works too, if it's opened from Explorer direct instead of being opened by my VB program! My production workbook is full of WinAPI calls so my guess is that there is some latent bug in my code somewhere, which I'm getting away with - until such time as I want to use this solution. Fwiw: I'm running Excel 2019 64-bit on Windows 11 64-bit. I'll keep your solution for future use elsewhere but I think I'll have to give up trying to make it work in this application. Thanks for your help though.