At work we use excel as a work log and can click on a hyperlink that links to the folders of work people have produced.
It seems like a good idea to create a macro that will create the work folder too (to save people from having to do it manually)
I am looking for a user friendly function that will work as follows.
On click of cell (or similar) - this will be in the same row as the entry.
1. Has user written their entry (fills in at least cells at column A and C)?
If NO then display a message saying to fill them in.
If YES then continue to 2.
2. Does the folder already exist?
If NO then create folder and give completion message
If YES give message saying that the folder exists
This is a collection of how I think the code might go, it's not fully functioning yet.
Any suggestions?
It seems like a good idea to create a macro that will create the work folder too (to save people from having to do it manually)
I am looking for a user friendly function that will work as follows.
On click of cell (or similar) - this will be in the same row as the entry.
1. Has user written their entry (fills in at least cells at column A and C)?
If NO then display a message saying to fill them in.
If YES then continue to 2.
2. Does the folder already exist?
If NO then create folder and give completion message
If YES give message saying that the folder exists
This is a collection of how I think the code might go, it's not fully functioning yet.
Any suggestions?
Code:
Sub FolderCreator()
Set Target = ActiveCell
'Define the date
Application.Goto Range("A" & ActiveCell.Row), True
r = ActiveCell
'Define the job title
Application.Goto Range("C" & ActiveCell.Row), True
s = ActiveCell
t_left = Left(r & s, 1)
t_right = Right(r & s, 1)
link_folder = "C:\Documents and Settings\Username\Desktop\" & Right(Year(r), 2) & Right("0" & Month(r), 2) & "\"
link_subfolder = link_folder & Right(Year(r), 2) & Right("0" & Month(r), 2) & Right("0" & Day(r), 2) & "_" & s
If tleft <> " " And tright <> " " Then
For i = 2 To 65536
If Target.Address = "$M$" & i Then MkDir link_subfolder
Next i
End If
End Sub
Last edited: