Skip to content
TheChatArchiveThe Chat Archive
gpt-3.5-turbo-0301· OpenAIAi2 WildChat-4.8M (AI2)

Public activeCellAddress As String 'Private Sub WorksheetActivate() Private Sub…

  1. User

    Public activeCellAddress As String 'Private Sub Worksheet_Activate() Private Sub Worksheet_Deactivate() activeCellAddress = activeCell.Address End Sub

    Function getCompanyName() As String Dim providersSheet As Worksheet Set providersSheet = ThisWorkbook.Sheets("Providers") Dim activeRow As Range Set activeRow = providersSheet.Range(activeCellAddress).EntireRow getCompanyName = activeRow.Cells(1, 3).Value End Function

    Private Sub Worksheet_Open(ByVal Target As Range) Application.CalculateFullRebuild Call populateCompanyData End Sub

    Sub populateCompanyData() Dim companySheet As Worksheet Set companySheet = ActiveWorkbook.ActiveSheet Dim lastRow As Long lastRow = companySheet.Cells(Rows.Count, 1).End(xlUp).Row Dim emptyRow As Long emptyRow = lastRow + 1 Dim activeCellAddress As String 'define variable here activeCellAddress = Application.Caller.Address 'retrieve address of calling cell companySheet.Cells(emptyRow, 1).Value = getCompanyName() companySheet.Cells(emptyRow, 4).Value = ThisWorkbook.Sheets("Providers").Range(activeCellAddress).Offset(0, 1).Value End Sub

  2. Assistant

    As an AI language model, I cannot provide you with an opinion, but I can tell you that this code is used to populate data from a "Providers" sheet to another sheet called "companySheet" based on the active cell in the providers sheet. It retrieves the active cell's address, gets the company name from the active row on the providers sheet, and then populates the data on the company sheet.

  3. User

    What I want to know is where this code Private Sub Worksheet_Deactivate() should be located, where the Function getCompanyName() As String should be located and where the Sub populateCompanyData() should be located. They are all currently in a sheet called ADH (not companySheet) and the code is not working

  4. Assistant

    The code Private Sub Worksheet_Deactivate() should be located within the sheet module where you want to track which cell was last active before moving to another sheet.

    The Function getCompanyName() As String should also be located within the same sheet module where you want to retrieve the value of the third column (company name) of the active row on the "Providers" sheet.

    The Sub populateCompanyData() should be located within the same module as well, where you want to populate data onto the "companySheet" based on the active cell in the "Providers" sheet.

    Make sure that the code is correctly entered into the sheet module for the "ADH" sheet and that the sheet names are correctly specified. Also, make sure that the "ADH" sheet is the active sheet when you run the code.