Macro enabled workbooks wont open in Excel 2016

Question

I have two sheets in a workbook. One is protected accept about two dozen cells. The other is not protected. When I run a macro to copy and paste 19 cells from the unprotected sheet to 19 cells in the protected sheet, 8 of the pastes go to unprotected cells to allow the user to edit those cells. After the macro runs, the 8 cells are protected.

I unprotect the sheet, format the 8 cells as unlocked AGAIN, save, protect sheet, the cells are unlocked as they should be, until I run the macro.

Private Sub Workbook_Open()
'    Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
'    Application.DisplayFormulaBar = False
'    Application.DisplayStatusBar = Not Application.DisplayStatusBar
'    ActiveWindow.DisplayWorkbookTabs = False
    Sheets("user").Protect Password:="1goodjob", _
    UserInterFaceOnly:=True
End Sub

Any help would be greatly appreciated!

Thanks, Mike

wyn hopkins · Answer

Hi Michael&nbsp;Can you clarify what your issue is and post the code for your copy/paste action&nbsp;Thanks&nbsp;Wyn

michael simpler · Answer

Hi Wyn, and thanks for the reply.&nbsp; I just resolved the issue with a paste special.&nbsp;&nbsp;BUT, I have another issue you may be able to help me with.&nbsp; I have a user form to search previous records in a table on another sheet named "data base" and load them in to a worksheet.&nbsp; The form has a list box displaying the first 6 columns from the data base, a textbox to enter what to search for, and control buttons.&nbsp; The Find button has a find next code, and the load button copies info from the selected row of data in the data base to various cells in a work sheet.&nbsp;The codes work great, but they work in the "data base" sheet, and totally ignor the list box on the user form. When I step through the code in split screen I see it go right to the sheet.&nbsp; How do I get the command buttons to work with the list box in the same user form?&nbsp;Thanks, Mike&nbsp;Option Explicit__________________________________________________________Private Sub CommandButton3_Click()' laods record into work sheet'' Clearing user worksheet before loading&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("D4").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("D6:E14").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("F4").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("E6:E14").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("H4").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("G6:G14").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("J4").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("I6:I14").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("L4").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("K6:K14").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("N4").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("M6:M14").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("P4").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("O6:O14").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("R4").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("Q6:Q14").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("T4").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("S6:S14").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("F18").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("E20:E28").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("H18").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("G20:G28").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("J18").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("I20:I28").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("L18").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("K20:K28").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("N18").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("M20:M28").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("P18").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("O20:O28").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("R18").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("Q20:Q28").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("T18:T29").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("C_Comments") = "Verylak base, Hentzen R, B, W, L, Verylak N, Y"&nbsp;&nbsp;&nbsp; Range("C_Sales_Order_No.").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("C_Customer_Name").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("C_Color_Name") = "none"&nbsp;&nbsp;&nbsp; Range("C_Manufacturer") = "none"&nbsp;&nbsp;&nbsp; Range("C_Date") = Date&nbsp;&nbsp;&nbsp; Range("C_Specie").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp;&nbsp; Range("C_New_Batch_Base").Select&nbsp;&nbsp;&nbsp; Selection.ClearContents&nbsp;&nbsp; &nbsp;' Loading&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 1).Select&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Sales_Order_No.").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SkipBlanks:=False, Transpose:=False&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 2).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Customer_Name").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SkipBlanks:=False, Transpose:=False&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 3).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Specie").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SkipBlanks:=False, Transpose:=False&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 4).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Color_Name").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SkipBlanks:=False, Transpose:=False&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 5).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Manufacturer").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SkipBlanks:=False, Transpose:=False&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 6).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Date").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SkipBlanks:=False, Transpose:=False&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 7).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Comments").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SkipBlanks:=False, Transpose:=False&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 8).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Base").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 9).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Red").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 10).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Brown").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 11).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Black").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 12).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Yellow").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 13).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_White").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 14).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Blue").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 15).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Misc1").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 16).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Misc2").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 17).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_TotalVolume").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 18).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_Can").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SkipBlanks:=False, Transpose:=False&nbsp;&nbsp;&nbsp; Sheets("data base").Select&nbsp;&nbsp;&nbsp; Cells(Application.ActiveCell.Row, 19).Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Range("A2").Select&nbsp;&nbsp;&nbsp; Sheets("user").Select&nbsp;&nbsp;&nbsp; Range("C_CanStain").Select&nbsp;&nbsp;&nbsp; ActiveSheet.Paste&nbsp;&nbsp;&nbsp; Range("T20:T28").Select&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Selection.Copy&nbsp;&nbsp;&nbsp; Range("D6:D14").Select&nbsp;&nbsp;&nbsp; Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; SkipBlanks:=False, Transpose:=False&nbsp;&nbsp;&nbsp; Application.CutCopyMode = False&nbsp;&nbsp;&nbsp; Range("C_New_Batch_Base").Select&nbsp;&nbsp;&nbsp; frmSearch.HideEnd Sub____________________________________________________________________Private Sub CommandButton4_Click()' search with FIND NEXT loop&nbsp;&nbsp;&nbsp; Dim SearchRange As Range&nbsp;&nbsp;&nbsp; Dim WantCell As Range&nbsp;&nbsp;&nbsp; Dim Wanted As String&nbsp;&nbsp;&nbsp; Dim FirstWantCell As String&nbsp;&nbsp;&nbsp; Wanted = tbxSenter.Value&nbsp;&nbsp;&nbsp; Set SearchRange = Range("A2", Range("F2").End(xlDown))&nbsp;&nbsp;&nbsp; Set WantCell = SearchRange.Find(What:=Wanted, MatchCase:=False, LookAt:=xlPart)&nbsp;&nbsp;&nbsp; If WantCell Is Nothing Then&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; MsgBox "No records found."&nbsp;&nbsp;&nbsp; Else&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; FirstWantCell = WantCell.Address&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Do&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; WantCell.Select&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Set WantCell = SearchRange.FindNext(WantCell)&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Loop While WantCell.Address &lt;&gt; FirstWantCell&nbsp;&nbsp;&nbsp; End IfEnd Sub_______________________________________________________________________Private Sub lbxSearchResult_Click()End Sub____________________________________________________________________Private Sub UserForm_Click()End Sub

Forum Discussion

Macro enabled workbooks wont open in Excel 2016

Share

Resources