Active Cell Address – Inactive Sheet

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
167
Office Version
  1. 2013
Platform
  1. 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.
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
12,926
Office Version
  1. 365
Platform
  1. 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
167
Office Version
  1. 2013
Platform
  1. Windows
Thanks jasonb75. I use Excel 2013. Will give this a shot.
 

MrDB4Excel

Board Regular
Joined
Jan 29, 2004
Messages
167
Office Version
  1. 2013
Platform
  1. 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,977
Office Version
  1. 365
Platform
  1. 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,977
Office Version
  1. 365
Platform
  1. 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
167
Office Version
  1. 2013
Platform
  1. 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,977
Office Version
  1. 365
Platform
  1. 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
 

Watch MrExcel Video

Forum statistics

Threads
1,132,874
Messages
5,655,743
Members
418,234
Latest member
jdorfma

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top