Worksheet_SelectionChange if Comment/Textbox

[AAM]

I'll often put something like this in a worksheet's code to both jump to the top of a long list and to prevent editing of the header rows:

Private Sub Worksheet_SelectionChange(ByVal Target As Range) 
If Not Application.Intersect(Target, Range("1:6")) Is Nothing Then Range("B7").Select
End Sub

So if I click anywhere in rows 1 thru 6, the cursor jumps to B7.

What would I add if I have a comment or text box in those same rows so that I can't select the comment or textbox, and the cursor will still jump to B7?

TIA,

Andy

[mikerickson]

This code will allow you to select a cell in 1:6 only if it has a comment. (how to insert a new comment, I don't know). Selecting a text box from the Drawing Menu doesn't trigger the SelectionChange event so that is N/A. I don't know if ActiveX text boxes act the same way.

The logic could be smoother, but addressing the .Comment property of an uncommented cell throws an error.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not (Application.Intersect(Target, Range("1:6")) Is Nothing) Then
    Application.EnableEvents = False
    If Comments.Count = 0 Then
        Range("b7").Select
    Else
        If Application.Intersect(Target, Cells.SpecialCells(xlCellTypeComments)) Is Nothing Then
            Range("b7").Select
        End If
    End If
    Application.EnableEvents = True
End If
End Sub

Apparently, SpecialCells triggers a SelectionChange event. This is puzzeling and rather than hijack this thread I'm going to start a new one.

new thread: http://www.vbaexpress.com/forum/show...462#post132462

[AAM]

Thanks, but this isn't what I want to do. Not only do I not want to be able to select the cells with comments (the original code already does that), but I don't want to be able to select the comment, or textbox itself. And I don't want to protect the sheet.

I tried[vba]If not Application.Intersect(Target, Cells.SpecialCells(xlCellTypeComments)) Is Nothing...[/vba]but that didn't work either.

[mikerickson]

I don't know of any way to select a comment without first selecting its cell. The text boxes present a bigger problem. You are looking for a way to trigger a routine when one is selected. It should be possible to access the GetFocus event with ActiveX toolboxes, but from the Drawing menu I don't know.

Excel is much better at allowing the user to do things than it is at forbidding them.

[AAM]

Hmm, maybe I better explain better what I'm trying to do.

If I have a long list with the first 6 rows frozen, I'll usually use the one-line code above so that if I click anywhere in those 6 header rows, the cursor jumps to the top of the list, B7 for example.

But if I have any comments showing, which I now do in a current project, clicking in the comment box puts me in the comment instead of jumping me to B7.

Is there some kind of change that would be triggered by clicking in the comment that I could then use to trigger Range("B7").Select?

Don't worry about text boxes, I don't need them in the Header rows, but I'd like to deal with the comments.

Andy

[Norie]

Andy

Assuming you mean a comment created using Insert>Comment I don't see how that code would behave like that.

It doesn't even mention comments and I can replicate the problem.

Is there anything else going on?

[AAM]

The code I posted with my original message will jump me to B7 if I click anywhere in rows 1:6, including cells with comments, except if I click in the comment itself (created by Insert>Comment). If I click in the comment itself, I'm in comment editing mode.

I can get the behavior I want if I protect the sheet and allow everything except editing objects. So I'll probably do that.

But I'm still curious if I could instead add a line to my little SelectionChange codelet.