Clear cell (VBA code)

**Sharp** · 01-15-2007, 08:27 AM

Hi,

using the following code to clear cells on opening a workbook, however it only clears cells on 1 tab, i want to be able to clear cells from different tabs in the same workbook. Any ideas?

a = MsgBox("WOULD YOU LIKE A BLANK FORM ?", vbYesNo)
If a = vbYes Then Cancel = Range( _" "). _
Select
Selection.ClearContents

thanks

**Sharp** · 01-15-2007, 04:10 PM

Hi,

not sure if i made it clear. Basically when a user opens the excel document they are asked if they, "would like a blank form", if yes it delete's the data from the cells identified between the "" marks. It however only deletes the cells in the first tab. I basically need to know how when identifying the cells to delete how i can tell it what tab to delete the cells from,

any ideas??

**VBA Noob** · 01-15-2007, 04:16 PM

Hi,

Try adding your code to the below


Sub ShtSelect()

Dim i As Integer
For i = 1 To Sheets.Count
Sheets(i).Activate
'your code
Next i
End Sub

VBA Noob

**Sharp** · 01-15-2007, 04:59 PM

Hi,

thanks, but now sure it does what i need it to.

Basically when the user opens the file they are asked the quesiton "WOULD YOU LIKE A BLANK FORM ?" (msg box) if the user clicks on yes (they want a blank form) then the code deletes cells A1 and C1 on sheet 1 and deletes cell B2 and D3 on Sheet 2,

any ideas??

**VBA Noob** · 01-15-2007, 05:16 PM

If only four cells can't you just clear the content

 
Sheets("Sheet1").Range("A1,C1").ClearContents
Sheets("Sheet2").Range("B2,D3").ClearContents

or

Sub ClearCells()

Dim Rng1 As Range
Dim Rng2 As Range
Set Rng1 = Sheets("Sheet1").Range("A1,C1")
Set Rng2 = Sheets("Sheet2").Range("B2,D3")

Rng1.ClearContents
Rng2.ClearContents
End Sub

VBA Noob

Clear cell (VBA code)

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Clear cell (VBA code)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions