Hello!

After weeks of Googling and trying unsuccessfully to fuddle through
VBA in order to alter Jim Rech's AutoSize MergeCells macro so that it
would decrease row height instead of just increasing, I ended up
uglifying my spreadsheet. So now it has no merged cells and uses 10-or-
so text boxes per sheet. Plus the user input is in the upper right and
the cells which use that input are diagonally across in the lower left
(alot of unused white space). But now I can use my hackjob autofit
macro that doesn't have to worry about merged cells:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
Me.Unprotect Password:="bulldog"
Rows("21:22").AutoFit
enditall:
Application.EnableEvents = True
Me.Protect Password:="bulldog"
End Sub

The problem is that even with the workbook protected, you can right-
click on the sheet tab and see the "View Code" menu option and you can
see the VBA and password to unprotect ("bulldog"). How can I hide
that? Can I
put some of the code in the worksheet and the rest (the part that
contains the password) in a general or workbook module? If so, how do
I split the code up to do that?
Thanks!
VR/Lost

You have to lock the VBA project for viewing and password protect it. In the
VBA editor, click on the VBAProject for your workbook in the Project Explorer
window. Right-click and select VBAProject Properties. IN the dialog that
opens, select the Protection tab. Click a checkmark into the "Lock project
for viewing" checkbox, then enter & confirm a password. Finally, click OK.
You will have to save, close, and re-open the workbook for the password
protection to take effect. Your users should no longer be able to view any of
the VBA code in the workbook.

Keep in mind that Excel's internal security is very weak. Once a user has a
workbook open, there are macros easily found on the internet that will remove
all password protection from it.

Hope this helps,

Hutch

"Lostguy" wrote:

> Hello!
>
> After weeks of Googling and trying unsuccessfully to fuddle through
> VBA in order to alter Jim Rech's AutoSize MergeCells macro so that it
> would decrease row height instead of just increasing, I ended up
> uglifying my spreadsheet. So now it has no merged cells and uses 10-or-
> so text boxes per sheet. Plus the user input is in the upper right and
> the cells which use that input are diagonally across in the lower left
> (alot of unused white space). But now I can use my hackjob autofit
> macro that doesn't have to worry about merged cells:
>
> Private Sub Worksheet_SelectionChange(ByVal Target As Range)
> On Error GoTo enditall
> Application.EnableEvents = False
> Me.Unprotect Password:="bulldog"
> Rows("21:22").AutoFit
> enditall:
> Application.EnableEvents = True
> Me.Protect Password:="bulldog"
> End Sub
>
> The problem is that even with the workbook protected, you can right-
> click on the sheet tab and see the "View Code" menu option and you can
> see the VBA and password to unprotect ("bulldog"). How can I hide
> that? Can I
> put some of the code in the worksheet and the rest (the part that
> contains the password) in a general or workbook module? If so, how do
> I split the code up to do that?
> Thanks!
> VR/Lost
>
>
>