Problem with clearing cells

[jvine]

Hi,
I need to clear all the data from specified cells on two sheets. When
step into the macro it works fine. When I run the macro from a comman
button the 'All_Barcodes' sheet is displayed with cells B2100
highlighted with all of the data still present.
Please help.
jvine

-------------------------------------------------------------

Sub Clear()
'
' Clear Macro
' Macro recorded 29/03/2004 by Janene Vine
'

YesNo = MsgBox("This will DELETE all barcodes, ready to accept today '
barcode entries." & Chr(13) & "Do you want to continue?", vbYesNo
vbCritical, "Caution")
Select Case YesNo
Case vbYes

Application.ScreenUpdating = False
Sheets("Barcodes").Select
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect
Range("B2:B1001").Select
Range("B1001").Activate
Selection.ClearContents
Range("B2").Select
ActiveSheet.Unprotect
ActiveWorkbook.Unprotect
Sheets("All_Barcodes").Visible = True
Sheets("All_Barcodes").Select
Range("B2:B1001").Select
Range("B1001").Activate
Selection.ClearContents
Range("B2").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Barcodes").Select
Range("B2").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True
Scenarios:=True
ActiveWorkbook.Protect Structure:=True, Windows:=False
Sheets("Barcodes").Select
Range("B2").Select
Application.ScreenUpdating = True

Case vbNo

MsgBox "Action cancelled", vbInformation, "Human Immunology Barcod
Form Checks"

End Select

End Sub

 

[mudraker]

The command button probally has focus which will stop your macro fro
completing the actions

Right click on command button > dselect properties

Change TakeFocusOnClick setting to fals

 

[Guest]

Your code was a bit messed up - take a look & you'll see
that it should be clearer to debug...

Sub Clear()

IF MsgBox("This will DELETE all barcodes, ready to
accept today 's
barcode entries." & Chr(13) & "Do you want to continue?",
vbYesNo +
vbCritical, "Caution") = vbYes Then

Application.ScreenUpdating = False
ActiveWorkbook.Unprotect
With Sheets("Barcodes")
.Unprotect
.Range("B2:B1001").ClearContents
.Protect DrawingObjects:=True, _
Contents:=True, _
Scenarios:=True

End With
with Sheets("All_Barcodes")
.Range("B2:B1001").ClearContents
End With
ActiveWorkbook.Protect Structure:=True, Windows:=False
Application.ScreenUpdating = True

End Sub


Patrick Molloy
Microsoft Excel MVP

 

[Nigel]

Set the TakeFocusOnClick property of the button to FALSE if using XL97.

Cheers
Nigel

 

[jvine]

Thankyou for your suggestions but neither worked. I am using Excel 200
it that helps..