Posts by StephenR

    Try the following. It deletes validation and then adds data validation back for the number of cells indicated in C1 of sheet1. I've added a worksheet change code which needs to go in the sheet module - right click Sheet1 tab, View Code and paste.


    The rest in a normal module:

    Will you always be selecting 10 modules on Sheet1 though? Or does that vary as well as the number of exams stored on the other sheet?


    I think what will need to do is update the source list every time C1 is changed using a change event so that the selections are made from the right list.

    Amended code above. Not sure what you mean by the total number of exams. Currently the validation list is based on the first column of the table in the second sheet. Are you saying you might only want to select a sub-set of these?

    Try this. I have converted the second sheet to a table and added the exclusions in column 2 onwards. I've not added them all so you'll have to do that (you'll also have to make them reciprocal if you understand what I mean). An advantage of this is that it will expand if you add new entries and the data validation can run off the first column.


    I've also output the errors in a message box. If you don't want then just remove the lines referring to "s".

    Well it is looking for a cell and then it is taking the value of that cell which is "TRUE". In the loop this line is saying

    Code
    ThisWorkbook.Worksheets(rngSht.Value).Visible = (rngSht.Offset(0, 1).Value = "TRUE")


    look for the sheet named the same as the value of the cell rngSht, which is D9 in the first iteration. That's why I suggested you use column A.

    But in your file, D9 contains "TRUE" (your data actually starts in row 5 so not sure why your code starts at 9) so then this line

    Code
    ThisWorkbook.Worksheets(rngSht.Value).Visible = (rngSht.Offset(0, 1).Value = "TRUE")


    is looking for Worksheets("TRUE") which doesn't exist. The offset is column E which is empty.

    Re the first query, can you try adding this line at the top

    Code
    application.displayalerts=false


    and turn it back on at the end?


    Re the second, replace this line

    Code
    wb.Sheets("Summary").Range("B9:K9").Copy _
     ThisWorkbook.Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2)

    with these two lines

    Code
    wb.Sheets("Summary").Range("B9:K9").Copy
    ThisWorkbook.Sheets("Summary").Range("A" & Rows.Count).End(xlUp)(2).pastespecial xlvalues

    You can't create an array by using the literal string "Array". Here is another approach.