macro to run IF condition met

S

Savuti

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
 
J

Jim Thomlinson

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

Savuti

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
 
J

Jim Thomlinson

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
 
A

Alan

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
 
R

Rick Rothstein \(MVP - VB\)

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
 
S

Savuti

Rick thank you. I learned something new - much appreciated -Lord I have a lot
to learn
 
A

Alan

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

Rick Rothstein (MVP - VB) said:
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
Click to expand...
 
S

Savuti

Alan, thank you as well for taking the time to respond. The more ways of
learning to do something the better.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Coding Help for Beginner 0
Run macro if conditions meet 1
condition for run macro 2
Deactivate button macro if condition not met 3
Remove Identical words 0
Drop down list control value in vba 2
active range to run macro 4
Run Macro when a condition is met 9

Top