id prefer to have the buttons as that works well for the office, It wont be changing often maybe once or twice by the end of the year, if i could learn how to do it that would be far better in case i need to build one for each and every project engineer. Once they are set up on there account or pathway the main file for them i.e. F100 tracking ### (initials) will be allocated to them. I can change the file search id easy enough that's self explanatory but the link and the opening of the file is proving difficult to modify or at-least for me to find where i need to modify it.
sorry since i have shown the others what you have made so far they have loved the idea but now want different hings than i had originally wanted. I am trying to get them to use a master sheet for the F100 tracker which will then be in one place for all to use? If thats the case it will only be one more change if not (i.e each want there own) i will need to alter the script for each person.
If you have to alter the code for each person, then my suggestion of a pop up asking them to choose a file would probably work better. What do you think?
now want different things than i had originally wanted
Do you need help with the above?
If you could make a code up for the table I can put both options to the boss and then he can decided which he prefers. Thanks for all your help mate you made things so much easier already.
Replace the current macros in the worksheet code modules for F100 Detail - Design and F100 Detail - Production. Enter a Project Title and press the RETURN key. If you use this method, you no longer need the buttons or the macros in Module1. The F100 Tracking file will be opened, the data will be copied/pasted and The F100 Tracking file will be closed automatically without the user seeing what is happening.Code
- Private Sub Worksheet_Change(ByVal Target As Range)
- If Intersect(Target, Range("D6, H6, L6, P6, T6, X6")) Is Nothing Then Exit Sub
- Application.ScreenUpdating = False
- Dim srcWB As Workbook, desWS As Worksheet, ws As Worksheet, fnd As Range
- Dim flder As FileDialog, FileName As String, FileChosen As Integer
- Set desWS = ThisWorkbook.ActiveSheet
- Set flder = Application.FileDialog(msoFileDialogFilePicker)
- flder.Title = "Please Select a folder and file."
- FileChosen = flder.Show
- FileName = flder.SelectedItems(1)
- Set srcWB = Workbooks.Open(FileName)
- For Each ws In srcWB.Sheets
- Set fnd = ws.Range("C5, C32 ,H5,H32,M5,M32").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
- If Not fnd Is Nothing Then
- fnd.Offset(3, -1).Resize(20, 4).Copy Target.Offset(2, -2)
- Target.Offset(24) = fnd.Offset(1)
- Target.Offset(23) = fnd.Offset(, 2)
- Target.Offset(25) = fnd.Offset(1, 2)
- End If
- Next ws
- srcWB.Close False
- Application.ScreenUpdating = True
- End Sub
The boss didn't like the box as it had to be done after entering the F100 ID no in each time. Thanks again for your help. I'm going to have a play around over the next few days to see if i can get the script to work off another button. i.e. so i can enter all the f100 id no is on all fields and then hit the button to fetch the data for all the entries. Much appreciated once again its working a treat so far.
You are very welcome.