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.

 

[Joe4]

It is important to understand how event procedures like "Workbook_Open" behave. When used and placed in the "ThisWorkbook" module, the VBA code fires automatically, upon opening the workbook.
You can also have event procedure code place in sheet modules, like "Worksheet_Change", which run automatically upon the manual change of a value on the sheet (there are numerous other ones that run off of other events).

If you want VBA code that you call/run manually (or attach to a button), you typically put those in a standard Module.

So, the best place to start is from the following:
- What is it exactly that you want the VBA code to do?
- How do you want the VBA code to be called (autoamtically, manually, etc)?
If automatically, what should trigger it to run?

 

[Tennisguuy]

The code will prevent all of the formulas in the workbook from working. The workbook formula has rates that are only effective for a certain time period but some would still use it so I wanted a way to not have the formulas working if someone entered new data after a certain date. I recently discovered that when I used this same method in a workbook that only had one sheet and if someone copied that sheet into a new workbook the code would not transfer so I wanted to put the code on the specific sheet so if they copied it into a new workbook the code went with it.

I wanted the code to run automatically when the workbook is opened.

 

[Joe4]

I wanted the code to run automatically when the workbook is opened.

Then you want to keep it right where it is is. But instead of looping through all the sheets, if you just want to apply it to the one sheet, then remove the "For each..." loop and instead just activate the sheet you want to apply it to.

Note that this just disables the automatic calculation of the formulas. However, if they press F9 to do a manual calculation, it will update the values.
If you really do not want the values to change, you will want to change all the formulas to hard-code values (Copy --> Paste --> Special Values over top of the formulas).

 

[Tennisguuy]

There are a lot of formula so the hard code method would be too time consuming. The VBA code works perfectly is just that when it is copied into a new workbook it doesn't transfer when applied to thisworkbook. sheet.

I'm am not very good with VBA code is this what you meant. I tried this and took the code out of this workbook

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

but the formula still are working.

 

[Joe4]

There are a lot of formula so the hard code method would be too time consuming.

You could do it all at once, no need to go cell-by-cell.

The VBA code works perfectly is just that when it is copied into a new workbook it doesn't transfer when applied to thisworkbook. sheet.

What is the name of the sheet that you want this applied to?

 

[Tennisguuy]

The name of the sheet is Auto Pricing. I modified the third line

 

[Joe4]

Try this:

[COLOR=#333333]Private Sub Workbook_Open()[/COLOR]

    Sheets("Auto_Pricing").Activate
    If Date > #1/1/2019# Then
        ActiveSheet.EnableCalculation = False
    End If

[COLOR=#333333]End Sub[/COLOR]

 

[Tennisguuy]

I copied the code onto the worksheet by right clicking on the sheet tab>selecting view code> then pasting the above code. I exited the worksheet and save then open back up and entered new data and the formula still worked and change based on on the new data.

 

[Joe4]

You need to put this code in the "ThisWorkbook" module if you want it to run when opening the file, not in the Sheet module.