PDA

View Full Version : Edit objects



wilg
01-30-2011, 06:23 PM
Hi, I have a spreadsheet that has approx 230 worksheets. I need to edit a worksheets_change event code by adding in a new command. Each worksheet has same codes.
How can I easily change this code in each object (Worksheet in vba) other than copy and past into each object individually?
I cannot hold down shift to select all the objects and past into at once. Any suggestions?

p45cal
01-30-2011, 06:48 PM
I wonder whether this code needs to be in each sheet. There's a Workbook_SheetChange even in the ThisWorkbook code module which will work on all sheets.
What's the code look like at present?

wilg
01-30-2011, 07:30 PM
Hi, here is the code.
I use activesheet. calculate as I have the workbook on manual calculate.
I need to insert the msgbox code into these 250 sheets.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
If Range("ai56") > 5 Then
MsgBox "You have keyed in more than permitted days."
End Sub

p45cal
01-30-2011, 07:50 PM
Just put that code in the module I mentioned and delete all the versions behind each sheet. It'll work on whichever sheet is being changed.

wilg
01-30-2011, 07:55 PM
That's it....works good. Thanks again p45cal!