Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I share VBA code in multiple workbooks?
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I share VBA code in multiple workbooks?
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 -- HTH... Jim Thomlinson "xrbbaker" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I share VBA code in multiple workbooks?
Try ActiveWorkbook instead of ThisWorkbook.
-- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "xrbbaker" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I share VBA code in multiple workbooks?
Dead on. Thanks Jim.
"Jim Thomlinson" wrote: 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 -- HTH... Jim Thomlinson "xrbbaker" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How do I share VBA code in multiple workbooks?
Right as well. Thanks much Bob.
"Bob Phillips" wrote: Try ActiveWorkbook instead of ThisWorkbook. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "xrbbaker" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
share and merge workbooks | Excel Discussion (Misc queries) | |||
macro or code to open multiple workbooks | Excel Discussion (Misc queries) | |||
Code needed to print multiple workbooks | Excel Programming | |||
Can 2 workbooks share data in Name Fields? | Excel Programming | |||
VBA code in multiple workbooks | Excel Programming |