Worksheet selection change formula entry

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
2,004
Office Version
  1. 365
Platform
  1. Windows
I am trying to enter the following formula into cells on a worksheet change event but it errors out.

formula to input is: "=IF(H$10="",($H$8/12*K$8),($H$8/12*K$8)+H$10)"

I have used the following format to do this:

Code:
   Case "Post Tender Activity"
            .Range("J4:M4,o4:r4").Formula = "=0"
            Target.Offset(0, 12).Formula = "=IF(H$10="",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
            Target.Offset(0, 13).Formula = "=IF(I$10="",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

when selecting "Post Tender Activity", the first line clears the range preceding the cells where the formula is to reside, and then it errors out on the line beginning Target.Offset(0,12) with the message "Application defined or Object defined error".

If i change out the formula bit to simply activate the cell, it works a treat.
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
aaahh, Smitty. what would I get paid without you??!! ;o)

further question though. I posted yesterday about having a workbook level beforedoubleclick event and want this one to operate in a similar way. That is, rather than copying and pasting the macro into every sheet (around 135 so far), i would like it to go into This Workbook. I tried adapting it as I was shown yesterday for the other macro, but have a problem with the For and Next (error message is that I have the Compile error: Next without For).

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'///shifts savings depending on Status - Est>Target>Banked and back again.

    Dim Sht As Worksheet

    startt = Sheets("First").Index + 1
    endd = Sheets("Last").Index - 1
    
    For I = startt To endd
        If Target.Cells.Count > 1 Then Exit Sub
        If Intersect(Target, Range("I4")) Is Nothing Then Exit Sub
        
        Application.ScreenUpdating = False
        With Sht
            ' .Unprotect

            Select Case Target.Value

            Case "Negotiate"
                .Range("J4:M4,T4:W4").Formula = "=0"
                .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
                .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

            Case "Preparation"
                .Range("J4:M4,T4:W4").Formula = "=0"
                .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
                .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

            Case "RFx"
                .Range("J4:M4,T4:W4").Formula = "=0"
                .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
                .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

            Case "Negotiate"
                .Range("J4:M4,T4:W4").Formula = "=0"
                .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
                .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

            Case "Approval"
                .Range("J4:M4,T4:W4").Formula = "=0"
                .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
                .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

            Case "Completed"
                .Range("J4:M4,o4:r4").Formula = "=0"
                .Range("U4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
                .Range("V4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

            Case "Post Tender Activity"
                .Range("J4:M4,o4:r4").Formula = "=0"
                Target.Offset(0, 12).Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
                Target.Offset(0, 13).Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

            Case "Closed"
                .Range("J4:M4,O4:R4,T4:W4").Formula = "=0"

            Case "On Hold"
                .Range("O4:R4,T4:W4").Formula = "=0"
                .Range("K4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
                .Range("L4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"

            Case "Not Started"
                .Range("O4:R4,T4:W4").Formula = "=0"
                .Range("K4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"
                .Range("L4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"


            End Select
            .Calculate
            '.Protect
        Next I
    End With
    Application.ScreenUpdating = True
End Sub

any suggestions?
 
Upvote 0
Give this a shot Andrew:

<font face=Tahoma><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetChange(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>, <SPAN style="color:#00007F">ByVal</SPAN> Target <SPAN style="color:#00007F">As</SPAN> Range)<br><br><SPAN style="color:#007F00">'///shifts savings depending on Status - Est>Target>Banked and back again.</SPAN><br><br>        <SPAN style="color:#00007F">If</SPAN> Target.Cells.Count > 1 <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> Intersect(Target, Range("I4")) <SPAN style="color:#00007F">Is</SPAN> <SPAN style="color:#00007F">Nothing</SPAN> <SPAN style="color:#00007F">Then</SPAN> <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <br>        <SPAN style="color:#00007F">With</SPAN> Application<br>            .ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>                <SPAN style="color:#007F00">'.Unprotect</SPAN><br>                <SPAN style="color:#00007F">Select</SPAN> <SPAN style="color:#00007F">Case</SPAN> Target.Value<br>                    <SPAN style="color:#00007F">Case</SPAN> "Negotiate"<br>                        <SPAN style="color:#00007F">With</SPAN> Sh<br>                            .Range("J4:M4,T4:W4").Value = 0<br>                            .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"<br>                            .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">Case</SPAN> "Preparation"<br>                        <SPAN style="color:#00007F">With</SPAN> Sh<br>                            .Range("J4:M4,T4:W4").Value = 0<br>                            .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"<br>                            .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">Case</SPAN> "RFx"<br>                        <SPAN style="color:#00007F">With</SPAN> Sh<br>                            .Range("J4:M4,T4:W4").Value = 0<br>                            .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"<br>                            .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">Case</SPAN> "Negotiate"<br>                        <SPAN style="color:#00007F">With</SPAN> Sh<br>                            .Range("J4:M4,T4:W4").Value = 0<br>                            .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"<br>                            .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">Case</SPAN> "Approval"<br>                        <SPAN style="color:#00007F">With</SPAN> Sh<br>                            .Range("J4:M4,T4:W4").Value = 0<br>                            .Range("P4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"<br>                            .Range("Q4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">Case</SPAN> "Completed"<br>                        <SPAN style="color:#00007F">With</SPAN> Sh<br>                            .Range("J4:M4,o4:r4").Value = 0<br>                            .Range("U4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"<br>                            .Range("V4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">Case</SPAN> "Post Tender Activity"<br>                        Sh.Range("J4:M4,o4:r4").Value = 0<br>                            Target.Offset(0, 12).Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"<br>                            Target.Offset(0, 13).Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"<br>                    <SPAN style="color:#00007F">Case</SPAN> "Closed"<br>                        Sh.Range("J4:M4,O4:R4,T4:W4").Value = 0<br>                <SPAN style="color:#00007F">Case</SPAN> "On Hold"<br>                        <SPAN style="color:#00007F">With</SPAN> Sh<br>                            .Range("O4:R4,T4:W4").Value = 0<br>                            .Range("K4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"<br>                            .Range("L4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">Case</SPAN> "Not Started"<br>                        <SPAN style="color:#00007F">With</SPAN> Sh<br>                            .Range("O4:R4,T4:W4").Value = 0<br>                            .Range("K4").Formula = "=IF(H$10="""",($H$8/12*K$8),($H$8/12*K$8)+H$10)"<br>                            .Range("L4").Formula = "=IF(i$10="""",($H$8/12*L$8),($H$8/12*L$8)+I$10)"<br>                        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>                    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Select</SPAN><br>            .Calculate<br>            <SPAN style="color:#007F00">'.Protect</SPAN><br>            .ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>

Note that you don't need to set the reference to the sheet(s) because it's already there:(ByVal Sh As Object, ByVal Target As Range)
 
Upvote 0
thanks Smitty. I was aiming to allow the event macro only on sheets between my bookend sheets, "First" and "Last". So, slight adaptation (thanks to Tom Urtis on another post) gives me the following which seems to work perfectly.

Code:
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

'///shifts savings depending on Status - Est>Target>Banked and back again.

    Dim shtFirst%, shtLast%
    shtFirst = Worksheets("First").Index
    shtLast = Worksheets("Last").Index
    If ActiveSheet.Index <= shtFirst Then Exit Sub
    If ActiveSheet.Index >= shtLast Then Exit Sub
    If Target.Cells.Count > 1 Then Exit Sub
    If Intersect(Target, Range("$I$4")) Is Nothing Then Exit Sub

    With Application
        .ScreenUpdating = False
        '.Unprotect
        Select Case Target.Value

        Case "Preparation"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
                .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "RFx"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
                .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Negotiate"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
                .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Approval"
            With Sh
               .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
                .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Completed"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$O$4:$R$4").Value = 0
                .Range("$U$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$V$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Savings Validation"
            With Sh
                .Range("$D$4").Value = 1
                .Range("$J$4:$M$4,$O$4:$R4$").Value = 0
                .Range("$U$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$V$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        Case "Closed"
            With Sh
                .Range("$D$4").Value = 0
                .Range("$J$4:$M$4,$O$4:$R$4,$T$4:$W$4").Value = 0
            End With

        Case "On Hold"
            With Sh
                .Range("$J$4:$M$4,$T$4:$W$4").Value = 0
             .Range("$P$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$Q$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With
        
        Case "Not Started"
            With Sh
                .Range("$D$4").Value = 3
                .Range("$O$4:$R4$,$T$4:$W$4").Value = 0
                .Range("$K$4").Formula = "=IF($G$8=0,0,IF($H$10="""",($H$8/12*$K$8),($H$8/12*$K$8)+$H$10))"
                .Range("$L$4").Formula = "=IF($G$8=0,0,IF($I$10="""",($H$8/12*$L$8),($H$8/12*$L$8)+$I$10))"
            End With

        End Select
        .Calculate
        '.Protect
        .ScreenUpdating = True
    End With
End Sub

many thanks mate. hope all is well.
 
Upvote 0
hey smitty. that code below bugs out on the line:
"If Intersect(Target, Range("$I$4")) Is Nothing" when other cells are selected on the page. I am copying descriptions from N8 in the user's copy of the workbook to the same cell in a master copy of the workbook. the error comes up when the focus is shifted back to the master copy after selecting the copy cell. where it says if the target cell is "nothing", does "nothing" mean if no change has been made since last save? how can i handle this error to ignore any change to the sheet other than a change to I4?
 
Upvote 0
just realised, the error is occurring in the user's copy of the workbook and not the Master copy.
 
Upvote 0
If Intersect(Target, Range("$I$4")) Is Nothing

Means that if I4 is not affected by any action you're taking the code won't run. So if you're copying into A1 for instance, the code won't fire. I.E. when the change event fires to evaluate a change on a sheet, if I4 is empty, then the code won't fire.

does "nothing" mean if no change has been made since last save?

It means since the sheet was last changed, which could be the last time it was saved, but more likely the last entry made to the sheet.

And this line:

If Target.Cells.Count > 1 Then Exit Sub


Also tells the code to exit if you're pasting into multiple cells, so I'm not sure where the problem is.
 
Upvote 0
it seems to be fine in the master copy which is the most important thing so i will leave it as is for the moment. cheers.

am about to post a new query on comparisons. care to take a peek if you have time?
 
Upvote 0

Forum statistics

Threads
1,213,581
Messages
6,114,451
Members
448,573
Latest member
BEDE

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