anyone can help ?

[ReD-DevIL]

Public Function Get_dir()

Windows("PEM.xls").Activate

With Worksheets("sheet1")
.Activate
Get_dir = .Range("H1001").Value
End With

End Function

can anyone help me figure out what does this function do ? especiall
the line Get_dir = .Range("H1001").Valu

 

[Jörg]

Hi,

especially this line returns the value of cell "H1001"

if you run a line like msgbox get_dir() you´ll get a message-box which shows
you the value of cell H1001

best regards

 

[funkymonkUK]

Public Function Get_dir()

Windows("PEM.xls").Activate

With Worksheets("sheet1")
.Activate
Get_dir = .Range("H1001").Value
End With

End Function

can anyone help me figure out what does this function do ? especially
the line Get_dir = .Range("H1001").Value

While from the info you have given. I would say you have a workbook
which is opened called PEM.xls. in this workbook there is a sheet 1
which it activates and then declares what get_dir is from the value of
cell H1001.

The get_dir value is then past onto macro which would use that value

 

[broro183]

Hi,
The comments I have included after each line explain the purpose of the
line.

Public Function Get_dir()
Windows("PEM.xls").Activate
'makes the above file the active window. If the file is not open macro
will
'probably quit or may ask if you want to debug the code.
With Worksheets("sheet1")
'the "with" is used to allow for multiple things to be done to the same
object
'(ie "sheet1"). In this case, make it the active sheet & use a value
from one
'of its cells.
Activate
'makes sheet1 the active sheet
Get_dir = .Range("H1001").Value
'takes the value of cell H1001 & assigns it to a variable called
"Get_Dir"
End With
End Function

The name of the function suggests that cell H1001 may contain a file
path (or directory) & the aim is to obtain this value.
If the main/only purpose of this function is to retrieve the value of
cell H1001 it could be shortened to either of the following lines (the
first one may not work - I haven't tested it):

Public Function Get_dir()
Get_dir = Windows("PEM.xls").Worksheets("sheet1").Range("H1001").Value
'or
Get_dir =
Workbooks("PEM.xls").Worksheets("sheet1").Range("H1001").Value
end function

hth
Rob Brockett
NZ
Always learning & the best way to learn is to experience...

 

[ReD-DevIL]

Thanks alot, you guys are very helpful.
Actually i am given this assignment for my attachment, whereby i hav
to edit the program but i have to understand the codes befor
proceeding to make any editing and i am not quite familiar with exce
programming. I just received this codes today, but too bad i don't hav
the program with me now. I hope you guys will be there to guide m
through... :

 

[funkymonkUK]

thanks for the response. We only try our best to help people lik
yourself who have little/no knowledge of VBA code. Its through helpin
you that others as well as ourselves can develop and learn the ways th
VBA works.

If you need any help just shout!! But not to loud some people got han
overs from the weekend

 

[davesexcel]

Can Anyone help...

It would help readers of the forum if you actually put a topic in th
Title Box instead of " Can Anyone help"

It also helps when people are searching forums for information..

 

[ReD-DevIL]

Hi, i think i managed to understand the sub getdir () afte
explainations from you guys. But i encounter another problem, thi
program actually needs the user to enter userid and password. However
when i enter the userid and password, it says
Run-time error '76': Path not found

If (IsNumeric(userid)) Then
dir = Get_dir()
Open dir & "passwd" For Random Access Read As #1
Get #1, userid, passwd1
Close
If (passwd <> passwd1 Or passwd = Empty) Then
MsgBox ("Invalid User ID or Password")
Else
Login_ok = 1
End If
Else
MsgBox ("Pls enter your badge number only.")
frm_newuser.Hide
End If


then the line with a smile behind is highlighted with yellow.

can anyone explain to me what does this error means

 

[funkymonkUK]

what is the value of get_dir?

do you have access to that directory

 

[ReD-DevIL]

dir = Get_dir()
Open dir & "passwd" For Random Access Read As #1

I presume when it open dir, then Get_dir() sub is called then a fil
named passwd is open right ? (There are two hidden file in the sam
folder as the database file userid & passwd.) So the passwd fil
contains the password? I have access to the directory but i trie
entering all the passwods. The same error occurs

 

[funkymonkUK]

by the sounds of things you learning how to hack a excel workbook.

yeah you right in saying that it opens a file called passwd howeve
have you open the userid and matched it with the password?

if you want i can have a look at it for you

 

[ReD-DevIL]

The passwd problem is solved, my supervisor unlocked it.
Now, i am asked to improve the program.
If you have the time to help me, here is the brief introduction o
the project and what my supervisor wants me to improve on.

The company keep track of their maintenance date of their equipment s
whenever the staff finish the maintenance on a particular euqipmen
they will log in to the program to enter the record.

so when the user enter the record, the program will based on the dat
then change the colour of the cell to show that maintenance is done o
that date. The file 1.doc attached will show what i am trying to say
But that has already been done, then the improvement:

1)I have to calculate their next maintenance due date and change th
colour of the cell to indicate that.

