ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   macro to run IF condition met (https://www.excelbanter.com/excel-programming/410327-macro-run-if-condition-met.html)

Savuti
macro to run IF condition met
 
please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect

Jim Thomlinson
macro to run IF condition met
 
if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect

Savuti
macro to run IF condition met
 
Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect

Jim Thomlinson
macro to run IF condition met
 
Sub TEST()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If

End Sub

--
HTH...

Jim Thomlinson


"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect

Alan
macro to run IF condition met
 
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect

Savuti
macro to run IF condition met
 
Jim, you are a genius, works perfect - thank you sir

"Jim Thomlinson" wrote:

Sub TEST()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If

End Sub

--
HTH...

Jim Thomlinson


"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say "delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect

Rick Rothstein \(MVP - VB\)[_1862_]
macro to run IF condition met
 
You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick


"Alan" wrote in message
...
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following
criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say
"delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect


Savuti
macro to run IF condition met
 
Rick thank you. I learned something new - much appreciated -Lord I have a lot
to learn

"Rick Rothstein (MVP - VB)" wrote:

You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick


"Alan" wrote in message
...
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following
criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say
"delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect



Alan
macro to run IF condition met
 
That's why you're the MVP! :) Thanks!

"Rick Rothstein (MVP - VB)" wrote:

You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick


"Alan" wrote in message
...
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following
criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say
"delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect



Savuti
macro to run IF condition met
 
Alan, thank you as well for taking the time to respond. The more ways of
learning to do something the better.

"Alan" wrote:

That's why you're the MVP! :) Thanks!

"Rick Rothstein (MVP - VB)" wrote:

You can further reduce the code like this...

Sub Test()
If Range("A1").Value = 0 Then
ActiveSheet.Unprotect
Range("B9:I15").Copy Destination:=Range("B10")
Range("B7:I7").Copy Destination:=Range("B9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Rick


"Alan" wrote in message
...
Another method which will limit the number of selections:

Sub Test()
If Range("A1").Value = 0 Then
Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Copy Destination:=Range("B16:I16")
Range("B14:I14").Copy Destination:=Range("B15:I15")
Range("B13:I13").Copy Destination:=Range("B14:I14")
Range("B12:I12").Copy Destination:=Range("B13:I13")
Range("B11:I11").Copy Destination:=Range("B12:I12")
Range("B10:I10").Copy Destination:=Range("B11:I11")
Range("B9:I9").Copy Destination:=Range("B10:I10")
Range("B7:I7").Copy Destination:=Range("B9:I9")
Range("B7:I7").ClearContents
With Range("B9:I9")
.Locked = True
.FormulaHidden = False
End With
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
Else
MsgBox "delete list"
End If
End Sub

Alan



"Savuti" wrote:

Jim thanks for the reply. I did copy and paste your code, I got a Compile
error stating - Else without if.

I have included my complete existing code. Appreciate the help.

Range("B9:I16").Select
ActiveSheet.Unprotect
Range("B15:I15").Select
Selection.Copy
Range("B16:I16").Select
ActiveSheet.Paste
Range("B14:I14").Select
Application.CutCopyMode = False
Selection.Copy
Range("B15:I15").Select
ActiveSheet.Paste
Range("B13:I13").Select
Application.CutCopyMode = False
Selection.Copy
Range("B14:I14").Select
ActiveSheet.Paste
Range("B12:I12").Select
Application.CutCopyMode = False
Selection.Copy
Range("B13:I13").Select
ActiveSheet.Paste
Range("B11:I11").Select
Application.CutCopyMode = False
Selection.Copy
Range("B12:I12").Select
ActiveSheet.Paste
Range("B10:I10").Select
Application.CutCopyMode = False
Selection.Copy
Range("B11:I11").Select
ActiveSheet.Paste
Range("B9:I9").Select
Application.CutCopyMode = False
Selection.Copy
Range("B10:I10").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.Copy
Range("B9:I9").Select
ActiveSheet.Paste
Range("B7:I7").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("B9:I9").Select
Selection.Locked = True
Selection.FormulaHidden = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True,
Scenarios:=True
Range("I7").Select
End Sub

"Jim Thomlinson" wrote:

if range("A1").value = 0 then
Range("B9:I16").Select
ActiveSheet.Unprotect
else
msgbox "delete list"
end if
--
HTH...

Jim Thomlinson


"Savuti" wrote:

please help.... I need to have a macro run under the following
criteria:

if cell A1 = 0 then run macro
if cell A1 0 then don't run macro and show message box to say
"delete list"

below is the begining of the existing code ...... thanks

Range("B9:I16").Select
ActiveSheet.Unprotect




All times are GMT +1. The time now is 05:58 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com