Active Cell Address – Inactive Sheet

MrDB4Excel

Active Member
Joined
Jan 29, 2004
Messages
327
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

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
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.
 
Upvote 0
Thanks jasonb75. I use Excel 2013. Will give this a shot.
 
Upvote 0
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.
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,237
Members
448,555
Latest member
RobertJones1986

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
Back
Top