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 B2:D100
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 Structu=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
----------------------------------------------------------------

--
Message posted from http://www.ExcelForum.com

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

--
Message posted from http://www.ExcelForum.com

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 Structu=True, Windows:=False
Application.ScreenUpdating = True

End Sub


Patrick Molloy
Microsoft Excel MVP

-----Original Message-----
Hi,
I need to clear all the data from specified cells on two
sheets. When I
step into the macro it works fine. When I run the macro
from a command
button the 'All_Barcodes' sheet is displayed with cells
B2:D1001
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 's
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 Structu=True, Windows:=False
Sheets("Barcodes").Select
Range("B2").Select
Application.ScreenUpdating = True
End Sub
---------------------------------------------------------
--------


---
Message posted from http://www.ExcelForum.com/

.

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

Cheers
Nigel


"jvine " wrote in message
...
Hi,
I need to clear all the data from specified cells on two sheets. When I
step into the macro it works fine. When I run the macro from a command
button the 'All_Barcodes' sheet is displayed with cells B2:D1001
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 's
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 Structu=True, Windows:=False
Sheets("Barcodes").Select
Range("B2").Select
Application.ScreenUpdating = True

Case vbNo

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

End Select

End Sub
-----------------------------------------------------------------


---
Message posted from http://www.ExcelForum.com/

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

--
Message posted from http://www.ExcelForum.com