Solved

Worksheet Calculation Event

Posted on 2013-02-05
22
253 Views
Last Modified: 2013-02-06
I have the following code in a worksheet module. It works well when the value in cell BT2 is changed manually or using a data validation list. I need to change this to a calculation event (I think). The value in cell BT2 will be a result of the formula "=Sheet2!F3"


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("BT2")) Is Nothing Then Exit Sub 'Case Sensitive
Select Case Target.Value
Case "YES"
Replace_Owner_to_Lessor
Case "NO"
Replace_Lessor_to_Owner
End Select
End Sub
0
Comment
Question by:dnldsn
22 Comments
 
LVL 7

Expert Comment

by:leptonka
Comment Utility
Hi,

What is in Sheet2!F3 ? Is it an input cell?
Without knowing the project... maybe you only need to move this code to Sheet2 and use the Worksheet_Change event there,

Cheers,
Kris
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, dnldsn.

A few points..
(1) I assumed that you didn't want the "Replace" macros to run unless BT2's value has changed.
(2) Point (1) requires a cell to hold the previous value. I've used BU2, but of course it could be anywhere.
(3) I've disabled Events while it's processing a YES/NO.

The code is...
Private Sub Worksheet_Calculate()

If Me.Range("BT2") = Me.Range("BU2") Then Exit Sub

Application.EnableEvents = False

    Select Case Me.Range("BT2")
        Case "YES"
            Replace_Owner_to_Lessor
        Case "NO"
            Replace_Lessor_to_Owner
    End Select
    
    Me.Range("BU2") = Me.Range("BT2")

Application.EnableEvents = True

End Sub

Open in new window

Regards,
Brian.
0
 

Author Comment

by:dnldsn
Comment Utility
Sheet 2 is a data input worksheet with a large number of cells that populate cells in multiple worksheets in the workbook. I currently use the change event to change text and/or pictures or to hide/unhide rows on other worksheets based on the value in cell F3. The value in F3 is YES or NO from a data validation list.

Brian - I tried your code but it did not work.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
dnldsn,

Please see attached. BT2 is linked to BS2, so putting "YES" or "NO" in BS2 should generate a message.

(Edit: Oops, I corrected an address.)

Regards,
Brian.
Calculate-Event-Test.xlsm
0
 

Author Comment

by:dnldsn
Comment Utility
Ok, I am new at this.

Case "YES"
            Replace_Owner_to_Lessor
Case "NO"
            Replace_Lessor_to_Owner

The "Replace_Owner_to_Lessor" and "Replace_Lessor_to_Owner" are modules that find and replace text in a Text Box.

Sorry I did not provide complete information
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
dnldsn,

My fault - I should have mentioned that I was burying them in a MsgBox. Is it OK now?

Regards,
Brian.
0
 

Author Comment

by:dnldsn
Comment Utility
Brian - I tried the code in the worksheet provided in my workbook. While the message boxes displayed the text in the text box did not change.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
dnldsn.

I don't have your two macros, but I had to demonstrate that my code word, so I created my versions of the macros to display the macros' names. So your problems appears to be that you've included my versions of the macros in your file. Assuming that your macros are still there, simply delete my Replace_Owner_to_Lessor and Replace_Lessor_to_Owner macros (They're underneath my Worksheet_Calculate macro.)

Regards,
Brian.
0
 

Author Comment

by:dnldsn
Comment Utility
Brian

I have not been able to get this to work. I do get the message box but the text does not change. By the way I would not want a message box for this event.
0
 
LVL 85

Expert Comment

by:Rory Archibald
Comment Utility
If you only want to react to BT2 changing, I would agree with Kris that it's easier to simply monitor the input cell on sheet2 instead.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
dnldsn,

Please post your file here. If the data is sensitive then add a new empty sheet and delete all the others - it's just the code I need to see.

Thanks,
Brian.
0
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

Author Comment

by:dnldsn
Comment Utility
This seems to be more complicated than I thought it would be.

So, I have uploaded a section of the overall workbook for your review.

The cell to be monitored "P25" is on the "Check List" worksheet (Sheet 2)

The text boxes are on the "S-Application" worksheet and the "MT Amendment to TPM Agreement" The target text boxes are samples for testing.

The goal is to change the text in the text boxes to "Owner" or "Lessor" based on the value in cell "P25" on the "Check List" worksheet.

