Problem with clearing cells
 

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

Problem with clearing cells
 

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

Problem with clearing cells
 

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/

.

Problem with clearing cells
 

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/

Problem with clearing cells
 

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

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