Command button for Clearing the data in the spreadsheet

[tv9_rohith]

Hi,

I have to place a command button in the excel to clear the data in particular cells when I click on Commmand button.

and need one more formula for the below :

Only if there is a value in particular Cell then the command button placed in the sheet should get active otherwise it should be disabled.


I hope some will help me.

Thanks
Rohith

 

[jim may]

Here is a sample...
Assuming the use of a Active-X command button (Control toolbox) named CommandButton1

Excel Workbook
	D	E	F	G	H	I	J	K	L
1				999
2
3
4
5
6
7
8
9		123	765	987
10		65	987	654
11		987	765	432
12		321	6654	87
Sheet1

Excel 2007

in the ThisWorkbook module paste in:

Private Sub Workbook_Open()
    Sheet1.CommandButton1.Enabled = False
End Sub

and in your Sheet (the sheet you are working with) module paste in:

Private Sub CommandButton1_Click()
If Range("G1").Value = 999 Then
Range("E9:G12").ClearContents
End If
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$G$1" And Target.Value = 999 Then
CommandButton1.Enabled = True
End If
End Sub

 

[Bieli]

In command button you can use:

activesheet.range("A1")=""


and to enable/disable button you can use:

Private Sub Worksheet_Change(ByVal Target As Range)
If ActiveSheet.Range("A1") = "" Then
ActiveSheet.CommandButton1.Enabled = False
Else
ActiveSheet.CommandButton1.Enabled = True
End If
End Sub

 

[tv9_rohith]

Hi Jim,

what ever you have given me the formula I think it will work but in writing question I hve mixed up 2 things : could you please check and let me know the formulas for 2.

1) there will 2 command buttons in the sheet

Command Button 1--> One should be only activate if there is any value in particular cells - ( I.e., Cell A2, Cell B2) then the Command button 1 should be active if not it should be disabled. - execution formula I have writte .

Command Button 2 --> Only if there are values in the given range then the button should be active if not then the Cummand button should be disabled - and when ever this command button is active it should clear the given range after clicking on the button.

I think this is clear