Personal Macro Workbook

[Pestomania]

I currently have very little experience with Personal Macro Workbook (PMW) . I am working with a Macro that monitors a specific cell and activates when the data changes. The macro is nested under that specific sheet rather than a module and it works perfectly. Only problem is, I would like to move that macro to a PMW and have it only monitor that specific sheet. Below is the code.

Private Sub Worksheet_Change(ByVal Target As Range)    If Target.Address = "$C$7" Then        Run resetformatting    End IfEnd Sub

But once this macro is placed into the PMW, it no longer works. How can I make this macro monitor sheet("master") and determine when the cell changes?

 

[JoeMo]

If you want to monitor the sheet "master" for changes in a specific cell, your worksheet_change code needs to be in that sheet. Why do you want to put the code in your PERSONAL.XLSB workbook?

 

[Pestomania]

If you want to monitor the sheet "master" for changes in a specific cell, your worksheet_change code needs to be in that sheet. Why do you want to put the code in your PERSONAL.XLSB workbook?

I am using the personal because I use these macros on 50 workbooks a day. I would have to copy the code into each one.

Idid have the code in that sheet and it works great but I do not want to add it Everytime I open a book..

Thank you

 

[shg]

You can put code in Personal that will run when any cell in any open workbook is changed, but how would it know which worksheet is the one of interest?

 

[Pestomania]

You can put code in Personal that will run when any cell in any open workbook is changed, but how would it know which worksheet is the one of interest?

The cell is C7 on the "Master" sheet. It should only be monitoring that specific cell. Once input is there, it will run the macro to make various changes throughout the spreadsheet. The various changes can include but are not limited to conditional formatting, value updating, etc.

Thank you

 

[shg]

The change event occurs. It knows the cell and worksheet -- that's it:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

What logic does it apply to know that the sheet is the sheet of interest? By worksheet name? Something else?

 

[Pestomania]

The change event occurs. It knows the cell and worksheet -- that's it:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

End Sub

What logic does it apply to know that the sheet is the sheet of interest? By worksheet name? Something else?

The above code was placed in that specific sheets coding. So I placed the above code in "Sheet1" VBA profile. But now that it is moving to a Personal Macro Workbook. I need it to be set up to look at "Sheet 1" and cell "C7".

In C7 there are four options: small, medium, large, other. Based on the option selected, it will set up the rest of the workbook. It currently works if it is placed in "Sheet1"s specific VBA. But I need it to work from the PMW.

Unfortunately, I cannot change anything in the format of the workbook so it has to always work.

 

[shg]

When you put code in a workbook's sheet module and an event is triggered, you KNOW what worksheet you're on, right?

When you put event code in Personal, all it knows is the sheet. Is knowing that the name is Sheet1 sufficient? Should the macro run if C7 in some random workbook with a sheet name Sheer1 is changed?

 

[Pestomania]

When you put code in a workbook's sheet module and an event is triggered, you KNOW what worksheet you're on, right?

When you put event code in Personal, all it knows is the sheet. Is knowing that the name is Sheet1 sufficient? Should the macro run if C7 in some random workbook with a sheet name Sheer1 is changed?

It needs to know the sheet name and the exact cell. Currently the code monitors that cell constantly and once the cell value changes, the macro runs immediately. The trigger is that specific cell.

I know this is a difficult question but it has to monitor that specific cell constantly and I would really like it to work from the private macro workbook.

Thank you

 

[shg]

The problem isn't complicated, we're just not communicating.

The computer says, "OK, cell C7 changed on some sheet in some workbook. Should I run the macro?"

What do you tell it?