External Data Toolbar permanently greyed out.

[Colin Hayes]

Hi All

I have a worksheet upon which I use the 'Refresh All' command to receive
data from an external source. It works well.

I also like to insert Comments on the sheet. I find however that when I
use the 'Refresh All' command the formatting of the sheet is changes
such that the 'Insert Comment' link disappears from the context menu.

How can I stop the Refresh All command changing the format of my sheet ,
and leave me the option to insert a Comment?

I tried to use the data Range Properties command on the External Data
toolbar , but it is constantly greyed out.

Grateful for any ideas...

 

[GS]

Colin Hayes presented the following explanation :

Hi All

I have a worksheet upon which I use the 'Refresh All' command to receive data
from an external source. It works well.

I also like to insert Comments on the sheet. I find however that when I use
the 'Refresh All' command the formatting of the sheet is changes such that
the 'Insert Comment' link disappears from the context menu.

How can I stop the Refresh All command changing the format of my sheet , and
leave me the option to insert a Comment?

I tried to use the data Range Properties command on the External Data
toolbar , but it is constantly greyed out.

Grateful for any ideas...

Try...

In the Sheet_BeforeRightClick event:

Commandbars("Cell").Reset

 

[Colin Hayes]

Colin Hayes presented the following explanation :

Try...

In the Sheet_BeforeRightClick event:

Commandbars("Cell").Reset

Hi Garry

Thanks for getting back again.

Not sure how (or where) to apply this. Can you advise?



Best Wishes

 

[GS]

Colin Hayes expressed precisely :

Hi Garry

Thanks for getting back again.

Not sure how (or where) to apply this. Can you advise?



Best Wishes

Hi Colin,
You can use the wks event behind ThisWorkbook OR the associated event
behind the worksheet containing the imported data. In both cases,
the action loads the menu so you can access it via code. In your case,
though, Reset (works anytime, whether the menu is loaded or not) may
not work as expected and so you could set the "Insert Comment" menu's
Visible prop as follows...

CommandBars("Cell").Controls("Insert Comment").Visible = True


<FYI>
I've implemented similar concept in our Calendar project to handle the
CommentCover feature, whereby the "Insert/Delete Comment" menuitems are
appropriately replaced with our own menuitem. n our project I use the
Sheet_BeforeRightClick event behind ThisWorkbook so it works on all
project sheets. If you only have one or two sheets with imported data
then you'd want to use Worksheet_BeforeRightClick behind the
appropriate sheet.

 

[Colin Hayes]

Hi Colin,
You can use the wks event behind ThisWorkbook OR the associated event behind
the worksheet
containing the imported data. In both cases, the action loads the menu so you can
access it via
code. In your case, though, Reset (works anytime, whether the menu is loaded or
not) may not
work as expected and so you could set the "Insert Comment" menu's Visible prop
as follows...

CommandBars("Cell").Controls("Insert Comment").Visible = True


<FYI>
I've implemented similar concept in our Calendar project to handle the
CommentCover feature,
whereby the "Insert/Delete Comment" menuitems are appropriately replaced with
our own
menuitem. n our project I use the Sheet_BeforeRightClick event behind
ThisWorkbook so it works
on all project sheets. If you only have one or two sheets with imported data then
you'd want to use
Worksheet_BeforeRightClick behind the appropriate sheet.

Hi Garry

OK thanks for that.

No joy , I'm afraid. The use of the Refresh All icon continues to remove
the formatting permissions which were previously in place. It's not
clear why. Excel can be very frustrating at times, I find , but I'll
stick at it.


Best Wishes

 

[GS]

Hi Colin,
You can use the wks event behind ThisWorkbook OR the associated event behind
the worksheet
containing the imported data. In both cases, the action loads the menu so
you can
access it via
code. In your case, though, Reset (works anytime, whether the menu is loaded
or
not) may not
work as expected and so you could set the "Insert Comment" menu's Visible
prop
as follows...

CommandBars("Cell").Controls("Insert Comment").Visible = True


<FYI>
I've implemented similar concept in our Calendar project to handle the
CommentCover feature,
whereby the "Insert/Delete Comment" menuitems are appropriately replaced
with
our own
menuitem. n our project I use the Sheet_BeforeRightClick event behind
ThisWorkbook so it works
on all project sheets. If you only have one or two sheets with imported data
then
you'd want to use
Worksheet_BeforeRightClick behind the appropriate sheet.

Hi Garry

OK thanks for that.

No joy , I'm afraid. The use of the Refresh All icon continues to remove the
formatting permissions which were previously in place. It's not clear why.
Excel can be very frustrating at times, I find , but I'll stick at it.


Best Wishes

By what means was your 'formatting permissions' in place? Are you
talking about sheet protection permissions? If so, simply reset them as
exampled in our Calendar project. (What I suspect is going on is the
RefreshData process is simply reapplying protect without your
permission settings, though I don't understand the why/how of that!)

 

[Colin Hayes]

By what means was your 'formatting permissions' in place? Are you talking about
sheet protection
permissions? If so, simply reset them as exampled in our Calendar project. (What I
suspect is
going on is the RefreshData process is simply reapplying protect without your
permission settings,
though I don't understand the why/how of that!)

Hi Garry

Well , what I have is a protected sheet where the right-click menu
allows the insertion of a Comment.

I run 'Refresh All' , and the 'Insert Comment' option is no longer
available. If I save now , I've lost it. <g>

That's the whole of the issue , really. I'm just trying to get it to
leave the 'insert comment' function in place in the right-click menu.



Best Wishes

 

[GS]

After serious thinking Colin Hayes wrote :

Hi Garry

Well , what I have is a protected sheet where the right-click menu allows the
insertion of a Comment.

I run 'Refresh All' , and the 'Insert Comment' option is no longer available.
If I save now , I've lost it. <g>

That's the whole of the issue , really. I'm just trying to get it to leave
the 'insert comment' function in place in the right-click menu.



Best Wishes

Ok, so if the sheet is protected then that protection has somehow been
re-applied WITHOUT your protection permissions. What I suggested was to
RESET protection as per the example in our Calendar project. (That's
the procedure that unprotects, then protects because sheet must be
unprotected for new protection to be effective)