Link to home
Start Free TrialLog in
Avatar of dnldsn
dnldsn

asked on

Worksheet Calculation Event

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
Avatar of leptonka
leptonka
Flag of Hungary image

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
Avatar of redmondb
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.
Avatar of dnldsn
dnldsn

ASKER

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.
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
Avatar of dnldsn

ASKER

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
dnldsn,

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

Regards,
Brian.
Avatar of dnldsn

ASKER

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.
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.
Avatar of dnldsn

ASKER

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.
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.
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.
Avatar of dnldsn

ASKER

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
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
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
ASKER CERTIFIED SOLUTION
Avatar of redmondb
redmondb
Flag of Afghanistan image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dnldsn

ASKER

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
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.
Avatar of dnldsn

ASKER

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.
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.
Avatar of dnldsn

ASKER

Brian

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

Bob
Avatar of dnldsn

ASKER

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.
I appreciate the kind words, Bob. Many thanks.