Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to clear contents of unprotected cells AND drop down boxes
Hi there
I have the following macro which clears the contents of unprotected cells on a sheet. It stops with the error message "400", however, when it gets to the drop down boxes on the sheet within the selection. Here is the macro: Sub clearunprotectedcells() Dim cell As Range Range("A3:I59").Select For Each cell In Selection If cell.Locked = False Then cell.ClearContents End If Next End Sub Does anybody have a way of modifying the script so it will clear all the drop down boxes (they are all unprotected, incidentally!) AS WELL AS the unprotected cells? any help gratefully recieved. please do not hesitate to contact if you need more info cheers jb |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to clear contents of unprotected cells AND drop down boxes
Hi there
as well as the request below (!), if anyone has any bright ideas on how i can get it to clear Merged Cells as well, that would be great. The "400" warning is coming up over those aswell... Many thanks again jb "JB2010" wrote: Hi there I have the following macro which clears the contents of unprotected cells on a sheet. It stops with the error message "400", however, when it gets to the drop down boxes on the sheet within the selection. Here is the macro: Sub clearunprotectedcells() Dim cell As Range Range("A3:I59").Select For Each cell In Selection If cell.Locked = False Then cell.ClearContents End If Next End Sub Does anybody have a way of modifying the script so it will clear all the drop down boxes (they are all unprotected, incidentally!) AS WELL AS the unprotected cells? any help gratefully recieved. please do not hesitate to contact if you need more info cheers jb |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to clear contents of unprotected cells AND drop down boxes
RE the merged cells you could do this:
Sub a() Dim Cell As Range For Each Cell In Range("A3:I59") If Cell.Locked = False Then Cell.MergeArea.ClearContents End If Next End Sub I don't understand the other issue. -- Jim "JB2010" wrote in message ... | Hi there | | | as well as the request below (!), if anyone has any bright ideas on how i | can get it to clear Merged Cells as well, that would be great. | | The "400" warning is coming up over those aswell... | | | Many thanks again | | | jb | | "JB2010" wrote: | | Hi there | | | I have the following macro which clears the contents of unprotected cells on | a sheet. It stops with the error message "400", however, when it gets to the | drop down boxes on the sheet within the selection. | | Here is the macro: | | | | Sub clearunprotectedcells() | | Dim cell As Range | Range("A3:I59").Select | For Each cell In Selection | If cell.Locked = False Then | cell.ClearContents | End If | Next | End Sub | | | Does anybody have a way of modifying the script so it will clear all the | drop down boxes (they are all unprotected, incidentally!) AS WELL AS the | unprotected cells? | | | any help gratefully recieved. please do not hesitate to contact if you need | more info | | | cheers | | | jb | |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Macro to clear contents of unprotected cells AND drop down box
Jim
you're a legend. many thanks for your help the "400" thing comes up because of the merged cells, not the drop downs (i had drop downs that were in merged cells & i originally mis-identified the cause of the problem). All sorted now. its so simple when you know how apologies for being a luddite cheers jb "Jim Rech" wrote: RE the merged cells you could do this: Sub a() Dim Cell As Range For Each Cell In Range("A3:I59") If Cell.Locked = False Then Cell.MergeArea.ClearContents End If Next End Sub I don't understand the other issue. -- Jim "JB2010" wrote in message ... | Hi there | | | as well as the request below (!), if anyone has any bright ideas on how i | can get it to clear Merged Cells as well, that would be great. | | The "400" warning is coming up over those aswell... | | | Many thanks again | | | jb | | "JB2010" wrote: | | Hi there | | | I have the following macro which clears the contents of unprotected cells on | a sheet. It stops with the error message "400", however, when it gets to the | drop down boxes on the sheet within the selection. | | Here is the macro: | | | | Sub clearunprotectedcells() | | Dim cell As Range | Range("A3:I59").Select | For Each cell In Selection | If cell.Locked = False Then | cell.ClearContents | End If | Next | End Sub | | | Does anybody have a way of modifying the script so it will clear all the | drop down boxes (they are all unprotected, incidentally!) AS WELL AS the | unprotected cells? | | | any help gratefully recieved. please do not hesitate to contact if you need | more info | | | cheers | | | jb | |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can cells be drop down boxes and still allow custom text? | Excel Discussion (Misc queries) | |||
Selecting from a Validation Drop Down Box aborts macro | Excel Worksheet Functions | |||
Copy drop down boxes and associate to cells dynamically in excel | Excel Worksheet Functions | |||
Drop Down Boxes for Validation Cells are Too Wide | Excel Discussion (Misc queries) | |||
automatic color change in cells using a drop down list | Excel Worksheet Functions |