Question about worksheet protection?

mattbird

Active Member
Joined
Oct 15, 2013
Messages
305
Office Version
  1. 2016
Hi,

I have protected my workbook and have found one of my button is not working. The button un-hides a tab normally when the sheet protection is on by now I have protected the workbook it gives a run-time error '1004' unable to set the visible properties of the worksheet class. I can't work out how to get it to work the code I am currently using is below:

Code:
Sub BCMDatabase_ViewStats_TextBox1_Click()    Sheets("Stats 1").Visible = True
    Application.Goto Worksheets("Stats 1").Range("B5")
    
End Sub

One the sheet opens I then have another button on the new sheet (Stats 1) which returns the user to the main sheet (BCM Database) and hides the tab gain, But again I get the same message run-time error '1004' unable to set the visible properties of the worksheet class. The code I am using for the return is below:

Code:
Sub Stats1_Return_TextBox1_Click()    Application.Goto Worksheets("BCM Database").Range("L15")
    Sheets("Stats 1").Visible = False
    
End Sub


Why is this happening and how do I get the codes to work when the workbook is protected?

Matt
 
Last edited:

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Hi there,

In general and by recollection (I'm fairly "blond", so banish all hope), for Worksheet protection, setting the fourth arg ( UserInterfaceOnly:= ) to TRUE will eliminate a number of problems. Yours seem to be more at Workbook protection though, so try .Unprotect before making changes to the workbook level (such as sheet visibility) and after you've re-hidden the sheet, (re-) .Protect the workbook.

Hope that helps,

Mark
 
Upvote 0
Thanks for the quick reply. Sorry I don't quite follow I am not that good on excel. Do I have to put unprotect into the code? The reason I am protecting the workbook is because it stops people right clicking the tab and changing things ie tab name, colour etc.

Matt
 
Upvote 0
Sample of only your first one:
Try

Code:
Sub BCMDatabase_ViewStats_TextBox1_Click()
With Sheets("Stats 1")
             .unprotect password:="hi"   '<< If there is a pw;  If no pw then change "hi" to ""
             .Visible = True
             .Range("B5") = "whatever update you need"
             .protect password:="hi"    '<< If there is a pw;  If no pw then change "hi" to ""
End with   
End Sub
 
Upvote 0
Hi, thanks for the code.

I think it is sore of working when the workbook is protected it flashes to worksheet 'stats 1' and back to main worksheet when button is pressed, but I can't view the stats page now. I have also noticed it enters data into range B5 on worksheet 'Stats1' which I don't want it to do, just want it to open on B5. any idea's?

Matt
 
Upvote 0
I have managed to get it to open the Stats 1 worksheet by changing one line of code (see below) which works when the worksheet is protected by again when I protect the workbook I get run-time error '1004' Unable to set visible property of the worksheet class.

Code:
Sub BCMDatabase_ViewStats_TextBox1_Click()
With Sheets("Stats 1")
             .Unprotect Password:="hi"   '<< If there is a pw;  If no pw then change "hi" to ""
             .Visible = True
             .Application.Goto Worksheets("Stats 1").Range("B5")
             .Protect Password:="hi"    '<< If there is a pw;  If no pw then change "hi" to ""
End With
End Sub

Matt
 
Last edited:
Upvote 0
You may want to comment out or remove the below line in your code as it "prevents you from making any changes to your cell Range("B5") ".

.Protect Password:="hi" << remove this line

But then in the "Stats 1" ThisWorkbook" code module - select the Workbook_BeforeClose Event and enter the single code line: me.Protect Password:="hi"

Hope that helps..
 
Upvote 0
I have tried the first part by removing the password part but still get run-time error '1004' Unable to set visible property of the worksheet class, and when I click on Debug in the message box it highlights '.Visible = True'​ in yellow.

Code:
Sub BCMDatabase_ViewStats_TextBox1_Click()
With Sheets("Stats 1")
             .Unprotect Password:="meme"
             .Visible = True
             .Application.Goto Worksheets("Stats 1").Range("B5")
        End With
        
End Sub
 
Upvote 0
probably won't make a difference but try

Code:
 .Visible = xlSheetVisible
 
Upvote 0

Forum statistics

Threads
1,213,496
Messages
6,113,993
Members
448,539
Latest member
alex78

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