Workbook_beforeclose deleting data validation list vba issue [SOLVED]

[scurtis32]

Hi.


I grabbed a version of the following code from another forum (ozgrid, Dave Hawley/iwrk4dedpr) and tried to adapt it to my own spreadsheet. However, it seems as though the named ranges I want to delete do not always get deleted. There must be a problem with how I have the code grab the named range from each worksheet and delete the data validation in the named ranges: CostCenterValue and MonthOfRevenue. If anyone can see it or know why I might be having this problem, I would appreciate your help.


Private Sub Workbook_beforeclose(cancel As Boolean)


Dim ws As Worksheet

Dim rRangeCheck As Range


For Each ws In ActiveWorkbook.Worksheets


On Error Resume Next


Set rRangeCheck = ws.Range("CostCenterValue")


On Error GoTo 0


If rRangeCheck Is Nothing Then


Else


With ws

.Range("CostCenterValue").Validation.Delete

.Range("MonthOfRevue").Validation.Delete

End With


End If


Next


End Sub

 

[SirJB7]

Hi, scurtis32!

Checked the proper name of 2nd named range? MonthOfRevenue or MonthOfRevue?

Regards!

 

[scurtis32]

So, I just did a "step into" within the macro and watched as it successfully deleted all the intended data validation lists. There must be an issue on the opening of the spreadsheet when I repopulate the data validation lists, but one particular list is longer than Excel likes. Time to continue exploring. Thanks, I originally thought I had some coding error in the original code but looks like it's happening somewhere else.

 

[SirJB7]

Hi, scurtis32!

As Excel run that macro before closing the workbook, either you have saved it before or not, the workbook got modified, so a prompt for saving it should appear, does it?

If so and if you click on Save button, when you open again are you getting that issue of one of those name data validation condition still active? In this case would you upload the original sample file, previous to the deletions?

If you don't get prompted then there must be an Application.DisplayAlerts set to false in some place.

Regards!

 

[scurtis32]

hi Sir.


Here is a link to the file. Any help you can provide is greatly appreciated. I've tried so many times to get this to work without an error popping up from Excel, but failed. It seems sometimes it works and sometimes it doesn't....


https://docs.google.com/file/d/0B3HJwfXBcmg1Z0JTREQ0LTNGdU0/edit?usp=sharing

 

[SirJB7]

Hi, scurtis32!


I tested your code and adding a previous range setting to nothing it worked fine. I slightly modified your code (moved the On Error Goto 0 upwards because it errors should be only ignored while testing ranges, don't they?, if not keep it in its original position) and changed the If condition to eliminate the empty Then):


-----

[pre]

Private Sub Workbook_beforeclose(cancel As Boolean)

Dim ws As Worksheet
Dim rRangeCheck As Range

For Each ws In Worksheets

ws.Activate

On Error Resume Next

Set rRangeCheck = Nothing
Set rRangeCheck = ws.Range("MonthOfRevue")

On Error GoTo 0

If Not (rRangeCheck Is Nothing) Then

With ws
.Range("CostCenterValue").Validation.Delete
.Range("MonthOfRevue").Validation.Delete
End With

End If

Next

End Sub

[/pre]
-----


Regards!

 

[scurtis32]

Brilliant! Thanks for your help. You were correct regarding the error handling. It was one of the things I moved at one point trying to figure out what was causing it to work some times and not others. It must have been whether the particular sheet that caused the error was the one active at time of close. I didn't realize the variable wouldn't rename when cycling through the worksheets here. But, I'm pretty new to vba so learning something as simple as that isn't so surprising.


Again, thank you. Your help and time is appreciated. Have a great weekend.


Steve

 

[SirJB7]

Hi, scurtis32!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

And remember that if error handling it managed thru Resume Next sentences then its scope should be as reduced as possible.

Regards!