Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RESET BUTTON
can anyone help me create a reset button in cell C42 of my work sheet to link
to a macro that will reset unlocked cells on a worksheet, for example the cells on , D2 D3 C7:D7 and D9 C11:D11 and D13 C15:D15 and D17 C9:D19 and D21 C23:D23 and D25 Will need to be reset everytime we use this. However, I need help creating a button and writing a macro to do this. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RESET BUTTON
Are those the only cells that would be unlocked?
Reset means clearcontents? Assuming worksheet is currently protected. Sub UnLocked_Cells() Dim Cell As Range, tempR As Range, rangeToCheck As Range 'rotate through all cells in the selection Cells.Select For Each Cell In Intersect(Selection, ActiveSheet.UsedRange) If Not Cell.Locked Then 'add to tempR if unprotected If tempR Is Nothing Then 'do it this way for the first unprotected cell Set tempR = Cell Else 'add subsequent cells this way. Set tempR = Union(tempR, Cell) End If End If Next Cell 'do this upon completion of the For..Next checking If tempR Is Nothing Then MsgBox "There are no UnLocked cells in " & _ "the selected range." End End If 'select qualifying cells tempR.ClearContents End Sub Gord Dibben MS Excel MVP On Mon, 2 Nov 2009 12:34:02 -0800, CandiC wrote: can anyone help me create a reset button in cell C42 of my work sheet to link to a macro that will reset unlocked cells on a worksheet, for example the cells on , D2 D3 C7:D7 and D9 C11:D11 and D13 C15:D15 and D17 C9:D19 and D21 C23:D23 and D25 Will need to be reset everytime we use this. However, I need help creating a button and writing a macro to do this. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
RESET BUTTON
I find that using the 'text box' from the Drawing toolbar is an easy way to
create a button that I can attach to a macro. Simply draw a small text box and make the text something like "Reset" and position it within cell C42. Set the properties to move with the cell. Add this code to your workbook in a regular code module. Note that there is really only one line of code in the sub, begins with ActiveSheet. and continues unbroken all the way through .ClearContents Sub ClearRange() ActiveSheet.Range("D2:D3,C7:D7,D9,C11:D11,D13,C15: D15,D17,C9:D19,D21,C23:D23,D25").ClearContents End Sub To get it into your workbook, open the workbook, press [Alt]+[F11] to open the VB Editor. In there, choose Insert -- Module. Then copy and paste the code above into that new module. Go back to C42 and your 'button'. Right-click near the edge of the button and choose "Assign Macro" from the popup list and choose the ClearRange entry in the available macros list. You're pretty much done. But to keep people from being able to use that macro when on some other sheet through Tools -- Macro -- Macros; go back into the VB Editor and change Sub ClearRange() to Private Sub ClearRange() This removes the macro from the Tools--Macro--Macros list, but leaves it attached to the button. By the way, having used a text box as a button, you can also now right-click on it and 'pretty it up' by using the "Format Text Box" option in the list to set things like the color of the button, text and its edge/border/lines. Hope this helps. "CandiC" wrote: can anyone help me create a reset button in cell C42 of my work sheet to link to a macro that will reset unlocked cells on a worksheet, for example the cells on , D2 D3 C7:D7 and D9 C11:D11 and D13 C15:D15 and D17 C9:D19 and D21 C23:D23 and D25 Will need to be reset everytime we use this. However, I need help creating a button and writing a macro to do this. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Reset Button | Excel Worksheet Functions | |||
Reset Button | Excel Worksheet Functions | |||
Reset button in my spreadsheet | Excel Discussion (Misc queries) | |||
Reset Button | Excel Discussion (Misc queries) | |||
Reset Button in Excel? | Excel Worksheet Functions |