Protecting/Unprotecting Cells and

allenlen

New Member
Joined
Jul 27, 2005
Messages
24
Developed a spreadsheet where the user will have access to only certain cells while others are locked. The sheet also has a print button with the actual print code in VB. The problem I'm having is the user cannot access the unprotected cells without the sheet printing. The sheet also prints when the user clicks and points on a protected cells. It all seems like one's mouse is linked to the print macro.

Here the code for the print macro:

Code:
Sub Print_Sheet_Click()
'
'Print_Sheet_Click Macro
'
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Note the print code is housed in a single module. I have several worksheets in one workbook that are assigned to the print module. There is also a main login sheet that requires the user to enter their password. Once entered, the user is taken a designated worksheet. The user see's only their worksheet; the other user worksheets are XlVeryHidden.

Note also the cells were lock/unlocked and worksheet protected through excel. I originally formatted all the cells in the worksheet and set the lock to “check”. I than formatted the range of cells that a user could change and set the lock to “uncheck”. Finally, I protected the worksheet. I even tried the reverse (unlock all and then lock followed by protect worksheet). Same results. Any help would be greatly appreciated.
 

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.
Hello, allenlen,

seems like your "buttonrange" is enormous
I don't remember how this can occur, but I've seen it before
I would delete all buttons (using code !! to be sure) and create them again
assuming your button is created with the controls-toolbar (since you used "click")
Code:
Sub remove_controls_within_range()
Dim sh As Shape
Dim rng As Range

Set rng = Cells 'or if you want to restric the range Range("A1:D10")

    For Each sh In ActiveSheet.Shapes
      If sh.Type = msoOLEControlObject Then
        If TypeName(sh.OLEFormat.Object.Object) = "CommandButton" _
        And Not Intersect(sh.TopLeftCell, rng) Is Nothing Then sh.Delete
      End If
    Next sh
End Sub
do you get positive results ?

kind regards,
Erik
 
Upvote 0
Erik, many thanks for the reply. Before I try, the range I have buttoms in are cells A1:K10. Do I need to set my range in your code? Do I also have to specify the worksheet name? If so, where and how?

Please excuse my ignorance; very novice with all of this.
 
Upvote 0
Before I try
when you copy your workbook, you can try out without any risk
the range I have buttoms in are cells A1:K10. Do I need to set my range in your code?
it there are not to many buttons I wouldn't restrict the range and just let it erase all, so you will be sure
Do I also have to specify the worksheet name? If so, where and how?
you can here, but you don't have to, just be sure the right sheet is active when you run it
For Each sh In ActiveSheet.Shapes
Please excuse my ignorance; very novice with all of this.
don't look to my first posts :oops:

best regards,
Erik
 
Upvote 0

Forum statistics

Threads
1,217,382
Messages
6,136,237
Members
450,000
Latest member
jgp19

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