Link to home
Start Free TrialLog in
Avatar of HoppaRoppa
HoppaRoppaFlag for United Kingdom of Great Britain and Northern Ireland

asked on

Excel could not save all... with loads of validations

This question has been asked here before but the solutions given do not apply to my particular problem.
I have inherited an Excel sheet which is loaded with code. I had to add even more code to create data validations in two separate columns. The validations have to be added programmatically since the named range of the data validation list depends on the user input on the previous column. It all works great and does what it needs to do. However when the users want to save the sheet if there are more than 1000 rows they get the “Excel could not save all the data and formatting you recently added…” error. Everything gets saved ok; we don’t need the error message at all. Only for the sake of making the sheet more user friendly I would like to remove the error message.
I have searched this issue online and for the record there is no conditional formatting in the sheet. And the sheet is not corrupt – I have tried my code on a blank sheet and get the same error. If I apply data validation to an entire column manually it all works. But doing it via the code gives this error. Here is the code used:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)


  Dim intCurrentRow As Integer
  Dim i As Integer

  intCurrentRow = Target.Row
  i = Range("A65536").End(xlUp).Row

  If intCurrentRow < i Then
        Dim j As Integer
        j = 6
        Do While i >= j

                  If Range("J" & j).Value = "6" Then
                        With Range("K" & j).Validation
                              .Delete
                              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                              xlBetween, Formula1:="=NamedRange"
                        End With
                    Else
                          If Range("J" & j).Value = "1" Or Range("J" & j).Value = "2" _
                          Or Range("J" & j).Value = "3" Or Range("J" & j).Value = "4" Then
                            With Range("K" & j).Validation
                                  .Delete
                                  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                  xlBetween, Formula1:="=TheOtherNamedRange"
                            End With
                          End If
                  End If

                  If Range("X" & j).Value = "6" Then
                        With Range("Y" & j).Validation
                              .Delete
                              .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                              xlBetween, Formula1:="=NamedRange"
                        End With
                   Else
                          If Range("X" & j).Value = "1" Or Range("X" & j).Value = "2" _
                          Or Range("X" & j).Value = "3" Or Range("X" & j).Value = "4" Then
                            With Range("Y" & j).Validation
                                  .Delete
                                  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                  xlBetween, Formula1:="=TheOtherNamedRange"
                            End With
                          End If
                  End If

        j = j + 1
        Loop
    Else

        Do While Range("A" & i) <> ""

                      If Range("J" & i).Value = "6" Then
                            With Range("K" & i).Validation
                                  .Delete
                                  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                  xlBetween, Formula1:="=NamedRange"
                            End With
                       Else
                              If Range("J" & i).Value = "1" Or Range("J" & i).Value = "2" _
                              Or Range("J" & i).Value = "3" Or Range("J" & i).Value = "4" Then
                                With Range("K" & i).Validation
                                      .Delete
                                      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                      xlBetween, Formula1:="=TheOtherNamedRange"
                                End With
                              End If
                      End If
                      If Range("X" & i).Value = "6" Then
                            With Range("Y" & i).Validation
                                  .Delete
                                  .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                  xlBetween, Formula1:="=NamedRange"
                            End With
                      Else
                              If Range("X" & i).Value = "1" Or Range("X" & i).Value = "2" _
                              Or Range("X" & i).Value = "3" Or Range("X" & i).Value = "4" Then
                                With Range("Y" & i).Validation
                                      .Delete
                                      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
                                      xlBetween, Formula1:="=TheOtherNamedRange"
                                End With
                              End If
                      End If
        i = i + 1
        Loop
    End If

End Sub

Open in new window


Any ideas will be greatly appreciated.

Thanks,
ASKER CERTIFIED SOLUTION
Avatar of Faustulus
Faustulus
Flag of Singapore image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of HoppaRoppa

ASKER

Thanks for the quick reply and I do agree with you. Creating all the validations for all of the cells is not the best approach. However, there are different ways a user may select while filling in the sheet. They may enter data row by row - in which case I want the validation only on that cell - and they may copy/paste 1000s of rows of data - in which case I want all the pasted cells to have the validation. What is more annoying is that they may do a combination of both. And after they have selected from the validation list they may want to go back and amend their selection - so the list still needs to be in there.
Can you make data validation work on pasted values? The target cell should assume the format of the source cell. If that cell has validation the target cell will have the same. However, if validation is pasted it is likely that the value meets the validation requirements. I don't find any reason for having validation in this process, either in the source or target cells. If you need to validate pasted values that would not be an issue to be resolved by validation lists and, therefore, not the subject of this discussion.
So, remains the scenario where they enter individual cells to set or modify their values. Those are the cells that need validation which, as I said, can be set when the cell is entered and removed when the value has been approved.
Bear in mind, please, that you can't tell whether a cell has validation or not just by looking at it. You may have tens of thousands of validations in your sheet or none, it will look just the same. A difference only becomes discernible when you enter a cell (select it), for example, for the purpose of modifying its value. That is a process that you can't group. It is one cell at a time. Therefore you only need one validation list - at a time or at any time.
Avatar of Rory Archibald
I'm not really clear why you need code for this at all - you can set up validation that is dependent on other cells using formulas in the validation source box - see for example: http://www.contextures.com/xlDataVal02.html
Faustulus; the reason they have asked me to develop this is that they don't trust the pasted values and further on in the process they validate these values by using even further code. My coding power failed to delete validations as soon as they are created (since the code is in the worksheet selection change event and as soon as the newly created validation list is clicked the validation is deleted before a selection is made... blah blah...).
Anyway, it actually now occured to me that I can indeed use your idea of deleting the validations. As soon as the user has selected all the values I don't need these lists anymore. As I said I didn't spend any more brain power in trying to do this in the same event but I added  the code below in the Before Save event:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
  
  
  Dim i As Integer
  
  i = Worksheets("Sheet1").Range("A65536").End(xlUp).Row
  
   Range("K6:K" & i).Validation.Delete
   Range("Y6:Y" & i).Validation.Delete
End Sub

Open in new window


That did the trick. Thank you very much.

rorya; I have tried that initially. I can't remember now what the problem was but it soon became clear it was not going to be as easy. As I said it is a heavily loaded, properly abused sheet and whatever I add on it destroys something else. It is a neat trick though and I have used it succesfully in the past.
As an after thought:-
Since the workbook saves OK the part that is being lost might be in the various backups that Excel keeps. To escape the error messages you might try disabling "Previous versions".