VBA being applied to other Workbooks

marvin jones

Board Regular
Joined
Mar 16, 2016
Messages
85
Hi,

I need to prevent users of a workbook from editing a tab. The code works fine (below) but it has been applied to completely different workbooks now. How do I stop this from happening?

Private Sub Workbook_Open()
Application.CommandBars("Ply").Enabled = False
End Sub

Many thanks
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920
The command Bars Enabled property is an Application property, it applies to all the application (all workbooks)

You could try setting the Enabled property to True in this book's Workbook_Deactivate event.

Or you could password protect the sheet in question and leave the CommandBars as they are.
 

marvin jones

Board Regular
Joined
Mar 16, 2016
Messages
85
Hi Mike,

Thanks for the reply.

Ah, I see, so any change I apply with an application property will cause every other Excel workbook I have to change also? Sorry, I'm very new to VBA.

Is there a way I can prevent tabs from being edited in just one workbook?

The user needs to be able to enter into cells, but that's all i want them to do.

Thanks again, Mike.
 

marvin jones

Board Regular
Joined
Mar 16, 2016
Messages
85
Hi,

I see what you say - if I just protect the sheet and workbook that is just as good, so i will do this.

However - although i have deleted the code below, i can still not edit in all my other workbooks.

Application.CommandBars("Ply").Enabled = False

Any ideas?

Thanks.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,920

ADVERTISEMENT

In the immediate window, type Application.CommandBars("Ply").Enabled = True and press Enter.
 
Last edited:

marvin jones

Board Regular
Joined
Mar 16, 2016
Messages
85
I tried that also, but it didn't make a difference.

The only thing that seems to work is the code below when it is added to ThisWorkbook. I don't want to have to go into every workbook I have to add this code just to get the tab enabled function working again!

Private Sub Workbook_Open()
Application.CommandBars("Ply").Enabled = True

If I remove the code above, it stops me from editing the tabs. Really frustrating.
End Sub
 

marvin jones

Board Regular
Joined
Mar 16, 2016
Messages
85
Fixed it!

Application.CommandBars("Cell").Reset did the trick from within the immediate window.

Thanks for your help Mike. I certainly learnt a lot here - Be careful when using an application property!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,323
Messages
5,600,954
Members
414,417
Latest member
Nobu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top