data validation

[kevin carter]

hi
i have a worksheet (template) that has cell contain data validation(i.e drop
down list)
problem is when i run a macro that saves the information to another
worksheet(output)
it is killing the data validation on the worksheet(template).
Can anyone suggest a reason why macros kill data validation and how i can
fix it?


thanks in advance

kevin

 

[Debra Dalgleish]

Do you actually have a template file open (*.xlt), or is this a copy of
a template file, or a normal workbook?

If you select a cell with data validation, and choose Data>Validation,
can you see the validation criteria that were previously set?

What version of Excel are you using, and does the macro apply a freeze
pane, or other changes to the template?

 

[Paul Watkins]

Hi
Kevin's PC is offline at the moment, I work with him and have the same
problem.

This is a normal workbook (.xls)
If we select the original data validated cell the validation is still there,
but there is no drop down menu in the cell anymore.
Excel 2000
The Macro is designed to save a copy of 'Sheet1' from this book and paste it
into another book.
There are frozen panes on the page, but the macro does not create them.


Paul

 

[Debra Dalgleish]

Choose Tools>Options, View tab, and check that the Objects option is set
to Show All or Show Placeholders.

If that's not it, can you show the code from the macro?

 

[Paul Watkins]

The 'Showall' is ticked.
Here is the macro.
The problem is that it doesn't 'disable' the data validation every time.Sub extractacopy()
Application.ScreenUpdating = False
ThisWorkbook.Sheets("TEMPLATE").Unprotect
ThisWorkbook.Sheets("GRAPHICS").Unprotect
Sheets("REPORT").Visible = xlSheetVisible
ThisWorkbook.Sheets("TEMPLATE").Select
Range("A1:AB59").Select
Selection.Copy
Sheets("REPORT").Select
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
If ThisWorkbook.Worksheets("REPORT").Range("K5").Value = "input" And
ThisWorkbook.Worksheets("REPORT").Range("P5").Value = "output" And
ThisWorkbook.Worksheets("REPORT").Range("U5").Value = "finished" Then
Application.DisplayAlerts = False
strFileName = Range("K6").Value
ThisWorkbook.Worksheets(Array("REPORT")).Copy
ActiveWorkbook.Worksheets("REPORT").Protect
ActiveWorkbook.SaveAs Filename:="C:\TEMP\report\" & strFileName &
".xls"
ActiveWorkbook.Close
Application.DisplayAlerts = True
End If
ThisWorkbook.Sheets("TEMPLATE").Protect
ThisWorkbook.Sheets("GRAPHICS").Protect
Sheets("REPORT").Visible = xlHidden
Sheets("TEMPLATE").Select
Range("Ad11").Select
ThisWorkbook.Save
Application.ScreenUpdating = True
End Sub
Paul

 

[Debra Dalgleish]

I don't see anything in the code that should affect Data Validation.
Freeze Panes affected the dropdown lists in Excel 97, but not later
versions. Some problems have been reported when cells are merged, but
your code doesn't include that.

What cells have data validation? Does the problem last indefinitely, or
if you close Excel and reopen the file do the dropdowns reappear?

 

[Paul Watkins]

Thanks for the reply
The Cells that have data validation are merged.

The problem does not last indefinitely. If we close Excel and re-open the
workbook then the data validation works again.