RESET BUTTON

[CandiC]

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
C77 and D9
C1111 and D13
C1515 and D17
C919 and D21
C2323 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.

 

[Simon Lloyd]

You didnt give very much information so i assume you want the cell
locked again
Use this code in the worksheet code module that you are working o
(right click sheet tab, view code and paste in), don't for get to chang
the password whatever you want it to be

Code
-------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range
Dim rng As Rang
If Target.Address <> "$C$42" Then Exit Su
If Target.Cells.Count > 1 Then Exit Su
Set rng = Me.Range("D23,C77,D9,C1111,D13,C1515,D17,C919,D21,C2323,D25"
Me.Unprotect Password:="password
rng.Locked = Tru
Me.Protect Password:="password
End Su
-------------------

can anyone help me create a reset button in cell C42 of my work sheet t
lin
to a macro that will reset unlocked cells on a worksheet, for exampl
th
cells on

D
D
C77 and D
C1111 and D1
C1515 and D1
C919 and D2
C2323 and D2

Will need to be reset everytime we use this. However, I need hel
creating
button and writing a macro to do this

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Gord Dibben]

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

 

[JLatham]

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("D23,C77,D9,C1111,D13,C1515,D17,C919,D21,C2323,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.