How do I share VBA code in multiple workbooks?

[Guest]

Hello,

I've been forced to do a bit of simple VBA programming and thanks to this
forum have something that is working quite well. It's just some user forms
to prompt for pws and routines to manage protecting/unprotecting 20+ sheets.

Now that I have these form/routines put together in this one workbook, is
there a way that I can reuse this generic stuff in the other 6-8 workbooks
with the same need? I explored the add-ins and was able to save it as such,
and then pull it in to another workbook. While it pops up the forms, the
protect/unprotect doesn't work. I suspect because the add-in is in another
project from the main workbook, the add-in main routine:

For Each ws In ThisWorkbook.Worksheets
ws.EnableSelection = xlUnlockedCells
ws.Protect Password:=ProtectionPassword, DrawingObjects:=True,
Contents:=True, Scenarios:=True
Next ws

probably only iterates within the add-in Project.

Thanks in advance for any guidance.

Russ

 

[Guest]

You have explicitly referenced ThisWorkbook in the code meaning the the code
will act upon the workbook running the code (your addin in this case). What
you really want to run it against is the active workbook so something like
this perhaps...

For Each ws In ActiveWorkbook.Worksheets
ws.EnableSelection = xlUnlockedCells
ws.Protect Password:=ProtectionPassword, DrawingObjects:=True,
Contents:=True, Scenarios:=True
Next ws

 

[Bob Phillips]

Try ActiveWorkbook instead of ThisWorkbook.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Guest]

Dead on. Thanks Jim.

You have explicitly referenced ThisWorkbook in the code meaning the the code
will act upon the workbook running the code (your addin in this case). What
you really want to run it against is the active workbook so something like
this perhaps...

For Each ws In ActiveWorkbook.Worksheets
ws.EnableSelection = xlUnlockedCells
ws.Protect Password:=ProtectionPassword, DrawingObjects:=True,
Contents:=True, Scenarios:=True
Next ws

 

[Guest]

Right as well. Thanks much Bob.

Try ActiveWorkbook instead of ThisWorkbook.

--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)