Any Suggestion on how to improve?

[74Pumpkin]

I have attached a file. I'm looking for some suggestions if you have any. Also I'm looking for a way to do a few more things.

1st. I have a user form to add new people. I would like a way to automate creating a word document from a master doc, of the new persons name and having it hyperlinked to them in the spreadsheet. I have a form in word that I track information on employees. ( Or a better way of doing it also)

2nd. I currently have every day of the week thoughout the year refrenceing the info section. ( We have alot of changes in schedule) When I make a change it changes the history also. I.E. If I change Jeff's schedule in September. Jan thru Aug is also changed. Is there a way I can insert new information to each day for the future without changing the past? Some type of copy down from type of code? ( Not sure this makes sence)

Any other helpful hints on layout or better code would be nice. this is my first attempt at using VBA. I've had alot of help from here and other boards.

Thanks

Jeff

[Len Piwowar]

Have you thought about using Outlook? Create a folder for each employee with each having there own calendar and journal? For what you are trying to do I believe it may be a better application for the task.

[74Pumpkin]

I tried using Outlook at first but it didn't work very well for what I was looking for. Its that all in one look that is important. Thanks for the suggestion!

[mdmackillop]

Part 1, Create a Range Name where the names are to be listed (MyNames) M3:AG3. The code below should create the word documents and the hyperlink.
MD



Private Sub Worksheet_Change(ByVal Target As Range)
Dim WordBasic As Object
Dim DefPath As String
Dim MyAdd As String
DefPath = "C:\AAA\"
Application.ScreenUpdating = False
If Not Intersect(Target, Range("MyNames")) Is Nothing Then
Dim WordDoc As Object
Set WordDoc = CreateObject("word.document")
'WordDoc.Application.Visible = True
WordDoc.SaveAs DefPath & Target & ".doc"
WordDoc.Application.Quit
Set WordDoc = Nothing
End If
Target.Select
MyAdd = DefPath & Target & ".doc"
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:=MyAdd
Application.ScreenUpdating = True
End Sub

[74Pumpkin]

Thanks for the response MD.

As I am real new to VBA it took me a while to figure out that this is not suppose to go into the User form code. Anyway. I made the range MyNames as you said to, but I keep getting an runtime error #13 on the line

MyAdd = DefPath & Target & ".doc"

I changed a few things that I thought might be wrong but to no avail. Would it be easier to put this in the user form that adds names? I?m not too concerned on doing a mass conversion but to automate for the new people being put in.

Thanks for the help

Jeff

[mdmackillop]

Hi Jeff,

I've incorporated the code in your sample. I hadn't seen your New Employee form, so I've changed the function to call the code from there. I've rejigged your button code to add new names in the next available cell. I've also added a ShowAll select statement at the top of the list to reduce cycling through all the values. Makes it quicker at this debugging stage.
MD

[74Pumpkin]

Thanks MD. That works great!. It really speeds things up for me...