Protecting/Unprotecting Cells and

[allenlen]

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:

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.

 

[erik.van.geit]

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")

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

 

[allenlen]

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.

 

[erik.van.geit]

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

best regards,
Erik

 

[allenlen]

Many thanks Erik! Will advise if I still have problems.

 

[erik.van.geit]

Is your problem solved ?