Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· openaiHugging Face Suzhen/CodeChat

In Excel VBA I have created a global variable; Public xxxxActive As Boolean and placed…

  1. 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.

  2. 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.

  3. 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

  4. 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 Sub
    

    This code uses the Cells method 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 i to Long instead of Integer for compatibility with large sheets, and added some comments to explain the code.

  5. User

    if some of the cells in the row are blank, not B it always has a value, will it affect the code

  6. Assistant

    If some of the cells in the row are blank, it may affect the code in two ways:

    1. The lastRow variable may be incorrect if there are blank cells below the last row with data in column B. This is because Cells(Rows.Count, "B").End(xlUp).Row finds 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.

    2. The mValues array 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 Sub
    

    This code uses the SpecialCells(xlCellTypeConstants) method to exclude the blank cells from the range when getting the rowValues, 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.

  7. 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

  8. Assistant

    To call the ColumnList sub 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 Sub
    

    This code uses Me.Range("B1") to pass a cell in column B as the Target parameter to the ColumnList sub. 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
    
  9. 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

  10. Assistant

    It is possible that the TaskRequest sub 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 the Call TaskRequest line 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 Sub
    

    If 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.

  11. User

    After this code has run, what is the correct code to select the last cell in column B

  12. Assistant

    To select the last cell in column B after the ColumnList sub has run, you can use the following line of code:

    Range("B" & Cells(Rows.Count, "B").End(xlUp).Row).Select
    

    This code uses Cells(Rows.Count, "B").End(xlUp) to find the last non-blank cell in column B, and then Row to 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 calls Select on 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 If
    

    This 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.

  13. User

    the following commands are not closing word; wordApp.Quit

    Set wordApp = Nothing

  14. Assistant

    If wordApp.Quit is not closing Word, there may be a couple of reasons why this is happening. Here are some things you can try:

    1. 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.

    2. Use Application.Quit instead: Instead of using wordApp.Quit, you can try using Application.Quit or Exit Sub to close Word. This will close all instances of Word, including any hidden or background instances that may not be referenced by wordApp.

    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 Sub
    

    If 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.

Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.