Disable Control in Tools Menu

[Juan Sanchez]

Hi all

I hope someone can help me. I have an add-in that creates
an item on the Tools menu which is asigned to a macro in
the add-in. Just an extra tool. This is a workbook related
tool and I've noticed that the tools menu disables some of
the items inside it when XL is open but no workbook is
open. i.e it disables "Options", "Share workbook" and so
on.

I would like my tool to be disable as well when no
workbook is open since it creates an error if used when no
workbook is open. Can someone help me with the coding for
this, or direct me to an example?

TIA

Regards
JS

 

[Ron de Bruin]

Hi Juan

One way

Sub test()
On Error GoTo QuitOpen
ActiveWorkbook.Activate
'your code
Exit Sub
QuitOpen:
On Error GoTo 0
MsgBox "There is no file open", , "Your tool name"
End Sub

 

[Juan Sanchez]

Hi Ron

Thanks, thas sort of how I have the error handling sort
out. What I was wondering is if I can actually maje the
item appear dull and actually disable from the tools menu
so that it can't be clicked at. or selected...

On another question, what is the property that tells me if
a book has been saved for the fisrt time?

Regards
JS

-----Original Message-----
Hi Juan

One way

Sub test()
On Error GoTo QuitOpen
ActiveWorkbook.Activate
'your code
Exit Sub
QuitOpen:
On Error GoTo 0
MsgBox "There is no file open", , "Your tool name"
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Juan Sanchez" <[email protected]> wrote in

message news:[email protected]...

 

[Ron de Bruin]

Hi Juan

out. What I was wondering is if I can actually maje the
item appear dull and actually disable from the tools menu
so that it can't be clicked at. or selected...

I don't know ?

On another question, what is the property that tells me if
a book has been saved for the fisrt time?

A workbook that is not saved don't have a path

Sub test()
If Len(ActiveWorkbook.Path) = 0 Then
MsgBox "Not saved"
Else
MsgBox ActiveWorkbook.Path
End If
End Sub

 

[Juan Sanchez]

Ron, thanks a lot, that worked out great...
My add-in was creating a corrupted file that made XL crash
when run on an unsaved workbook... I used

If len(activeworkbook.path)=0 then goto NoSaved

NoSaved:
Bla Bla Bla
exit sub


It solved the problem... I appreciate it... thanks again...

I'll keep looking on the other subject to see if it can be
done...

Cheers
Juan

-----Original Message-----
Hi Juan

I don't know ?


A workbook that is not saved don't have a path

Sub test()
If Len(ActiveWorkbook.Path) = 0 Then
MsgBox "Not saved"
Else
MsgBox ActiveWorkbook.Path
End If
End Sub


--
Regards Ron de Bruin
http://www.rondebruin.nl


"Juan Sanchez" <[email protected]> wrote in

message news:[email protected]...