VBA code not working when distributed

**colarguns** · 12-11-2014, 10:31 AM

I have a workbook containing a few wsheets, and once I supply the code to the (This Workbook), the code works fine. As an FYI, the code's objective is to lock the scroll areas on each sheet. When I email this file to another user, the code isn't working. This is mainly due to their security settings, which are set to not allow macros. Is there a workaround to this? My wants or needs are this, when the user opens the file, the scrolling areas I set in VB will work seamlessly.

I was thinking along the lines of creating a macro to "freeze or lock" the entire workbook, and the user will have to follow instructions to enable macro security properly. What this really boils down to is that I want to maintain the aesthetics of the design.

While I am in here, and being a separate issue, when I look in the editor, it seems that vba projects keep adding on top of another, and I cannot delete these in the editor. I have to delete these files in the directories and start over again. When I start saving a file, somehow it adds yet another vb project (same file name). Weird. Any ideas?

Thanks!

Private Sub Workbook_Open()
Worksheets("Main").ScrollArea = "A1:AL115"
Worksheets("1st Qtr").ScrollArea = "A1:Ar130"
Worksheets("2nd Qtr").ScrollArea = "A1:Ar130"
Worksheets("3rd Qtr").ScrollArea = "A1:Ar130"
Worksheets("4th Qtr").ScrollArea = "A1:Ar130"
Worksheets("REP HOURS").ScrollArea = "A1:Ar130"
Worksheets("UNION ARTICLES").ScrollArea = "A1:M40"
Worksheets("UNION REPS").ScrollArea = "A1:S80"
Worksheets("L1458 Stewards").ScrollArea = "A1:Q60"
End Sub

**Richard Buttrey** · 12-11-2014, 10:40 AM

Hi,

One way would be to get your users to make the folder in which they hold your files a Trusted Location. The Macro security is there for a reason.

Not sure what you're getting at with your second question but since it is a separate issue I suggest you raise a thread in its own right and give a little more clarification.

**colarguns** · 12-11-2014, 10:54 AM

Thanks Richard and Merry Christmas to you sir,

Your input is appreciated, however, being that I work in the Federal Government, there will be hundreds of people that will be receiving this file over time. Therefore, your approach would be a little cumbersome "respectfully". If there is no way around this, then all I can do is to provide written instruction on the main worksheet. My second concern would be that my password protection stay in tact. Thanks again!

**Andy Pope** · 12-11-2014, 11:31 AM

Rather than restrict the scroll area, which requires code, could you not hide the rows and columns to the right and below the area the user is allowed to see?

Edit: I guess this does not actually stop the user from scrolling the cells out of view, so maybe not a good work around.

Ghost VBProjects in the project window of the IDE can be caused by other addins.

**colarguns** · 12-11-2014, 11:42 AM

Thanks Andy, yes, by hiding rows or columns, this will still not solve, however, A+ for effort! As for Ghost VB, sounds scary, lol. I am unsure what all that entails, but if there were a method out there that would solve the dilemma, I am all for it! Thanks again sir.

**Andy Pope** · 12-11-2014, 11:48 AM

Actually if you also remove the scrollbars then it does work. See attached.

With the vbprojects you would need to check what addins (com addins) you have and remove them. Then add them back one at a time testing to see if the project duplication occurs.