Modify VBA Code for Specific Sheet

[Tennisguuy]

I have the following code in Thisworkbook in the VBA module. I would like to copy this code onto a specific sheet but not sure how to modify it. I think I would need to change the third line to the name of the sheet.

Private Sub Workbook_Open()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
' Add condition for specifying sheet names if needed
If Date > #1/1/2020# Then
sh.EnableCalculation = False
End If
Next
End Sub


I would like to change the code from Thisworkbook to Auto Pricing and put the code on this specific sheet.

 

[Tennisguuy]

Oh ok can the code not work upon opening the file if it is in the sheet module. If it can only run open opening in the thisworkbook the previous code will work because it will not allow any worksheet to work. The main reason I want to put it in the sheet module is because if they copy the sheet out of the workbook the code doesn't transfer and I was thinking if I put the code on the sheet and if the copied the sheet into a new workbook the code will go with it and still work.

 

[Joe4]

can the code not work upon opening the file if it is in the sheet module.

It is important to have a clear understanding of how Event Procedure code works. The different modules have different Event Procedures, and you cannot simply swap Workbook Event Procedures to Sheet Evern Procedures, and expect them to work, without making changes to them. For example, Workbook_Open event procedures will NEVER run in the any sheet module.

I highly recommend reading this article: http://www.cpearson.com/excel/Events.aspx

The main reason I want to put it in the sheet module is because if they copy the sheet out of the workbook the code doesn't transfer and I was thinking if I put the code on the sheet and if the copied the sheet into a new workbook the code will go with it and still work.

No, VBA code is not copied along to a new workbook with copying the sheet.

I would probably recommend that you approach your issue another way. If this issue is changing prices, I would recommend creating a lookup table with dates and prices, and have your formulas look up the correct price for that particular date using a LOOKUP formula.
See: https://www.extendoffice.com/documents/excel/2702-excel-vlookup-between-date-range.html

 

[Tennisguuy]

Ok thanks Joe. I appreciate you taking the time to help.

 

[Joe4]

You are welcome.

Though I have never done it, I have people write VBA to create VBA code in other workbooks, but I have never done that myself.
But I don't think that is necessary, if you change how you approach this.