Active Cell Address – Inactive Sheet

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
145
Office Version
2013
Platform
Windows
How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current, active sheet.
This is done to always show the user if a specific cell is actually the active cell in a specific inactive worksheet.
In addition, to make this seem a bit more complicated, my workbook contains 6 sheets, labeled as follows:
Current Rates, USD2CZK Log, EUR2CZK Log, CZK2EUR Log, RUB2EUR Log, EUR2RUB Log.
I want to use VBA to get the cell address of the active cell in each of the sheets USD2CZK Log, EUR2CZK Log, CZK2EUR Log, RUB2EUR Log, EUR2RUB Log and place those cell references, 5 in all, on sheet Current Rates. (Incidentally, these active cell addresses are empty, no data is present)
In addition, the indicated cell addresses of the active cells in those 5 sheets are directly below, in the same column of those 5 sheets, the last entered data. So for example VBA’s results indicate the active cell in each of those 5 sheets is B678. I need to also have VBA return the cell address of the cell above the active cell wherein the last data was entered, in this example, B677.
I assume the VBA involved would need an entry for each of the 5 sheets.
I hope this is an easy fix, any help is greatly appreciated. I have scoured several web pages to find an answer and do not seem to find anything that remotely applies, close, but no cigar.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,629
Office Version
2019
Platform
Windows
How to use VBA to get the cell address of the active cell in an inactive worksheet
If the worksheet is not active then the cell is not active, to the best of my (limited) knowledge, activecell and selection only work on the active worksheet, although for some reason you can still refer to the activesheet of an inactive workbook o_O
I need to also have VBA return the cell address of the cell above the active cell wherein the last data was entered, in this example, B677.
If it is the last entry then is it fair to assume that B678:B1048576 would be empty? If so then you can use the standard method of finding the last row, e.g

VBA Code:
Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Address
If data can be entered into the middle of a table, then it could be a bit more challenging, you could perhaps use selection change to store the cell addresses of the last selected cell as a pubic variable, but that would only work if each was changed at least once.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
145
Office Version
2013
Platform
Windows
Thanks jasonb75. I use Excel 2013. Will give this a shot.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
145
Office Version
2013
Platform
Windows
If it is the last entry then is it fair to assume that B678:B1048576 would be empty?

VBA Code:
Worksheets("Sheet1").Cells(Rows.Count, "B").End(xlUp).Address

Works, thanks. I came up with a different method to get the other parts of my post.
If you are interested let me know and I can post the code and other pertinent info.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,549
Office Version
365
Platform
Windows

ADVERTISEMENT

One way

VBA below writes the cell address of the active cell to A1 in active sheet whenever user selects a cell
Refer to that cell in any formula in sheet Current Rates like this
='USD2CZK Log'!A1

Test in a copy of your workbook
Paste the code below into ThisWorkbook code window
It will not work in a Module like Module1
It will not work in a sheet's code window
Test by selecting different cells in the 5 sheets

VBA Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    If Sh.Name = "Current Rates" Then Exit Sub
    Sh.Range("A1") = ActiveCell.Address(0, 0)
End Sub
 
Last edited:

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,549
Office Version
365
Platform
Windows

ADVERTISEMENT

An alternative would be to write the 5 cell references directly to 5 cells in sheet "Current Rates", something like this ...

VBA Code:
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    Dim addr As String:     addr = ActiveCell.Address(0, 0)
    With Sheets("Current Rates")
        Select Case Sh.Name
            Case "USD2CZK Log":     .Range("D10") = addr
            Case "EUR2CZK Log":     .Range("E10") = addr
            Case "CZK2EUR Log":     .Range("F10") = addr
            Case "RUB2EUR Log":     .Range("G10") = addr
            Case "EUR2RUB Log":     .Range("H10") = addr
        End Select
    End With
End Sub
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
145
Office Version
2013
Platform
Windows
Thanks again Yongle. I will test this to see how it works. But do I add this to a module or to the current rates worksheet?
It seems appropriate to add it to the worksheet.
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,549
Office Version
365
Platform
Windows
Thanks @jasonb75

It seems appropriate to add it to the worksheet
Quite the opposite.
- The macro works across the whole workbook and is a replacement for the one provided in my previous post. Apologies for not making that clear :oops:

EXPLANATION
the macro is triggered by selection change in the active sheet
VBA is asking ...
Which cell is active?
Which sheet is active?
If it is USD2CZK Log then write value to D10 in sheet Current Rates etc
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,186
Messages
5,509,691
Members
408,749
Latest member
Bhuvaneshvar kashyap

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top