Merging and formatting a cell in workbook_open

H

Hari

Hi,

I have a workbook_open event, in which Im unprotecting a sheet, copy a
cell's contents (A3 Cell) in that unprotected sheet and paste it in to cell
J12 of the same sheet and then protecting back the sheet.

Idea seeking - Im quite wary of my VB project password being "hacked" in to.
Plainly speaking I dont have much problem if they see the code etc but in
the workbook_open event the password of the sheet is given which I would not
like to disclose. Its because I use the same password for protecting other
sheets and the workbook itself. I understand that worksheet passwords can be
easily hacked but after hacking I believe the hacker can only unprotect the
sheet and will not know what password I have kept. ( that is im fine if the
hacker hacks the sheet but as a result of hacking I dont want the actual
password to be known).

Doubt - Hence, I thought that since Im performing a fairly simple operation
of copy paste in that sheet, why not unprotect those 2 cells ( A3 and J12 )
and this way when the code runs I would not have to supply the password of
the worksheet in the code. Now, while doing this operation I want the cells
J12 to P13 to be merged and the resultant cell J12 to have a date format.

Presently my code is :-

Private Sub Workbook_Open() 'line no 1
Sheets("Report Data").Unprotect Password:="g" 'line no 2
Sheets("Report Data").Cells(12, 10) = Sheets("Report Data").Cells(3, 1)
'line no 3
Sheets("Report Data").Cells(3, 1) = "" 'line no 4
Sheets("Report Data").Protect Password:="g", 'line no 5
End sub()

Since I will be doing away with line no 2 and line no 5, I want to put a
code just before the present line no 3 which merges cell J12 to cell p13 and
also formats the resultant cell J12 to date format.

Please guide me.

Regards,
Hari
India
 
D

Dave Peterson

First, if the user can hack into this worksheet, they'll be able to hack into
any worksheet--your common password doesn't mean a lot.

In fact, your common password gets translated into a code that could represent a
lot of "real" passwords. (And the hacking algorithm could show the password
that works for this workbook/worksheets and will work for all
workbooks/worksheets with the same "real" password.)

J.E. McGimpsey has some nice notes (and a password hacking program <vbg>) at:
http://www.mcgimpsey.com/excel/removepwords.html

But you could protect your VBA project to make it a little more difficult for
the users to see the code.

Inside the VBE,
Tools|VBA Project Properties|Protection tab

But this password can be cracked, too.

I do usually protect my code, but I usually protect a worksheet with no
password. It stops people from overwriting formulas, but makes it slightly
easier for me--I don't forget the password!

and I recorded a macro when I merged a cell and applied a date format:

With Worksheets("sheet1").Range("p12:j13")
.Merge
.Value = Date
.NumberFormat = "mm/dd/yyyy"
End With

(I even stuck the current date in.)
 
H

Hari

Hi Dave,

Thanx a lot for replying to my query.

Actually I went thru JEM's notes and thats why I got apprehensive that
people could hack thru my worksheets/books.

I have protected my VB code and I was fearing that if they hack my VB code
then they will get to know the real password. From ur Message I now
understand that there are programs which other than unprotecting can even
tell the password which was used to protect the sheet.

I was being foolish. I should have recorded a macro for merging the cells. I
m in to stage of If and for statements in VB and dont have much syntax
knowledge beyond that. I forgot completely that macro recording can be used
if all else fails. Thanx a lot for ur help.

Regards,
Hari
India
 
Top