In Excel VBA I have created a global variable; Public xxxxActive As Boolean and placed…
- User
In Excel VBA I have created a global variable; Public xxxxActive As Boolean and placed it in a Module of its own.
In sheet1 just before I activate another sheet, i change the global variable;
xxxxActive = True Sheet2.Select
In sheet2 at the start of a VBA routine and within the sub i use; Sub Start of Routine() If Not xxxxActive Then Exit Sub to check if the module is True. If the module is True it allows the rest of the code to run, if false it exits the sub.
In sheet2 in my Worksheet_Deactivate routine, I use xxxxActive = False to change the global routine to False. What I have found is that if I go back to sheet1, the statement xxxxActive = True only works if it is the Private Sub Worksheet_Activate() routine. If in sheet1 I place the statement xxxxActive = True in another sub routine, it does not change the xxxxActive back to True. I was hoping to only be able to change the xxxxActive to True by the events of a VBA within sheet1. What might be the cause of this.
- Assistant
The reason why the statement xxxxActive = True only works in the Private Sub Worksheet_Activate() routine is because this is the only event that fires when you switch between sheets. If you want to change the global variable xxxxActive back to True in another sub routine, you need to make sure that this sub routine is called whenever you want to activate sheet1.
One way to do this is to create a separate sub routine in sheet1 that sets xxxxActive to True and then calls the sub routine that activates sheet2. For example:
Public Sub ActivateSheet2() xxxxActive = True Sheet2.Select End Sub
Now, whenever you want to activate sheet2 from sheet1, you should call the ActivateSheet2 sub routine instead of directly selecting sheet2. This will ensure that xxxxActive is set to True before switching to sheet2.
Alternatively, if you want to keep the code that activates sheet2 in a different sub routine and still be able to set xxxxActive to True from another sub routine in sheet1, you can pass the value of xxxxActive as an argument to the sub routine that activates sheet2. For example:
Public Sub ActivateSheet2(ByRef isActive As Boolean) If Not isActive Then Exit Sub Sheet2.Select End Sub
Public Sub SetActiveToTrue() xxxxActive = True End Sub
Now, in your other sub routines in sheet1, you can call the SetActiveToTrue sub routine to change xxxxActive to True, and then call the ActivateSheet2 sub routine with xxxxActive passed as an argument. For example:
Sub SomeOtherSubRoutine() SetActiveToTrue ActivateSheet2 xxxxActive End Sub
This will ensure that xxxxActive is set to True before calling the sub routine that activates sheet2.
- User
This code is supposed to take the values of the last row from B to L and put them in Column M from 1 to 11. Unfortunately it is not accurate. Can you provide a fix: Sub ColumnList(Target As Range)
Dim rowValues As Range Set rowValues = Range("B" & Target.Row & ":L" & Target.Row) If Not Intersect(Target, rowValues) Is Nothing Then Dim mValues As Variant mValues = Application.Transpose(Range("B" & Target.Row & ":L" & Target.Row)) Dim i As Integer For i = 1 To UBound(mValues, 1) Range("M" & i).Value = mValues(i, 1) Next i End If
End Sub
- Assistant
The issue with the code is that it assumes that the values will always be in the last row of the sheet, but it does not specify which row to start from. To fix this, you can modify the code to specify the row to start from. Here's an updated code with comments:
Sub ColumnList(Target As Range) Dim rowValues As Range ' Get the last row in column B Dim lastRow As Long lastRow = Cells(Rows.Count, "B").End(xlUp).Row ' Check if the changed cell is within the range of B to L in the last row Set rowValues = Range("B" & lastRow & ":L" & lastRow) If Not Intersect(Target, rowValues) Is Nothing Then ' Get the values in B to L as a transposed array Dim mValues As Variant mValues = Application.Transpose(Range("B" & lastRow & ":L" & lastRow)) ' Loop through the array and put the values in column M Dim i As Long ' Use Long instead of Integer for compatibility with big sheets For i = 1 To UBound(mValues, 1) Range("M" & i).Value = mValues(i, 1) Next i End If End SubThis code uses the
Cellsmethod to find the last row in column B, and then uses that row to specify the range of values to get and put in column M. This ensures that the code operates on the correct row even if there are empty rows in the sheet.I also changed the variable
itoLonginstead ofIntegerfor compatibility with large sheets, and added some comments to explain the code. - User
if some of the cells in the row are blank, not B it always has a value, will it affect the code
- Assistant
If some of the cells in the row are blank, it may affect the code in two ways:
-
The
lastRowvariable may be incorrect if there are blank cells below the last row with data in column B. This is becauseCells(Rows.Count, "B").End(xlUp).Rowfinds the last non-blank cell in column B, and if there are blank cells below that cell, it will not include those rows in the range. -
The
mValuesarray may contain empty cells when transposing the range. This means that if there are blank cells in the range, the corresponding cells in column M will also be blank.
To handle these cases, you can modify the code to exclude the blank cells from the row and only transpose the non-blank cells. Here's an updated code that handles both cases:
Sub ColumnList(Target As Range) ' Get the last row in column B that has data Dim lastRow As Long lastRow = Cells(Rows.Count, "B").End(xlUp).Row ' Get the range of values to transpose (exclude blank cells) Dim rowValues As Range Set rowValues = Range("B" & lastRow & ":L" & lastRow).SpecialCells(xlCellTypeConstants) ' Check if the changed cell is within the range of B to L in the last non-blank row If Not Intersect(Target, rowValues) Is Nothing Then ' Get the non-blank values in B to L as a transposed array Dim mValues As Variant mValues = Application.Transpose(rowValues) ' Loop through the array and put the values in column M Dim i As Long For i = 1 To UBound(mValues, 1) Range("M" & i).Value = mValues(i, 1) Next i End If End SubThis code uses the
SpecialCells(xlCellTypeConstants)method to exclude the blank cells from the range when getting therowValues, and then transposes only the non-blank cells. This ensures that the corresponding cells in column M will not be blank if there are blank cells in the range. -
- User
How then can I call this code from the WorkSheet Activate routine. Sub ColumnList(Target As Range)
Dim rowValues As Range ' Get the last row in column B Dim lastRow As Long lastRow = Cells(Rows.Count, "B").End(xlUp).Row ' Check if the changed cell is within the range of B to L in the last row Set rowValues = Range("B" & lastRow & ":L" & lastRow) If Not Intersect(Target, rowValues) Is Nothing Then ' Get the values in B to L as a transposed array Dim mValues As Variant mValues = Application.Transpose(Range("B" & lastRow & ":L" & lastRow)) ' Loop through the array and put the values in column M Dim i As Long ' Use Long instead of Integer for compatibility with big sheets For i = 1 To UBound(mValues, 1) Range("M" & i).Value = mValues(i, 1) Next i End If
End Sub
- Assistant
To call the
ColumnListsub from the Worksheet Activate routine, you can add the following line of code to the Worksheet_Activate sub:Private Sub Worksheet_Activate() ColumnList Me.Range("B1") ' Call ColumnList with a cell in column B End SubThis code uses
Me.Range("B1")to pass a cell in column B as theTargetparameter to theColumnListsub. This will trigger the code to get the values from the last row in column B and put them in column M when the worksheet is activated.You may also need to change the name of the worksheet in the code from "Sheet1" to the name of your worksheet. For example:
Private Sub Worksheet_Activate() ColumnList Me.Range("B1") ' Call ColumnList with a cell in column B End Sub - User
I got the code to work as required. It does take a couple of seconds bu this is Ok. However, it does not Call TaskRequest
Sub ColumnList(Target As Range) Dim rowValues As Range Dim lastRow As Long lastRow = Cells(Rows.Count, "B").End(xlUp).Row Set rowValues = Range("B" & lastRow & ":L" & lastRow) Dim mValues As Variant mValues = Application.Transpose(Range("B" & lastRow & ":L" & lastRow)) Dim i As Long ' Use Long instead of Integer for compatibility with big sheets For i = 1 To UBound(mValues, 1) Range("M" & i).Value = mValues(i, 1) Next i
Call TaskRequest
End Sub
- Assistant
It is possible that the
TaskRequestsub is not being called because there is an error in the sub or it is not located in a module that is available to the current worksheet. You can try adding a message box before and after theCall TaskRequestline to see if the code is reaching that point:Sub ColumnList(Target As Range) Dim rowValues As Range Dim lastRow As Long lastRow = Cells(Rows.Count, "B").End(xlUp).Row Set rowValues = Range("B" & lastRow & ":L" & lastRow) Dim mValues As Variant mValues = Application.Transpose(Range("B" & lastRow & ":L" & lastRow)) Dim i As Long ' Use Long instead of Integer for compatibility with big sheets For i = 1 To UBound(mValues, 1) Range("M" & i).Value = mValues(i, 1) Next i MsgBox "Before TaskRequest" ' Add this line to check if the code is reaching this point Call TaskRequest MsgBox "After TaskRequest" ' Add this line to check if the code is reaching this point End SubIf the message boxes do not appear when you expect them to, this could indicate that there is an error in the sub or that it is not being called. You can also try stepping through the code using the VBA debugger to see where it might be going wrong.
- User
After this code has run, what is the correct code to select the last cell in column B
- Assistant
To select the last cell in column B after the
ColumnListsub has run, you can use the following line of code:Range("B" & Cells(Rows.Count, "B").End(xlUp).Row).SelectThis code uses
Cells(Rows.Count, "B").End(xlUp)to find the last non-blank cell in column B, and thenRowto get the row number of that cell. It then uses this row number to construct a cell reference to the last cell in column B, and callsSelecton that range to select the cell.Note that if there are no non-blank cells in column B, this code will select cell B1. If you want to handle this case differently, you can add a check for an empty column B before selecting the last cell, like this:
Dim lastB As Range Set lastB = Cells(Rows.Count, "B").End(xlUp) If Not IsEmpty(lastB) Then lastB.Select End IfThis code checks if the last non-blank cell in column B is empty using
IsEmpty, and only selects the cell if it is not empty. This ensures that the code does not select cell B1 if there are no non-blank cells in column B. - User
the following commands are not closing word; wordApp.Quit
Set wordApp = Nothing
- Assistant
If
wordApp.Quitis not closing Word, there may be a couple of reasons why this is happening. Here are some things you can try:-
Make sure all Word objects are closed: Before calling
wordApp.Quit, make sure all Word documents and windows are saved and closed. Word may not close if there are unsaved changes or if there are open dialogs that require user input. -
Use
Application.Quitinstead: Instead of usingwordApp.Quit, you can try usingApplication.QuitorExit Subto close Word. This will close all instances of Word, including any hidden or background instances that may not be referenced bywordApp.
Here's an updated version of the code that uses
Application.Quit:Sub OpenWordDoc() ' Declare Word objects Dim wordApp As Word.Application Dim wordDoc As Word.Document ' Create new instance of Word Set wordApp = New Word.Application ' Open Word document Set wordDoc = wordApp.Documents.Open("C:\Path\To\Your\Document.docx") ' Do some stuff with the document... ' Save and close the document wordDoc.Close SaveChanges:=True ' Quit Word and release objects wordApp.Quit Set wordDoc = Nothing Set wordApp = Nothing End SubIf Word still does not close after trying these solutions, there may be other factors at play, such as security settings or other running applications that are preventing Word from closing. In this case, you may need to investigate further or seek additional assistance.
-
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.