Merging and formatting a cell in workbook_open

  • Thread starter Thread starter Hari
  • Start date Start date
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
 
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.)
 
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
 
Back
Top