How to move the cursor out of the comment box?

[leonlai]

Hello,

I am developing a few macros dealing with comments.

I notice that if a user happens to place (or accidentally leaves) his mouse cursor inside a comment box (e.g. after editing a comment), and then clicks on any macro button, the button does not work. He should first move his cursor out of the comment box before clicking on any macro button.

Worse, if I place the macro button in the Excel Ribbon, and the user clicks on any button while his cursor is inside a comment box, the program crashes.

Anybody has any idea how we can avoid this type of problem?

My preferred idea is that when a user clicks on any macro button, the button first checks if the cursor is inside any comment box. If yes, it will display a MsgBox "Please remove your cursor from the comment box!". How can this be done?

Alternative ideas are also welcome!

Thanks
Leon

 

[Jaafar Tribak]

How many buttons are we talking about ? do you have the button on the ribbon or the worksheet ?

If the button is on the worksheet, what is its name ?

 

[leonlai]

Hi, Jaafar

Thanks for your reply.

[COLOR=#333333]How many buttons are we talking about ?[/COLOR]

I have one button, but I can have more - the problem is the same

[COLOR=#333333]do you have the button on the ribbon or the worksheet ?[/COLOR]

I have tried with my button on my worksheet: When I click on the button, it does not fire.
I have also tried with my button on the ribbon: When I click on the button, the program crashes

[COLOR=#333333]If the button is on the worksheet, what is its name ?[/COLOR]

The name of the button is not important. You can call it any name. In fact, I use a rectangle shape and assign a simple macro to it:
say "Hello World!"

Then I create a single comment on the worksheet.
I add some text in the comment, and deliberately leave my cursor inside the comment box.
When I click on the rectangle, it does not fire.

Best Regards,
Leon

 

[leonlai]

Hi,

I solved my problem partially by using a Try-Catch code block.

If a user happens to click a ribbon button while his cursor is still inside a comment box, my Try-Catch blocks prevents my program from crashing.

I use:

Try
Catch ex As Exception
MsgBox ex.Message

The message displayed is:
Object reference not set to an instance of an object

However, this message is too vague.

Is there a more precise type of exception which I can use to catch the type of error I have in mind, so that I can display a more precise error message, something like:
"Remove your cursor from the comments block, and try again" ?

Using Try-Catch is one solution. Are there others?

Thanks
Leon

 

[Jaafar Tribak]

@leonlai

Try Catch is not vba ... It is C !!!

I can look for a code that I remember writing to get out of comments and ajust it a little bit and post here if you are still interested.

 

[Jaafar Tribak]

Ok- you or anyone interested in this can use this little hack.

Short of using a fast timer, I have used CommandBars OnUpdate event to get the job done.

Code goes in the ThisWorkbook Module :

Option Explicit

Private Type POINTAPI
    x As Long
    y As Long
End Type

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    Private Declare PtrSafe Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare PtrSafe Function GetActiveWindow Lib "user32" () As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetAsyncKeyState Lib "user32" (ByVal vKey As Long) As Integer
    Private Declare Function GetCursorPos Lib "user32" (lpPoint As POINTAPI) As Long
    Private Declare Function GetActiveWindow Lib "user32" () As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If

Private WithEvents cmbrs As CommandBars


Private Sub Workbook_Activate()
    Set cmbrs = Application.CommandBars
    Call cmbrs_OnUpdate
End Sub

Private Sub Workbook_Deactivate()
    Set cmbrs = Nothing
End Sub

Private Sub cmbrs_OnUpdate()

    Dim tCurPos As POINTAPI
    Dim oShp As Object
    
    On Error Resume Next
    
    Application.CommandBars.FindControl(ID:=2040).Enabled = Not Application.CommandBars.FindControl(ID:=2040).Enabled
    Call GetCursorPos(tCurPos)
    Set oShp = ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.y)
    If TypeName(oShp) = "TextBox" Then
        Do
            If GetActiveWindow <> Application.Hwnd Then Exit Do
            Call GetCursorPos(tCurPos)
            Set oShp = ActiveWindow.RangeFromPoint(tCurPos.x, tCurPos.y)
            If GetAsyncKeyState(VBA.vbKeyLButton) And TypeName(oShp) <> "TextBox" Then
                ActiveCell.Select
                Exit Do
            End If
            DoEvents
        Loop
    End If
End Sub

 

[leonlai]

Hi, Jaafar

Try Catch is not vba ... It is C !!!

You are right.

I did not mention that I was using 2 different programming languages for my tests.

I used VBA for button (or rectangle) on my worksheet.
and I used VB .NET for button on the Ribbon. It's here that I uses Try-Catch. It works, but not very interesting.

Sorry for the confusion.

Leon

 

[leonlai]

Hi, Jaafar

Thanks a lot for your code.

It contains a lot of code I have never seen before, and I would like to try it.
Unfortunately, I could not make it work.

Or, more precisely, I do not know how to use it.

Could you explain how to use the code?
This is what I did, but it does not work:

1. Copy the code to a module.

But the foll. line is highlighted as an error:

Private WithEvents cmbrs As CommandBars

2. Drop a rectangle on my worksheet and try to assign a macro. But there is no macro to assign.

So, I am not sure I am doing thing correctly.

Leon

 

[MARK858]

Jaafar did state that the code needs to go in the ThisWorkbook module.

 

[Jaafar Tribak]

Hi, Jaafar

Could you explain how to use the code?

Follow these steps:

1- Have your shapes(s) rectangle with its assigned macro ready in your worksheet(s) as well as your comment(s).
2- In the VBEditor, double-click the ThsiWorkbook Module in the Project explorer .. This should bring the blank ThisWorkbook module pane to the front.
3- Paste the whole code in this blank pane , save and reopen the workbook file .

This last step is so that the Workbook_Activate event code is executed when the workbook is first opened or activated.