Empty or Populate cells if conditions are met

R

Runner11

New Member
Joined
Jul 23, 2014
Messages
8
Hi to everyone,
I am new to the forum and to the vba code. I am creating an excel workbook which would automate certain type of routinary calculations.
So far all I wanna do is to add to my workbook a cell conditional value according to a number which I have in a cell (let's say C10).
I created a peice of script which has to delete or insert values and formulas according to the value in cell C12


Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    Application.ScreenUpdating = False
    NmrStd = Sheets("Step 1").Range("C10").Value
    Select Case NmrStd
    Case Is = 2
        Selection.Range("D17 : I18").ClearContents
        [COLOR=#000000][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Step 1"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Range[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"K20"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Formula[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"=AVERAGE(I15 : I16)"[/FONT][/COLOR]
[COLOR=#008800][FONT=inherit]        [/FONT][/COLOR]
    Case Is = 4
     Selection.Range("D17 : E18").ClearContents
     [COLOR=#000000][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Step 1"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Range[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"D17"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Value[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"STD1"
[/FONT][/COLOR]     [COLOR=#000000][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Step 1"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Range[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"D18"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][FONT=inherit][COLOR=#000000]VAlue [/COLOR][/FONT][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"STD2"
[COLOR=#000000][FONT=inherit]     Worksheets[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][FONT=inherit]"Step 1"[/FONT][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Range[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][FONT=inherit]"I17"[/FONT][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Formula[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][FONT=inherit]"=E17/(F17*G17*H17)"[/FONT]
[COLOR=#000000][FONT=inherit]     [FONT=inherit]Worksheets[/FONT][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Step 1"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][FONT=inherit]Range[/FONT][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"I18"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][FONT=inherit]Formula[/FONT][FONT=inherit] [/FONT][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][FONT=inherit] [/FONT][COLOR=#008800][FONT=inherit]"=E18/(F18*G18*H18)"[/FONT][/COLOR]
     Worksheets[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][FONT=inherit]"Step 1"[/FONT][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Range[/FONT][/COLOR][COLOR=#666600][FONT=inherit]("[/FONT][/COLOR][FONT=inherit]G17"[/FONT][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Formula[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]= [/FONT][/COLOR][COLOR=#000000][FONT=inherit]"=[/FONT][/COLOR]SE(E17>0;CERCA.VERT(F2;Prodotti!A3:S37;19;FALSO);"")"
[/FONT][/COLOR]     [COLOR=#000000][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Step 1"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Range[/FONT][/COLOR][COLOR=#666600][FONT=inherit]("[/FONT][/COLOR][COLOR=#008800][FONT=inherit]G18"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Formula[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] "=[/FONT][/COLOR][COLOR=#008800]SE(E17>0;CERCA.VERT(F2;Prodotti!A3:S37;19;FALSO);"")"[/COLOR]
     [COLOR=#000000][FONT=inherit]Worksheets[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"Step 1"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Range[/FONT][/COLOR][COLOR=#666600][FONT=inherit]([/FONT][/COLOR][COLOR=#008800][FONT=inherit]"K20"[/FONT][/COLOR][COLOR=#666600][FONT=inherit]).[/FONT][/COLOR][COLOR=#000000][FONT=inherit]Formula[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#666600][FONT=inherit]=[/FONT][/COLOR][COLOR=#000000][FONT=inherit] [/FONT][/COLOR][COLOR=#008800][FONT=inherit]"=AVERAGE(I15 : I18)"[/FONT][/COLOR]
End Select
       Application.ScreenUpdating = True
End Sub


Why does not work? Excel get blocked and crashes when I run it.
thanks a lot

Luca
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
Sort by date Sort by votes
It was probably recursive events causing the crash

This will stop it

Application.EnableEvents

This code will work

Is the worksheet "Step 1" if so they might be redundant references

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error Resume Next
    ' the range C10 on the current sheet has changed
    If Intersect(Target, Range("C10")) Is Nothing Then
      Exit Sub
    End If
    'stop the worksheet change event triggering because of changes caused by the code in the work sheet change event
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    NmrStd = Target.Value
    
    Select Case NmrStd
        Case Is = 2
            Selection.Range("D17 : I18").ClearContents
            Worksheets("Step 1").Range("K20").Formula = "=AVERAGE(I15 : I16)"
    
        Case Is = 4
         Selection.Range("D17 : E18").ClearContents
         Worksheets("Step 1").Range("D17").Value = "STD1"
         Worksheets("Step 1").Range("D18").Value = "STD2"
         Worksheets("Step 1").Range("I17").Formula = "=E17/(F17*G17*H17)"
         Worksheets("Step 1").Range("I18").Formula = "=E18/(F18*G18*H18)"
         Worksheets("Step 1").Range("G17").Formula = "=SE(E17>0;CERCA.VERT(F2;Prodotti!A3:S37;19;FALSO);"")"
         Worksheets("Step 1").Range("G18").Formula = "=SE(E17>0;CERCA.VERT(F2;Prodotti!A3:S37;19;FALSO);"")"
         Worksheets("Step 1").Range("K20").Formula = "=AVERAGE(I15 : I18)"
    
    End Select
Application.ScreenUpdating = True
're-enable events
Application.EnableEvents = True

End Sub
 
Upvote 0
CharlesChuckieCharles it works simply wonderfully. I have one more question.
The sheet must remain protect all time. how can I deproctect with vba code in order to make your code work.
Thanks a lot!

Luca
 
Upvote 0
I wanted to add another part to your code which allows to hide or unhide a worksheet called "Step 2" but it does not work. How can we sort it out this.

Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
    
    On Error Resume Next
    ' the range C10 on the current sheet has changed
    If Intersect(Target, Range("C9")) Is Nothing Then
      Exit Sub
    End If
    'stop the worksheet change event triggering because of changes caused by the code in the work sheet change event
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    
    NmrStd = Target.Value
    
    Select Case NmrStd
        Case Is = 2
            Selection.Range("D17 : I18").ClearContents
            Worksheets("Step 1").Range("K20").Formula = "=AVERAGE(I15 : I16)"
    
        Case Is = 4
         Selection.Range("D17 : E18").ClearContents
         Worksheets("Step 1").Range("D17").Value = "STD1"
         Worksheets("Step 1").Range("D18").Value = "STD2"
         Worksheets("Step 1").Range("I17").Formula = "=E17/(F17*G17*H17)"
         Worksheets("Step 1").Range("I18").Formula = "=E18/(F18*G18*H18)"
         Worksheets("Step 1").Range("G17").Formula = "=SE(E17>0;CERCA.VERT(F2;Prodotti!A3:S37;19;FALSO);"")"
         Worksheets("Step 1").Range("G18").Formula = "=SE(E17>0;CERCA.VERT(F2;Prodotti!A3:S37;19;FALSO);"")"
         Worksheets("Step 1").Range("K20").Formula = "=AVERAGE(I15 : I18)"
    
    End Select


[COLOR=#0000ff]cell = Sheets("Step 1").Range("C11").Value[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]If cell = 2 Then[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]Worksheets("Step 2").Visible = xlVisible[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]Else[/COLOR]
[COLOR=#0000ff]
[/COLOR]
[COLOR=#0000ff]Worksheets("Step 2").Visible = xlVeryHidden[/COLOR]
[COLOR=#0000ff]End If[/COLOR]


Application.ScreenUpdating = True
're-enable events
Application.EnableEvents = True

Keep in mind that must remain protect by password (of course excluding cells needed to insert numbers)
Thanks a lot

Luca
 
Upvote 0
If you're using theVBA to protect the sheets then use

ActiveWorkbook.Sheets("Sheet1").Protect "google", True, True, True, True

where the forth TRUE is :

UserInterfaceOnly : Optional Variant True to protect the user interface, but not macros. If this argument is omitted, protection applies both to macros and to the user interface.

(Worksheets is'nt a thing in Excel it's Sheets or WorkSheet)

If Sheets("Step 1").Range("C11").Value= 2 Then
sheets("Step 2").Visible = xlVisible
Else
sheets("Step 2").Visible = xlVeryHidden
End If
 
Last edited:
Upvote 0
Thanks a lot for your reply. As a new user, were I put this piece of code? Workbook opening section?

Code: 
[COLOR=#333333]ActiveWorkbook.Sheets("Sheet1").Protect "google", True, True, True, True [/COLOR]

Luca
 
Upvote 0
THis normally an action you'd carry out when saving, and it would normally complement an unprotect action when opening or doing a specific action, so where you place this code is down to how you see your solution working from a users point of view
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Question Question
Seeking Help with CELLS Property in VBA
2
Replies
13
Views
624
Alex Blakenburg
A
N
  • Question Question
Count - specific consecutive order
Replies
6
Views
985
Peter_SSs
Peter_SSs
P
  • Question Question
Populating, aggregating and sorting multiple matching values in Excel
Replies
0
Views
183
Pbobas
P
W
  • Question Question
Trying to learn how to loop code
Replies
1
Views
211
mumps
mumps
J
  • Solved
conditional formatting based on duplicate cells
Replies
4
Views
237
Joe4
Joe4

Forum statistics

Threads
1,223,246
Messages
6,170,987
Members
452,373
Latest member
TimReeks

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top