2) Because there are 2 type of maintenance for the equipment lets sa
CAL & PEM. CAL is done every 6 months while PEM is every 3 months bu
if both maintenance due date are within one month they will have to b
brought forward to be conducted on the same date.

How do i do this

+-------------------------------------------------------------------
|Filename: 1.doc
|Download: http://www.excelforum.com/attachment.php?postid=4500
+-------------------------------------------------------------------

 

[ReD-DevIL]

Got another question...
How can i make the program create a new worksheet yearly for next yea
with dupilcating?
E.g. for 2006 the program have to create a worksheet named 2007 an
once into 2007, it create a worksheet for 2008

 

[ReD-DevIL]

Got another question...
How can i make the program create a new worksheet yearly for next yea
WITHOUT dupilcating?
E.g. for 2006 the program have to create a worksheet named 2007 an
once into 2007, it create a worksheet for 2008

 

[funkymonkUK]

The passwd problem is solved, my supervisor unlocked it.
Now, i am asked to improve the program.
If you have the time to help me, here is the brief introduction o
the project and what my supervisor wants me to improve on.

The company keep track of their maintenance date of their equipment s
whenever the staff finish the maintenance on a particular euqipmen
they will log in to the program to enter the record.

so when the user enter the record, the program will based on the dat
then change the colour of the cell to show that maintenance is done o
that date. The file 1.doc attached will show what i am trying to say
But that has already been done, then the improvement:

1)I have to calculate their next maintenance due date and change th
colour of the cell to indicate that.

2) Because there are 2 type of maintenance for the equipment lets sa
CAL & PEM. CAL is done every 6 months while PEM is every 3 months bu
if both maintenance due date are within one month they will have to b
brought forward to be conducted on the same date.

How do i do this ?

Hi Could you not do it how I have by having just the date? It woul
shorten the amount of columns used and would cut down on file size
this would also eliminate the need to have one for end year?

About the condition if they are on the same month will they been don
on the same month? please explain abit mor

 

[funkymonkUK]

forgot to add the attachmen

+-------------------------------------------------------------------
|Filename: Example.zip
|Download: http://www.excelforum.com/attachment.php?postid=4501
+-------------------------------------------------------------------

 

[ReD-DevIL]

Hi, there are alot more complicated problems with my project.
Is there any way i can contact with you more conveniently?
e.g. msn messenger

 

[funkymonkUK]

sorry unfortunately i am at work and they block msn messanger. you coul
email me using my email addy on my profile

 

[ReD-DevIL]

oh well its the same then haha.
Maybe i can try asking my supervisor to change the format.
On the second condition let say if PEM is conducted on 15/03/06 and CAL
is due on 13/04/06 which is lesser than one month difference, they will
both be conducted on the same day which is one 14/03/06.

 

[funkymonkUK]

let me have a bash at it