Share via

Data Validation is not working when I close And reopen the workbook

Anonymous
2023-05-09T06:08:32+00:00

I write a formula in Data validation and after closing, reopening the workbook the data validation is not working but once I open data validation again and click ok then its working Fine Can someone Provide solution to this problem please.

Microsoft 365 and Office | Excel | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments

Answer accepted by question author

Anonymous
2023-05-10T14:19:25+00:00

Hi Bokkisam!

Thank you for the feedback.

Perhaps, you can use VBA (Visual Basic for Applications) code to automate the process of applying data validation every time the workbook is opened

Here's an example of how you can do this: Open your workbook and press Alt + F11 to open the VBA editor.

In the editor, double-click on the "ThisWorkbook" object in the Project Explorer window to open the code window for that object.

In the code window, paste the following code: Private Sub Workbook_Open() Dim rng As Range Set rng = Range("A1:A10") 'Replace A1:A10 with the range you want to apply data validation to With rng. Validation . Delete 'delete any existing validation . Add Type:=xlValidateWholeNumber, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:="1", Formula2:="100" 'replace with your data validation settings . IgnoreBlank = True . InCellDropdown = True . InputTitle = "" . ErrorTitle = "" . InputMessage = "" . ErrorMessage = "" . ShowInput = True . ShowError = True End With End Sub Replace "A1:A10" with the range you want to apply data validation to.

Replace the validation settings in the . Add method with the settings you want to apply to the range.

Save the VBA code and close the VBA editor.

Now, every time the workbook is opened, the data validation rule will be automatically applied to the specified range. Note that the user will need to enable macros in order for this to work.

Kind Regards, Shakiru

Was this answer helpful?

2 people found this answer helpful.
0 comments

2 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2023-05-10T06:32:18+00:00

    Hi Shakiru S!

    I want the data validation to apply automatically every time I open the workbook. If I am going to share this my friends, they can't reapply data validation every time. So, I am trying to automate the process.

    can you help me to automate the process.

    Thank you for replying Shakiru.

    Best Regards,

    Bokkisam

    Was this answer helpful?

    0 comments
  2. Anonymous
    2023-05-09T07:05:38+00:00

    Hi Bokkisam V!

    There could be several reasons why your data validation is not working after reopening the workbook in MS Excel.

    Here are a few solutions you can try: * Enable Automatic Calculation: Ensure that automatic calculation is enabled in Excel. To do this, go to the Formulas tab, click on Calculation Options, and select Automatic. This ensures that any changes made to the workbook are calculated automatically.

    * Reapply Data Validation: Try reapplying data validation to the cells after reopening the workbook. To do this, select the cells with data validation, go to the Data tab, click on Data Validation, and then click OK without making any changes. This will reapply the validation rules to the selected cells.

    Kindly let me know, if you require additional assistance, I will be glad to help further.

    Best Regards, Shakiru

    Was this answer helpful?

    0 comments