#1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 37
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 22,906
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 2,203
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Reset Button Raz Excel Worksheet Functions 3 November 27th 08 12:32 AM
Reset Button Raz Excel Worksheet Functions 4 November 26th 08 06:26 PM
Reset button in my spreadsheet GT Excel Discussion (Misc queries) 3 August 8th 07 01:18 PM
Reset Button trilogy Excel Discussion (Misc queries) 0 July 27th 06 03:36 AM
Reset Button in Excel? Jan C. Excel Worksheet Functions 3 July 2nd 05 02:08 AM


All times are GMT +1. The time now is 01:11 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"