Application.StatusBar Cell Address

[bsquad]

I am looking if anyone knows of a LIVE function or sub that would give the Cell Address in the numeric format(I am not sure the technical name for it). But lets say I select A1 - in the status bar it would I would see "Cell(1,1)"


I thought of it when I found something someone had wrote that gives something similar to the COUNT: MIN: MAX:...features already built in

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, _
ByVal Target As Range)
On Error GoTo ErrHandler:
    With Application
    .StatusBar = "Sum: " & Application.Sum(Target) & " | " _
    & "Count(Nums): " & .WorksheetFunction.Count(Target) _
    & " | Count(items): " & Application.CountA(Target) _
    & " | Average: " & Application.Average(Target)
    End With
Exit Sub
ErrHandler:
Application.StatusBar = False
End Sub

 

[JoeMo]

Try this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Application.StatusBar = "Cell(" & Target(1).Row & "," & Target(1).Column & ")"
End Sub

Note: to return the status bar to its normal state: first delete or comment out the code, then in the immediate window (ctrl + g) enter: Application.statusbar = false and press enter.

 

[bsquad]

Thank you for the response Joe,

I am not seeing any changes when I paste this into 'ThisWorkbook' - although do I need to maybe reset my StatusBar somehow?

 

[JoeMo]

Thank you for the response Joe,

I am not seeing any changes when I paste this into 'ThisWorkbook' - although do I need to maybe reset my StatusBar somehow?

Not necessary. Perhaps you have inadvertently disabled events. Try opening the VBE (alt + F11), then the Immediate Window (ctrl + g) and in the Immediate window type Application.EnableEvents = True and press enter. Now the code should fire whenever you select a new cell.

 

[bsquad]

you know what it was - it actually has to be save under the Sheet actual and not ThisWorkbook or a module; but that works perfectly fine for me. Much easier then trying to memorize the alphabet numerically. It will definitely speed up my code writing and accuracy since I would still consider myself a vba FNG

Thanks for this

 

[TonyUK72]

Not sure if it's any help:

If you right click the ribbon and select customise, the select formulas, then check the box for R1C1 reference style that might be what your looking for. You can always switch it back when done.

 

[bsquad]

Joe,

While I have it working on a new workbook; on the current one I have been working on - it does work, but only for about a second or so. I don't really expect you to have an answer since you haven't seen my code, but just if you had any thoughts. - When I have a new workbook open with your code AND my current workbook, it works perfectly fine, but when only my current workbook is open is when it occurs.


Tony,
that was my backup plan if nothing had worked; the purpose of the post was because I write in both styles and since I haven't gotten to the point where I can memorize both the style fields and having to switch back and forth, I was looking for something in the status bar - like Joe had provided. But the response is appreciated!

 

[JoeMo]

Joe,

While I have it working on a new workbook; on the current one I have been working on - it does work, but only for about a second or so. I don't really expect you to have an answer since you haven't seen my code, but just if you had any thoughts. - When I have a new workbook open with your code AND my current workbook, it works perfectly fine, but when only my current workbook is open is when it occurs.

If I understand correctly, when you select a cell you see Cell(x,y) for about a second and it vanishes even though you do not select another cell. When Cell(x,y) vanishes, what do you see on the status bar?

 

[bsquad]

yes that is correct. It shows - READY NUM LOCK. It also shows a little table like icon just to the right of NUM LOCK

 

[JoeMo]

yes that is correct. It shows - READY NUM LOCK. It also shows a little table like icon just to the right of NUM LOCK

Can you clear the status bar at that point using Application.StatusBar = False?