When an excel sheet opens, I want a vba code to open a specific word document doc1 only…
- User
When an excel sheet opens, I want a vba code to open a specific word document doc1 only if the values in the last row of column B and Column D are different. If the particular word document doc1 is left open the vba code should not attempt to open the already open document doc1 again. Only opening the word document doc1 if it is not or no longer open therefore only having one instance of the word document doc1.
After finishing and closing the word document, I will manually enter a date in column E of the last row entry. After entering the date a differnet word document doc2 should open. While the word document doc2 is open, if I click on different cell in column E it should not attempt to open the already open document doc2 again. Only opening the word document doc2 if it is not or no longer open therefore only having one instance of the word document doc2.
I also need a code that runs everytime I select or enter a value in a cell in Column E. I want the vba code to copy 10 values of 10 cells in the same row as the cell in column E I selected (B,C,D,E,F,G,H,I,J,L) and appended their values to column M in the following order M1 to M10. The values changing for every cell I select in column E and the values cleared when I also leave the sheet. The word document doc2 that is opened by activity on column E as mentioned above will capture data from colum M1 to M10.
All these codes will by about 50 excel sheet for the same purpose. Can the codes be called from modules?
- Assistant
Yes, the codes can be organized in modules and called upon by each of the 50 Excel sheets. Here is a possible implementation of the described functionalities:
In a module, define the following variables:
Dim wordApp As Word.Application Dim doc1 As Word.Document Dim doc2 As Word.Document Dim lastRow As Long Dim colBLastVal As Variant Dim colDLastVal As Variant Dim colELastVal As VariantThe first three variables are used to keep track of the Word application and documents. The other variables will be used to check if the last values in columns B, D, and E have changed.
In the
Workbook_Openevent, add the following code to initialize the Word application and opendoc1:Set wordApp = New Word.Application wordApp.Visible = True On Error Resume Next Set doc1 = wordApp.Documents("doc1.docx") On Error GoTo 0 If doc1 Is Nothing Then Set doc1 = wordApp.Documents.Open("path\to\doc1.docx") End If lastRow = ThisWorkbook.Sheets(1).Cells(Rows.Count, "B").End(xlUp).Row colBLastVal = ThisWorkbook.Sheets(1).Cells(lastRow, "B").Value colDLastVal = ThisWorkbook.Sheets(1).Cells(lastRow, "D").ValueThis code creates a new instance of the Word application and sets it to visible. It attempts to retrieve an already open instance of
doc1, and opens it if it is not found. Finally, it setslastRowto the last used row, and setscolBLastValandcolDLastValto the values in the last row of columns B and D.Add the following code to the
Worksheet_SelectionChangeevent:If ActiveCell.Column = 5 Then If colELastVal <> ActiveCell.Value Then On Error Resume Next Set doc2 = wordApp.Documents("doc2.docx") On Error GoTo 0 If doc2 Is Nothing Then Set doc2 = wordApp.Documents.Open("path\to\doc2.docx") End If colELastVal = ActiveCell.Value End If End IfThis code checks if the active cell is in column E. If it is, and the value has changed since the last time the event was triggered, it attempts to retrieve an already open instance of
doc2, and opens it if it is not found. Finally, it setscolELastValto the current value in the active cell.Add the following code to the
Worksheet_Changeevent:If Target.Column = 5 Then Dim rowValues As Range Set rowValues = ThisWorkbook.Sheets(1).Range("B" & Target.Row & ":L" & Target.Row) If Not Intersect(Target, rowValues) Is Nothing Then Dim mValues As String Dim i As Long For i = 1 To 10 mValues = mValues & rowValues(1, i).Value & " " Next i ThisWorkbook.Sheets(1).Cells(Target.Row, "M").Value = mValues End If End IfThis code checks if the changed cell is in column E. If it is, and it is within the range of columns B to L in the same row, it extracts the values of the 10 cells in that row and concatenates them into a string
mValues. It then writesmValuesto the corresponding cell in column M.Finally, add the following code to the
Workbook_BeforeCloseevent:On Error Resume Next doc1.Close False doc2.Close False wordApp.Quit Set doc1 = Nothing Set doc2 = Nothing Set wordApp = Nothing On Error GoTo 0This code closes the Word documents and application, and releases the associated objects.
Note that you may need to adjust the paths to the Word documents in the code, as well as add error handling and other features depending on your specific requirements.
- User
Can I use the workbook open event in a worksheet activate event so that it is the worksheet that opens the word document
- Assistant
Yes, you can use the
Worksheet_Activateevent instead of theWorkbook_Openevent to open the Word document associated with each worksheet. Here's an example implementation:- Create a new module and define the following variables:
Dim wordApp As Word.Application Dim doc1 As Word.Document- In the
Workbook_Openevent, initialize theWord.Applicationobject and opendoc1:
Private Sub Workbook_Open() Set wordApp = New Word.Application wordApp.Visible = True Set doc1 = wordApp.Documents.Open("path\to\doc1.docx") End Sub- In each worksheet module, add the following code to the
Worksheet_Activateevent:
Private Sub Worksheet_Activate() On Error Resume Next Set doc1 = wordApp.Documents("doc1.docx") On Error GoTo 0 If doc1 Is Nothing Then Set doc1 = wordApp.Documents.Open("path\to\doc1.docx") End If End SubThis code attempts to retrieve an already open instance of
doc1, and opens it if it is not found. Note that theOn Error Resume Nextstatement is used to handle the case wherewordApp.Documents("doc1.docx")throws an error because the document is not open.You can add similar code to the
Worksheet_SelectionChangeandWorksheet_Changeevents to open other word documents and update cell values, as described in the previous answer.Note that you may need to adjust the paths to the Word documents in the code, and make sure that each worksheet has its own copy of
doc1(or any other Word document that you want to open). - User
I do not want the workbook open event to open the word document. I want the document to only be opened by a worksheet and only when I have clicked on a cell in column E
- Assistant
Understood. You can modify the code as follows:
- In a new module, define the following variables:
Dim wordApp As Word.Application Dim colELastVal As Variant Dim doc2 As Word.Document- In each worksheet module, add the following code to the
Worksheet_Activateevent:
Private Sub Worksheet_Activate() Set wordApp = New Word.Application wordApp.Visible = True Set colELastVal = Nothing Set doc2 = Nothing End SubThis code creates a new instance of the
Word.Applicationobject, sets it to visible, and initializes thecolELastValanddoc2variables toNothing.- Add the following code to the
Worksheet_SelectionChangeevent:
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 5 Then If colELastVal <> Target.Value Then On Error Resume Next Set doc2 = wordApp.Documents("doc2.docx") On Error GoTo 0 If doc2 Is Nothing Then Set doc2 = wordApp.Documents.Open("path\to\doc2.docx") End If colELastVal = Target.Value End If End If End SubThis code checks if the selected cell is in column E. If it is, and the value of the cell has changed since the last time the event was triggered, it attempts to retrieve an already open instance of
doc2, and opens it if it is not found. Finally, it setscolELastValto the current value of the selected cell.- Add the following code to the
Worksheet_Changeevent:
Private Sub Worksheet_Change(ByVal Target As Range) If Target.Column = 5 Then Dim rowValues As Range Set rowValues = Range("B" & Target.Row & ":L" & Target.Row) If Not Intersect(Target, rowValues) Is Nothing Then Dim mValues As String Dim i As Long For i = 1 To 10 mValues = mValues & rowValues(1, i).Value & " " Next i Range("M" & Target.Row).Value = mValues End If End If End SubThis code checks if the changed cell is in column E. If it is, and it is within the range of columns B to L in the same row, it extracts the values of the 10 cells in that row and concatenates them into a string
mValues. It then writesmValuesto the corresponding cell in column M.- Finally, add the following code to the
Worksheet_Deactivateevent to closedoc2:
Private Sub Worksheet_Deactivate() On Error Resume Next doc2.Close False On Error GoTo 0 Set doc2 = Nothing End SubThis code attempts to close
doc2when the worksheet loses focus. Note that it usesOn Error Resume Nextto handle the case wheredoc2is not open, and releases the object usingSet doc2 = Nothing.You will need to adjust the paths to the Word documents in the code, and make sure that each worksheet has its own copy of
doc2(or any other Word document that you want to open). - User
The code that runs every time I select or enter a value in a cell in Column E does not work. I want the vba code to copy 10 values of 10 cells in the same row as the cell in column E I selected (B,C,D,E,F,G,H,I,J,L) and appended their values to column M in the following order M1 to M10. The values changing for every cell I select in column E and the values cleared when I also leave the sheet. Here is the code: Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then Dim rowValues As Range Set rowValues = Range("B" & Target.Row & ":L" & Target.Row) If Not Intersect(Target, rowValues) Is Nothing Then Dim mValues As String Dim i As Long For i = 1 To 10 mValues = mValues & rowValues(1, i).Value & " " Next i Range("M" & Target.Row).Value = mValues End If End If
End Sub
- Assistant
The code you provided for copying the row data to column M when a cell in column E is changed looks correct. Here are a few things you can check to make sure it works:
-
Make sure that you have placed the code in the correct worksheet module. It should be in the worksheet module for the sheet where you want this functionality.
-
Make sure that macros are enabled in your Excel file. If macros are disabled, the code will not execute.
-
Make sure that you are changing the value of a cell in column E, and not any other column, when you test the code. The
Target.Column = 5condition is what restricts the code to cells in column E. -
Make sure that there is no other code in the worksheet module that interferes with this code. If you have other
Worksheet_Chnageevents, for example, that change the same cells, it may affect how this code works.
If you have checked these things and the code is still not working, try adding some
Debug.Printstatements to the code to see if it is executing at all. For example, you can add the following line at the beginning of the code:Debug.Print "Worksheet_Change event was triggered"Then, in the Immediate Window (View > Immediate Window in the VBA Editor), you can see if this message is printed when you change a cell in column E.
You can also add
Debug.Printstatements inside theForloop to see if the row values are being concatenated correctly. For example:For i = 1 To 10 mValues = mValues & rowValues(1, i).Value & " " Debug.Print "mValues after appending value " & i & ": " & mValues Next iThis way, you can see if the
mValuesvariable contains the expected values.I hope this helps you debug the issue. Let me know if you have any other questions or concerns!
-
- User
still not working
- Assistant
I'm sorry to hear that. Here are a few more things you can check:
-
Make sure that you are changing the value of a cell in the same sheet where the code is running. If you change the value of a cell in a different sheet, the code in this sheet will not execute.
-
Make sure that you have not accidentally turned off events processing in Excel. This can happen if you run code that turns off events and does not turn them back on. You can check if events processing is on by going to the VBA Immediate Window and typing
Application.EnableEvents. If the result isTrue, events processing is on. If the result isFalse, typeApplication.EnableEvents = Trueto turn events processing back on. -
Make sure that the code is running at all by adding a message box to the
Worksheet_Changeevent code:
If Target.Column = 5 Then Dim rowValues As Range Set rowValues = Range("B" & Target.Row & ":L" & Target.Row) If Not Intersect(Target, rowValues) Is Nothing Then Dim mValues As String Dim i As Long For i = 1 To 10 mValues = mValues & rowValues(1, i).Value & " " Next i Range("M" & Target.Row).Value = mValues MsgBox "Code ran!" End If End IfIf this message box appears when you change a cell in column E, it means that the code is running.
- Try replacing the Range, Rows, and Columns functions with the fully qualified reference to the sheet where the code is running. For example, instead of
Range("B" & Target.Row & ":L" & Target.Row), useThisWorkbook.Sheets(1).Range("B" & Target.Row & ":L" & Target.Row). This ensures that the code is executing on the correct worksheet even if it is not the active sheet.
If none of these steps help resolve the issue, please provide more information on the behavior you are observing, and any error messages you are seeing.
-
- User
it worked but it put he values of B,C, D,E,F,G,H,L into the same row starting at m. I want it to put the values into M1, M2,M3, M4 up to M10
- Assistant
To write the values to M1 to M10 instead of M in the same row, you can modify the loop in the
Worksheet_Changeevent as follows:Dim mValues As Variant mValues = Application.Transpose(rowValues) For i = 1 To 10 Range("M" & i).Value = mValues(i) Next iThis code uses the
Transposefunction to convert the range into a 2D array where each column represents one of the values. Then, it iterates over the first 10 values in the first row of themValuesarray, and writes them to cells M1 to M10.Make sure to remove or comment out the previous code that writes the concatenated string to a single cell in column M.
I hope this solves the issue! Let me know if you have any other questions.
- User
I am getting an error on if TaskRequest is nothing Then : Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column = 5 Then If colELastVal <> Target.Value Then On Error Resume Next Set TaskRequest = wordApp.Documents("TaskRequest.docm") On Error GoTo 0 If TaskRequest Is Nothing Then Set TaskRequest = wordApp.Documents.Open("G:\Shared drives\Swan School Site Premises\PREMISES MANAGEMENT\SERVICE PROVIDERS\TaskRequest.docm") End If colELastVal = Target.Value End If End If
End Sub
- Assistant
It seems like the
TaskRequestvariable is not defined in your code. Please add the following line to the beginning of your code module to define it:Dim TaskRequest As Word.DocumentThis will declare
TaskRequestas a variable that can hold a reference to a Word document. Make sure it is at the top of the module, before any other procedures or functions.Also, make sure that you have a reference to the Microsoft Word Object Library in your Excel project. To check this, go to the VBA Editor, click on Tools > References, and see if Microsoft Word is checked in the list.
Let me know if this helps, or if you have any other questions or issues.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.