Agahdariya bikêr di barika statûyê de

Someone like, but I personally need a status bar only in 2-3 cases:

  • after filtering, it displays the number of values ​​remaining after the selection
  • when a range is selected, it displays the sum, average, and number of selected cells
  • in the case of heavy files, you can see the progress in recalculating formulas in the book.

Not so much for a line that takes up almost the entire width of the screen and hangs on it all the time. Let’s try to expand this modest list and add a few more useful features to it 🙂

General principles for managing the status bar

Managing the status bar with Visual Basic is very easy. To display your text in it, you can use a simple macro:

Sub MyStatus() Application.StatusBar = "Привет!" End Sub

After running it, we get:

Agahdariya bikêr di barika statûyê de

To restore the original state of the status bar, you will need the same short “anti-macro”:

Sub MyStatus_Off() Application.StatusBar = False End Sub

In the basic version, as you can see, everything is very simple. Now let’s try to develop the idea…

Address of the selected range in the status bar

 In the upper left corner of the Excel window in the formula bar, you can always see the address of the current cell. But if a whole range is selected, then, unfortunately, we will not see the selection address there – the same one single active cell is displayed:

Agahdariya bikêr di barika statûyê de

To solve this problem, you can use a simple macro that will display the address of the selected area in the status bar. Moreover, this macro should be launched automatically, with any change in the selection on any sheet – for this we will place it in the event handler SelectionChange our book.

Open the Visual Basic Editor using the button of the same name on the tab pêşvebirinê (pêşdebir) an jî kurteyên klavyeyê Left Alt+F11. Find your book in the upper left corner of the Project panel and open the module in it by double-clicking Ev pirtûka (Ev Pirtûka Xebatê):

In the window that opens, copy and paste the following macro code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)      Application.StatusBar = "Выделено: " & Selection.Address(0, 0)  End Sub

Now, when any range is selected (including more than one!), its address will be displayed in the status bar:

Agahdariya bikêr di barika statûyê de

To prevent the addresses of several ranges selected with Ctrl from merging, you can add a small improvement – use the Replace function to replace a comma with a comma with a space:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)      Application.StatusBar = "Выделено: " & Replace(Selection.Address(0, 0), ",", ", ")  End Sub

Number of selected cells in the status bar

When any range is selected, the number of non-empty selected cells is displayed on the right side of the status bar by default. Sometimes you need to know the number of allocated. This task can also be accomplished with a simple macro to handle the SelectionChange book event, as in the previous example. You will need a macro like:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range) Dim CellCount As Variant, rng As Range For Each rng In Selection.Areas 'Iterate through all selections RowsCount = rng.Rows.Count 'number of rows ColumnsCount = rng.Columns. Count 'number of columns CellCount = CellCount + RowsCount * ColumnsCount 'accumulate the total number of cells Next 'display in the status bar Application.StatusBar = "Selected: " & CellCount & " cells" End Sub

This macro loops through all Ctrl-selected areas (if there are more than one), stores the number of rows and columns in each area in the RowsCount and ColumnsCount variables, and accumulates the number of cells in the CellCount variable, which is then displayed in the status bar. At work it will look like this:

Agahdariya bikêr di barika statûyê de

Of course, you can combine this and the previous macros to display both the address of the selected range and the number of cells at the same time. You only need to change one penultimate line to:

Application.StatusBar = "Selected: " & Replace(Selection.Address(0, 0), ",", ", ") & " - total " & CellCount & " cells"

Then the picture will be quite wonderful:

Agahdariya bikêr di barika statûyê de

Well, I think you get the idea. Suggest in the comments – what else would be useful to display in the status bar?

  • What are macros, how they work, how to use and create them
  • Convenient coordinate selection on an Excel sheet
  • How to make complex formulas more visual

Leave a Reply