disabling custom buttons during cell edit mode

[Doug Glancy]

Is there any way to disable custom buttons durning cell editing, as occurs
with builtin buttons? I found an old post from Tom Ogilvy suggesting to
copy a builtin button and then reassign the onaction, caption, etc.
However, it seems that assigning a new onaction loses the trait of graying
out during cell edit. Here's the code I tried:

Sub test()
Dim cbar As CommandBarPopup
Dim ctl As CommandBarControl

On Error Resume Next
CommandBars(1).Controls("test").Delete
On Error GoTo 0

Set cbar = CommandBars(1).Controls.Add(Type:=msoControlPopup,
temporary:=True)
cbar.Caption = "test"
Set ctl = cbar.Controls.Add(ID:=757) 'Edit --> Go To button
With ctl
.Caption = "tester"
.OnAction = "tester"
End With

End Sub

Sub tester()
MsgBox "test"
End Sub

Thanks,

Doug

 

[Philip]

First, your ADO Connection...

change

cn.Cstring

to

cn.open Cstring

or

with cn
.connectionstring=Cstring
.open
end with

HTH

Philip

 

[Doug Glancy]

Wow, I would have never thought of that! <g>

Any other thoughts?

 

[Philip]

sorry, I answered the wrong post

for your one, I reckon you need to retrieve a reference to the button on the
toolbar and use thre .enabled property=false

to do that while a cell is in edit-mode?

have a look at the worksheet events, like worksheet_change.

But I really don't know if that type of functionality will help as normally
to signal a cell is in edit mode, behind the scenes I think Excel raises and
traps the 'Onkey down' events or mouse double-click event.

But those events are not exposed to VBA, so you're looking at sub-classing -
which is maybe going to far... and is quite involved... you'd need to use the
AddressOf operator - declare a variable withevents as a worksheet object, set
it to the active sheet, then see if you can't get more events that way
perhaps.

Sorry I can't help more...

Philip