Paste Function dialogue box is the first in the CMDbars collection

It isn't. The Worksheet Menu Bar is the first member of the commandbars
collection.

--
Regards,
Tom Ogilvy


"George Raft" wrote in message
...
Good point about the messages - hadn't thought about that, and it is
intended for use in many tables. Of course, if I remove it, the problem
goes away (why, I don't understand), but thaar ya go.

I also don't understand what the proposed test is doing, or how we know
the
Paste Function dialogue box is the first in the CMDbars collection, but
I'll
fiddle with it a bit to see if I can't learn something.

Thanks again.
Tony

Dave Peterson wrote in message
...
You may have noticed the when you're editing a cell, many of the icons
on
the
toolbars are disabled. You can have your function test an icon. (I
think
that
this was suggested by Jan Karel Pieterse:

Option Explicit
Function GETENTRY(atRow As Variant, _
atCol As Variant, _
inTable As Range) As Variant

Dim i As Long, j As Long

If Application.CommandBars(1).FindControl(ID:=23,
Recursive:=True).Enabled
_
= False Then
Exit Function
End If


On Error Resume Next
With Application
i = .Match(atRow, inTable.Columns(1), 0)
j = .Match(atCol, inTable.Rows(1), 0)
End With

If Err < 0 Then
'MsgBox "One of " & atRow & " or " & atCol & "not found.", vbCritical
GETENTRY = CVErr(xlErrNA)
Exit Function
End If

GETENTRY = inTable(i, j)

End Function


And I would be very wary about putting that message box into your
function. If
you have lots of formulas that refer to the same table and you change a
heading
row, you may be dismissing these message boxes for a longgggggg time.

George Raft wrote:

Sorry, I'm back again.

I've written a user-defined function to replace the often confusing
V/HLookup(...Match(...) .) combination used in spreadsheets to return
an
element in a bordered (top and left edges) table. Here's the code:

Function GETENTRY(atRow As Variant, _
atCol As Variant, _
inTable As Range) As Variant

Dim i As Integer, j As Integer

On Error Resume Next
With Application
i = .Match(atRow, inTable.Columns(1), 0)
j = .Match(atCol, inTable.Rows(1), 0)
End With

If Err < 0 Then
MsgBox "One of " & atRow & " or " & atCol & " _
not found.", vbCritical
GETENTRY = CVErr(xlErrNA)
Exit Function
End If

GETENTRY = inTable(i, j)

End Function

Here's the problem: It works fine if invoked directly, but if I use
Excel's
Paste (Insert) function dialogue box, then as soon as I click on the
top
left corner of the range for the 3rd arg (inTable), it triggers the
error
message.

(Actually, it doesn't exit the function as I expect either, instead it
flashes the message and waits for new input. Hitting the cancel
button on the dialogue box triggers the message again. But that's
a whole nuther confusion and I don't want to get side-tracked.)

I can avoid this outcome in 3 ways:

a) enter one of the other two args as the last one specified in the
dialogue
box,
b) remove the On Error Resume Next statement, or
c) and this is really wierd, remove the MsgBox statement.

Choice a) is clearly not reasonable - I can't expect others to know
that
trick - and choices b) & c) frustrate my *truly wonderful* error
handling.

('course, the only reason I found out about this bug is that I was so
excited to see Excel list MY FUNCTION that I just had to try it.
Hubrous
was my downfall.)

I'm really confused about this, especially the REM'ing out the msgbox
part.
Does anyone have any idea about what's going on.

--

Dave Peterson