none
sorting a protected worksheet RRS feed

  • Question

  • I have the following problem, I have a worksheet (stock) where I put in my stock and what I want to order, when I click the button the name and amount gets copied to another worksheet (bestelling) the problem is that it doesn't get copied in order so I want to sort it again, but that doesn't work because the sheet is protected (at least I think that is the problem) the code I used for this is code that works in another sheet. can someone help me please

    Private Sub Bestellen_Click()
     Dim wshS As Worksheet
         Dim wshT As Worksheet
         Dim s As Long
         Dim m As Long
         Dim t As Long
         Application.ScreenUpdating = False
         Set wshS = Worksheets("Stock") ' Source worksheet,  change name
         Set wshT = Worksheets("Bestelling") ' Target worksheet,  change name
         m = wshS.Range("D" & wshS.Rows.Count).End(xlUp).Row ' Last used source row
         t = wshT.Range("A" & wshT.Rows.Count).End(xlUp).Row ' Last used target row
         For s = 3 To m ' Loop through the rows of the source sheet,  starting at row 2
             If wshS.Range("D" & s).Value <> "" Then ' Column D has a value
                 t = t + 1 ' Increment target row
                 wshT.Range("A" & t).Value = wshS.Range("A" & s).Value ' Copy name
                 wshT.Range("F" & t).Value = wshS.Range("D" & s).Value ' Copy amount
             End If
         Next s
         Application.ScreenUpdating = True
         Range("D3:D200").ClearContents
         
    'code nog verder aanpassen zodat alles op de bestelling gesorteerd wordt volgens merk en dan volgens naam.
    'hier gewoon nog aan toevoegen dat actieve werkblad bestelling moet zijn en het zou moeten werken
    Application.ScreenUpdating = False
    Application.Worksheets("Bestelling").Select
    'Worksheets("Bestelling").Protect userinterfaceonly:=True
    Worksheets("Bestelling").Range("A4").Select
         Set tbl = ActiveCell.CurrentRegion
            tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _
            tbl.Columns.Count).Sort _
            Key1:=Range("B3"), Order1:=xlAscending, _
            Key2:=Range("A3"), Order2:=xlAscending, _
            Header:=xlYes
    'Worksheets("Bestelling").Protect userinterfaceonly:=False
    Application.Worksheets("Stock").Select
    Application.ScreenUpdating = True
    End Sub

    Tuesday, July 25, 2017 8:27 PM

All replies

  • I've tried the code you provided, and got run-time error.
    Haven't you experienced it?



    Ashidacchi


    • Edited by Ashidacchi Wednesday, July 26, 2017 1:30 AM
    Wednesday, July 26, 2017 1:30 AM
  • with me it gives an error in the line above your selected part. but this same code in a different page does not give an error... and I think that's because of the protection or something else
    Wednesday, July 26, 2017 6:08 AM
  • Could you share your Excel file via cloud storage such as OneDrive, Dropbox, etc?
    [note] Please modify/delete your private data before sharing.

    Ashidacchi

    Wednesday, July 26, 2017 6:33 AM
  • this is the link where the file will be, trying to upload it at the moment

    1drv.ms/

    f/s!AtmnUrlmHFJi3jkDsAi2Vmqc3hj

    you will have to put these two lines together, for some reason I can't post a link... account needs to be verified... i'll look in to this

    Thursday, July 27, 2017 1:31 PM
  • i'll put the file online in a few hours, I can't post it because the network of my work does not allow it
    Thursday, July 27, 2017 1:44 PM
  • 1drv.ms/f/s!AtmnUrlmHFJi3jkDsAi2Vmqc3hj
    won't work as expected: it may be deleted or prohibit to access.

    Ashidacchi

    Friday, July 28, 2017 12:49 AM
  • try this one

    1drv.ms/x/

    s!At1mnUrlmHFJi3lU4rbCefoCTLXc

    Friday, July 28, 2017 4:44 AM
  • I've download an Excel file you've shared. But I cannot find any buttons in it.
    Where are buttons?
    cf. on sheet "Stock", [Bestellen] button should be placed, according to your VBA code.

    Could you send me the file as an attached file via email?
    My email address:
    xxx (dot) xxx (at) gmail (dot) com  <--- masked for privacy (please read received mail) 

    Ashidacchi


    • Edited by Ashidacchi Friday, July 28, 2017 5:39 AM
    Friday, July 28, 2017 4:58 AM
  • Hello,

    >>but that doesn't work because the sheet is protected (at least I think that is the problem) the code I used for this is code that works in another sheet

    If the sheet is proteced, we are unable to sort it. Please unprotect it first and protect after sorting.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, July 28, 2017 6:35 AM
    Moderator
  • I did not get any mail...
    Saturday, July 29, 2017 11:55 AM
  • Hello,

    Your workbook is unavailable. Please try to unprotect your sheet and sort manually to check if the issue causes from protection. If it works, you could unprotect the sheet in your code before sorting and then protect.

    Regards,

    Celeste


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, July 31, 2017 6:43 AM
    Moderator