+ Reply to Thread
Results 1 to 5 of 5

Auto-Sort Pre-populated Column on Hidden Sheet, Protected Workbook

  1. #1
    Registered User
    Join Date
    05-09-2017
    Location
    Lombard, IL
    MS-Off Ver
    Excel 2010
    Posts
    2

    Auto-Sort Pre-populated Column on Hidden Sheet, Protected Workbook

    Hi All,

    My VBA experience consists of recording the occasional macro and copy/pasting code from websites like this to achieve what I need. However, my current need requires an understanding I simply do not have. I'm hoping that one of you fine citizens will help me.

    I work with an image analysis software that generates reports via Excel templates. Templates are customizable, with coded tags telling the application which data to put where. Order of generation is as follows:

    Select template from within app > app opens/scans XLSM copy of template > data transfers according to presence of coded tags > prompt to name/save XLSM
    > file written to Windows directory


    I am using this tool to transfer a single column of numbers to Excel. The numbers come over in the order items were analyzed on the image, but I need them in ascending order. While I would manually sort, the following conditions complicate things:

    1. The sheet containing the transferred data will be hidden (its cells are referenced for display on another sheet)
    2. The sheet referencing the data cells will be locked from sorting
    3. The workbook itself will be protected to prevent unhiding the data sheet
    4. My network is set up so that I cannot modify files after being saved to the directory (for security)

    Therefore, I need a macro to sort my one column of data located on a hidden sheet in ascending fashion, and I seek to have it apply upon the save operation of the reporting procedure above (in bold) via "Private Sub Workbook_AfterSave(ByVal Success As Boolean)".

    Attached is a stripped down version of the template I'm using. "Data" (Sheet2) is where I'm aiming the transfer (Column C). "Report" (Sheet1) is the display page.

    With the help of a colleague, I have put together a bit of code, but clearly it does not meet the conditions above, nor does it apply upon the save operation of the file as intended:

    Private Sub Workbook_AfterSave(ByVal Success As Boolean)

    On Error Resume Next

    If Not Sheet2.Visible Then
    If Not Intersect(Range("C1"), Range("C:C")) Is Nothing Then
    Range("C1").Sort key1:=Range("C2"), _
    order1:=xlAscending, Header:=xlYes, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom
    End If
    End If

    End Sub


    Any help you can provide is MUCH APPRECIATED!
    Attached Files Attached Files

  2. #2
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto-Sort Pre-populated Column on Hidden Sheet, Protected Workbook

    Just to make sure I got it straight:
    1. The sheet Data in your sample file will be the hidden sheet
    2. You only want to sort column C (Max Diameter) ? what about the rest, if you sort that column only the rest of the columns's rows will be out of sync.
    3. If you want the data to be sorted when saving DO NOT USE AFTER SAVE I would think Before_Save

    Hope you let me know fast, it's close to midnight here.
    ---
    Hans
    "IT" Always crosses your path!
    May the (vba) code be with you... if it isn't; start debugging!
    If you like my answer, Click the * below to say thank-you

  3. #3
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto-Sort Pre-populated Column on Hidden Sheet, Protected Workbook

    Try this the Data sheet is hidden you can only unhide it using VBA or the VBA editor window
    The data is sorted when the workbook is saved. the sort macro is in Module1 so you can use it to sort it when you want.
    I placed some dummy diameter data in the data column C and it sorts ONLY COLUMN C like you wanted
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    05-09-2017
    Location
    Lombard, IL
    MS-Off Ver
    Excel 2010
    Posts
    2

    Re: Auto-Sort Pre-populated Column on Hidden Sheet, Protected Workbook

    Dear Keebellah,

    You have saved me! Thank you so very much for your efforts. It works wonderfully. This more-than-novice-VB-guy is extremely grateful. You made my week. Thanks a million!!!

  5. #5
    Forum Expert Keebellah's Avatar
    Join Date
    01-12-2014
    Location
    The Netherlands
    MS-Off Ver
    Office 2021 (Windows)
    Posts
    7,900

    Re: Auto-Sort Pre-populated Column on Hidden Sheet, Protected Workbook

    Happy to help and happy coding

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. VBA auto sort multicolumn table when data is changed or deleted in protected sheet mode
    By sahasranama in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-24-2019, 04:37 AM
  2. [SOLVED] How to populated Listbox from Hidden sheet with rowsource property
    By ImranBhatti in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 01-25-2018, 09:20 AM
  3. Automatically Sort data in three columns when I update column E (Protected Sheet)
    By pwhittak in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 03-09-2016, 08:12 AM
  4. [SOLVED] Userform Populated By Drop Down List Of Range On Another Protected Workbook
    By BDBJ1 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 01-05-2016, 07:18 AM
  5. protected sheet Vs column hidden
    By tomluigi in forum Excel General
    Replies: 8
    Last Post: 01-01-2013, 08:20 AM
  6. [SOLVED] Auto sort on a protected sheet
    By LuisCotera in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 12-22-2012, 11:42 AM
  7. Auto Filter - Protected sheet/workbook
    By ronwill in forum Excel General
    Replies: 3
    Last Post: 01-10-2006, 11:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1