VBA being applied to other Workbooks

marvin jones

Board Regular
Joined
Mar 16, 2016
Messages
86
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
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.
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
In the immediate window, type Application.CommandBars("Ply").Enabled = True and press Enter.
 
Last edited:
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0

Forum statistics

Threads
1,213,565
Messages
6,114,338
Members
448,569
Latest member
Honeymonster123

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
Back
Top