I am OK with monitoring the cell directly or using the Calculate event. Actually I think I would rather monitor the cell directly.

The new code would need to share the worksheet module with the existing code.

Thanks for your interest and support.
Sales-Order-Test-Workbook.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Hi, dnldsn.

Edit: Apologies, I thought that your two replace macros were already working. I am changing them now, back in a minute.

Yes, you (and the other experts) are correct that a "Change" is better here. Please see attached. The code is...
Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Me.Range("P25"), Target) Is Nothing Then Exit Sub

Application.EnableEvents = False
    Select Case Target
        Case "YES"
            Replace_Owner_to_Lessor
        Case "NO"
            Replace_Lessor_to_Owner
    End Select
Application.EnableEvents = True

End Sub

Open in new window

Regards,
Brian.Sales-Order-Test-Workbook-V2.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
dnldsn,

Please see attached which has the two Replace macros updated. (I notice that you have "On Error Resume Next" in those macros - unless there's a specific error you're trying to hide, that's a dangerous thing to do, so you might consider dropping them. At the very least, comment them out while your code is under development.)

The code is...
Sub Replace_Owner_to_Lessor()
    Dim shp As Shape
    Dim sOld As String
    Dim sNew As String
    Dim xSheet As Variant
    
    'Change as desired
    sOld = "Owner"
    sNew = "Lessor"
    On Error Resume Next
    
    For Each xSheet In Array("S-Application", "MT Amendment to TPM Agreement")
    
        For Each shp In Sheets(xSheet).Shapes
            With shp.TextFrame.Characters
                .Text = Application.WorksheetFunction.Substitute( _
                  .Text, sOld, sNew)
            End With
        Next
        
    Next
    
End Sub
Sub Replace_Lessor_to_Owner()
    Dim shp As Shape
    Dim sOld As String
    Dim sNew As String
    Dim xSheet As Variant

    'Change as desired
    sOld = "Lessor"
    sNew = "Owner"
    On Error Resume Next
    
    For Each xSheet In Array("S-Application", "MT Amendment to TPM Agreement")
    
        For Each shp In Sheets(xSheet).Shapes
            With shp.TextFrame.Characters
                .Text = Application.WorksheetFunction.Substitute( _
                  .Text, sOld, sNew)
            End With
        Next
    
    Next
End Sub

Open in new window

Regards,
Brian.Sales-Order-Test-Workbook-V2.xlsm
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
Comment Utility
dnldsn,

Please see my previous two posts.

I just noticed that most of the modules do not start with "Option Explicit". "Option Explicit" makes code development much, much easier, so I have added it to each of the modules in the attached. (The exception is the "Go_to_Form" module which has lots of references to non-existent sheets - ones that were redacted?)

To have "Option Explicit" automatically added to any new modules, go in to VBE's Options and make sure that "Require Variable Declaration" is selected. If you'd like to know more, please let me know.

Regards,
Brian.Sales-Order-Test-Workbook-V3.xlsm
0
 

Author Comment

by:dnldsn
Comment Utility
Brian - Your solutions does work!
I will definitely take your recommendation on the "Option Explicit" and the error code. Thank you for the effort you have put into this.

My concern at this point is maintaining this beast. (By the way I did remove all of the worksheets except for the one's I wanted to use for testing.) The final work book will have 10 to 12 “shared” worksheets with multiple text boxes that will need to be managed.
I have uploaded a revised version of your last workbook. Since your code works I want to focus on seeing if there is a simpler solution.

I changed the worksheet "MT Amendment to TPM Agreement" to work based on a change in cell BT2 that is on the worksheet. The value in the cell is changed using a data validation list on the same worksheet. As you can see the code does a find and replace by cycling through the text boxes. I also added sample text boxes from other worksheets that will need to be managed so that you can see the issue I have about maintenance and replacing all of the text in a text box.

Something like that would be the preferred approach.

Would it be possible to use an Excel formula such as an “IF” statement that would “call” a module based on the value in the cell BT2?

Since I was focused on finding a solution based on “Find and Replace” I not include the more complex text boxes. Hope this has not made it frustrating for you.

I would also prefer to refer to the worksheets by "worksheet number" rather than tab lable.
Sales-Order-Test-Workbook-V4.xlsm
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
I changed the worksheet "MT Amendment to TPM Agreement" to work based on a change in cell BT2 that is on the worksheet. The value in the cell is changed using a data validation list on the same worksheet. As you can see the code does a find and replace by cycling through the text boxes. I also added sample text boxes from other worksheets that will need to be managed so that you can see the issue I have about maintenance and replacing all of the text in a text box.
As I understand it, your changes were ...
 - BT2 has a YES/NO dropdown.
 - The sheet now has a Change Event macro. This macro uses the "OLDReplace" macros. It also doesn't disable Events while running - probably OK now, but a future change to any of the three macros may leave you looking at an Excel crash.
As you want changes made to multiple sheets, why are you using "OLDReplace" macros rather than my modified "Replace" macros?

Would it be possible to use an Excel formula such as an “IF” statement that would “call” a module based on the value in the cell BT2?
Effectively no - while there's no problem calling a module, it's severely limited as to what it can change.

I would also prefer to refer to the worksheets by "worksheet number" rather than tab lable.
That would be lethally dangerous. A couple of alternatives...
(1) USe Codename, e.g. "Sheet2" for "Check List".
(2) Are there any sheets that you don't want to change? If not, then simply scan all sheets.

Since I was focused on finding a solution based on “Find and Replace” I not include the more complex text boxes.
Sorry, I don't understand this.

A couple of other items...

Am I correct that you'll have a number of places where the user can trigger the change for multiple sheets? If so could I suggest that when the value is changed on one sheet then the macro, beside updating the Textboxes, should also update all the places whee the "YES/NO" is stored. (This definitely would require events to be changed!)

Also, if there are multiple places then the Event macro should be moved to ThisWorkBook and made generic.

Edit:
The final work book will have 10 to 12 “shared” worksheets with multiple text boxes that will need to be managed.
What do you mean by "shared"?

Thanks,
Brian.
0
 

Author Comment

by:dnldsn
Comment Utility
Brian

First of all your code works!!

I have spent some more time looking over your workbook and have a better understanding of how it works.

I will change the "Event"

When I looked at your sheet 1 I thought you were replacing all of the text in the text box. My error

 Excel formula - OK, just a thought.

That would be lethally dangerous. A couple of alternatives...
(1) USe Codename, e.g. "Sheet2" for "Check List".
(2) Are there any sheets that you don't want to change? If not, then simply scan all sheets.

I did a poor job of stating my question. You are right.

“Find and Replace” My error I did not understand how your code worked and was missled by my interpertation of worksheet 1. (Should have know better)

A couple of other items...

There is only one place that a user can change the value and that is on the "Check List" worksheeet.

What do you mean by "shared"?

There are a number of forms that capture the same information. The difference in the forms are: Logo, Company name and Lessor or Owner. I can reduce the number of forms in the workbook by managing the above items.

You use an Array in the formula so I will need to add each worksheet I want to manage to the array? Can I use Sheet Codename for this?

What is the role of sheet1?

I have added your code to the my full workbook and it seems to be working. Will need to do a few more checks.

Thanks again for your very follow up.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
Thanks, dnldsn.

I think that most of your post was us agreeing with each other. If I've left anything out below, please let me know.

You use an Array in the formula so I will need to add each worksheet I want to manage to the array? Can I use Sheet Codename for this?
Yes, you'll need to add to the array - although it might be better to automatically include all sheets (if necessary, excluding the one or two that you specifically want to exclude, realising that you don't need to exclude sheets which don't have listboxes, or at at least ones with the values ).
If you don't want to go with my my previous paragraph - (1) Why?! (2) Codename is an easy change.

What is the role of sheet1?
My workings - I wanted to see which sheets had listboxes containing either of the two values. It should have been dropped.

Regards,
Brian.
0
 

Author Comment

by:dnldsn
Comment Utility
Brian

Thank you very much. Enjoyed the exchange and learned a lot.

Bob
0
 

Author Closing Comment

by:dnldsn
Comment Utility
Created simple clean code to solve the problem. Not only provided solution but reviewed all modules in the workbook and offered suggestions and the reason for them. Responses during the exchange were timely and well thought out.  Could not be more pleased.
0
 
LVL 26

Expert Comment

by:redmondb
Comment Utility
I appreciate the kind words, Bob. Many thanks.
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now