Command button to deleat cell data in other sheet

[vickypohadare]

Can u pls help me...
How can i creat command button in sheet1 which can deleat data in sheet2 cell of excel

 

[Momentman]

WELCOME TO THE FORUM
1. With Sheet1 active, Go to Developer Tab(if it's not visible, go to Excel options>Customise Ribbon> Tick the check box beside the developer tab)
2. Under the CONTROLS group, click Insert>Form Controls>Button (Form Controls)
3. Drag and drop to size and Position the button
4. After the button is placed, Excel brings a dialog box asking you to choose a macro to asign to the button, Select the new button(at the right)
5. It opens the Visual basic editor and you can add a line like

Sheets("Sheet2").Range("A1:D4").Delete

to delete data on Sheet2 from A1 to D4.

The final code might look something like this:

Sub Button1_Click()
    Sheets("Sheet2").Range("A1:D4").Delete
End Sub

 

[wellsr]

If you want to delete ALL the data in Sheet2, follow these steps:

1) Open your VBA editor (Alt+F11) and insert a module.
2) Paste the following code:

Sub ClearSheet2()
    Sheets("Sheet2").Cells.Clear
End Sub

3) Make sure you're on Sheet1.
4) From your developer tab, click Insert>Button (Form Control).
5) Draw the button wher you want it.
6) Click ClearSheet2 on the "Assign Macro" dialog box.

That's it - now when you click the button on Sheet1, all the data in Sheet2 will clear.

 

[vickypohadare]

Thanks guys its very usefull to me..

When i use below code

Sheets("Sheet2").Range("A1:D4").Clear

Then whole formated cell is deleted

But u i want to clear only NUMBER or TEXT in that cell not the border and colour of that cell.

 

[wellsr]

I'm glad we could help! You can change .clear to .ClearContents to only delete the number/text.

 

[vickypohadare]

Thank u so much.......its working.....dear