So I inherited this excel worksheet that my company uses for time sheets. So I got this and my vb skills are little to nonexistent, lol. I can follow most of what it’s doing but I can’t quite pin down why it won’t pull the correct information. The time sheets consist of two things, the manager workbook and the timesheet itself where the employees enter time. Once an employee enters the time they email it to the manager who checks it and approves it. Upon hitting the approval button the workbook is supposed to check the manager workbook to see if the person hitting the buttons name is listed as a manager and if they are it should grab their name and the path on the server where the file should be saved. This is the part where its messing up. Currently it will find the manager and put their name in the correct cell on the worksheet, then it says it saving but for the life of me I can’t tell where. Here is the code from the manager signature file 'Sub find_user_sig_test() 'Dim find_user_sig As String Function find_user_sig() Dim usrName, objFound As String Dim obj As Object Dim rowCnt Dim rowNum 'validate input to see if MGR name exists 'if it does then exit loop and set find_user_sig to "ok" 'if not exist - set find_user_sig = "error" and goto end of function usrName = LCase(Environ("USERNAME")) objFound = "false" 'may not need this ??????? depends on extend of validation rowCnt = 1 rowNum = 0 For rowCnt = 1 To 65000 If LCase(Range("A" & rowCnt).Formula) = usrName Then objFound = "true" 'may not need this ??????? depends on extend of validation rowNum = rowCnt GoTo contenueScript End If Next rowCnt 'if sig is not found then set error state and end function find_user_sig = "sigNotFound" GoTo EndFunction contenueScript: 'sig was found and function can contenue 'return the user find_user_sig = "oK|" & Range("B" & rowCnt).Formula '& "|" & Range("C" & rowCnt).Value EndFunction: End Function This is the code from the find manager workbook. The line “ find_user_sig = "oK|" & Range("B" & rowCnt).Formula '& "|" & Range("C" & rowCnt).Value ” Is interesting because it has the last part & "|" & Range("C" & rowCnt).Value commented out. The column c holds the file path where the timesheet folder is supposed to be going. I uncommented it and didn’t make a difference. Here is the code from the timesheet worksheet the employee sends to the manager: 'approves/disproves the employ's time sheet by manager 'calls function in another excel timesheet that checkes to see if user is authorized to sign time sheets 'places managers name on the sig line of emp time sheet 'can only be run from a manager that has access to the HR folder holding the sig sheet ' 'called by sig_btn1,2,or3 and given the location that the signature and date should be placed 'see above Sub Mgr_sig_copy(sigLoc As String, dateLoc As String, sigBtn As String, compLoc As String) Dim wbName, pgName, errorState, sigDate, sigName, passwd, passwd_Final, Passwd_WB_File As String Dim openWB errorState = "error" 'initializes errorState 'turn off alerts and screen updateding Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False 'grabs password to protect/unprotect sheet Passwd5_WS passwd 'sub call byref Passwd5_WB_File Passwd_WB_File 'subcall byref 'unprotect sheet ActiveSheet.Unprotect Password:=passwd 'get Name of this employ WorkBook wbName = ActiveWorkbook.Name 'to get path use FullName pgName = ActiveSheet.Name 'name of the sheet that needs mgr sig 'open sig workbook Set objExcel = CreateObject("Excel.Application") On Error Resume Next Set openWB = objExcel.Workbooks.Open(Filename:="\\star\car\HR Folders\HR RESOURCES\remote_scripts_sig.xls", Password:=Passwd_WB_File) If Err.Number <> 0 Then 'usr can not open sig workbook and errorState = "error" 'close mgr sig workbook without saving openWB.Close SaveChanges:=False GoTo skipSig End If 'call/run macro in other workbook holding approved mgrs and grabs their name in errorState errorState = openWB.Application.Run("remote_scripts_sig.xls!find_user_sig") 'close mgr sig workbook without saving openWB.Close SaveChanges:=False 'select the location for the sig and then place it in corect cell If InStr(1, errorState, "oK|") = 1 Then 'mgr is approved to sign/approve time sheet 'pull out the name and pathname from errorState CharPos1 = InStr(1, errorState, "|") CharPos2 = InStr(CharPos1 + 1, errorState, "|") sigName = Mid(errorState, CharPos1 + 1, CharPos2 - CharPos1 - 1) ' sigName = Right(errorState, (Len(errorState) - 3)) Workbooks(wbName).Sheets(pgName).Range(sigLoc).Formula = sigName 'HRFolderPath = Mid(errorState, CharPos2 + 1) 'set date week was approved/signed sigDate = Now Range(dateLoc).Formula = sigDate 'Places computer name of submitter in a hidden field next to sig date computer_name = Environ("COMPUTERNAME") 'the enviromental variable to get the compouter name Range(compLoc).Formula = computer_name Range(compLoc).Font.ColorIndex = 2 'white text 'delete the button 'for the final release, activate the delete "reset page" button ActiveSheet.Shapes(sigBtn).Delete ActiveSheet.Shapes("Reset Page").Delete Range("B10").Select 'sub call byref used to protect worksheet once it has been signed by Manager Passwd5_WS_Final passwd_Final 'protect sheet for final release ActiveSheet.Protect Password:=passwd_Final 'turn on alerts and screen updating Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True 'Save workbook in HR Folders Select Case sigLoc Case "L45" FolderDates = Application.WorksheetFunction.Text(Range("E7"), "mm-dd-yy") & _ " to " & Application.WorksheetFunction.Text(Range("H7"), "mm-dd-yy") Case "L49" FolderDates = Application.WorksheetFunction.Text(Range("M7"), "mm-dd-yy") & _ " to " & Application.WorksheetFunction.Text(Range("P7"), "mm-dd-yy") Case "L53" FolderDates = Application.WorksheetFunction.Text(Range("U7"), "mm-dd-yy") & _ " to " & Application.WorksheetFunction.Text(Range("X7"), "mm-dd-yy") Case Else End Select EmpName = InStr(1, Sheets("Info").Range("C2"), ",") LastName = Left(Sheets("Info").Range("C2"), EmpName - 1) FirstName = Right(Sheets("Info").Range("C2"), Len(Sheets("Info").Range("C2")) - EmpName - 1) HRFolderPathName = HRFolderPath & FolderDates & "\" Set fs = CreateObject("Scripting.FileSystemObject") If fs.FolderExists(HRFolderPath) = False Then MsgBox "Timesheet folder:" & vbCrLf & HRFolderPathName & vbCrLf & _ "does not exist. Please contact HR for assistance.", vbOKOnly, "Timesheet folder does not exist" GoTo endSub Else End If ActiveWorkbook.SaveAs Filename:=HRFolderPath & FolderDates & "\" & LastName & " " & FirstName & " 2010.xls" ActiveWorkbook.Close GoTo endSub End If What I tried to do was comment out the HRFolder path and set it to a unc path, when I do that the managers name who approved the worksheet name dosent get added. Any help with this is greatly appreciated
Yes it was working, its what they were using until they needed it for another 6 month period. A just copied the pages and changed some dates.
