is it possible to make comment appear faster in excel?

Excel referencing ....

• QUESTION: I have two workbooks call them A and B. Workbook A is just a spreadsheet with a button to fix problems with workbook B. Call it a patch. Option Explicit Sub Button1_Click() Dim oExcel As New Excel.Application Dim wbResults As Workbook Dim nIndex As Integer Dim vFiles As Variant oExcel.ScreenUpdating = False oExcel.DisplayAlerts = False oExcel.EnableEvents = False On Error Resume Next Dim oWorksheetBudget As Excel.Worksheet Dim oWorksheetSFORM As Excel.Worksheet Dim oWorksheetFTEAllocation As Excel.Worksheet '******************************************************************* 'open the file batch and get the file name vFiles = oExcel.GetOpenFilename(FileFilter:="Workbooks (*.xls), *.xls", _ Title:="Select File For Upload", MultiSelect:=True) ' Make sure dialog wasn't cancelled - in which case ' vFiles would equal FALSE and therefore is not an array. If Not IsArray(vFiles) Then MsgBox "No files selected." Exit Sub End If '**************************************************************************** '**************************************************************************** 'loop the file bacth for the upload oExcel.ScreenUpdating = False Set wbResults = oExcel.Workbooks.Open(CStr(vFiles(nIndex)), False) 'Process workbook B Dim pWord3 As String pWord3 = "1234" 'unprotects entire workbook I hope wbResults.Unprotect Password:=pWord3 Set oWorksheetBudget = wbResults.Worksheets("BudgetWorkSheet") Set oWorksheetSFORM = wbResults.Worksheets("SForm") Set oWorksheetFTEAllocation = wbResults.Worksheets("FTEAllocation") 'Cell G7 currently contains a reference =SForm!K3 'I want to change this cell to have =SForm!K3+FTEAllocation!D29. I forgot to add the other reference. oWorksheetBudget.Range("G7").Value = oWorksheetSFORM.Range("K3").Value + oWorksheetFTEAllocation.Range("D29").Value 'Remove all cell validations on BudgetWorksheet oWorksheetBudget.Range("H10:H400").Validation.Delete 'protect entire workbook I hope wbResults.Protect Password:=pWord3 'Save the workbook with the original file name wbResults.Save On Error GoTo 0 oExcel.ScreenUpdating = True oExcel.DisplayAlerts = True oExcel.EnableEvents = True End Sub Any ideas would be appreciated.. ANSWER: One of the things that you can do while tweaking the code is to comment the line "On Error Resume Next". This will stop the flow of the program in the place that it fails, and it will give you some insight on what is not working. The other thing that you may be missing is the unprotection of the individual worksheets. The statements that you use unprotect the workbook, but they don't unprotect the contents of each worksheet. You can try adding a new statement like: oWorksheetBudget.Unprotect Password:=pWord3 Or whatever password you are using. ---------- FOLLOW-UP ---------- QUESTION: Ok that helps point me the right direction my error is the line: Set wbResults = oExcel.Workbooks.Open(CStr(vFiles(nIndex)), False) "Script out of range error"

• Answer:

  OK, you may try to modify it to something like: Set wbResults = oExcel.Workbooks.Open(CStr(vFiles(1)), False) This will open the first workbook that you have selected on the open workbook dialog. The nIndex was probably used for a loop, but as the loop is not there you can skip it and go directly to the first file on the selection.