Set Focus to Cell

Plugins
1

Hello everybody,

I’m currently working on a set of macos for worksheets. I set formulars to cells etc. But funnily I’m stuck with a quite simple problem (so it seems). After processing several cells, I’d like to set the focus (activate) a particular cell.

I tried something like:

var oWorksheet = Api.GetActiveSheet();
oWorksheet.GetRange("B1:B1");

But that does not set the focus to the specified cell.

Can anybody help?

2

Hello @Dimpflmoser

Do you mean you’d like to select a cell or a range of cells? If yes, then for this Select method exists.

3

Hello Constantine,

I have already tried the select method - but nothing happens …

What my macro does is:
I first select cells from a row via GetRange and insert formulars to the cells. I then format the cells. So to the user it appears like a new input-row has been added, in which he can insert data which will be processed. What I’d like to achieve is that a certain cell in this row is activated - so the user can just start typing data into that cell. Let’s call the cell B2. But after processing the cells I use the select-method like follows (where intZeile represents the row number) the activated cell remains A2:

objRange = objTabbelle.GetRange("B" + intZeile);
objRange.Select();

Thanks again

4

You can use a separate object exclusively for the cell you want to select. For example:

// Initial process starts
var oWorksheet = Api.GetActiveSheet();
var oRange = oWorksheet.GetRange("A1:D1");
oRange.SetValue("Processing");
// Process has ended

// Selecting a separate cell 
var cellSelect = oWorksheet.GetRange("B2");
cellSelect.Select();

In this case after the data has been inserted according to your scenario, you can select exact cell with Select method. Isn’t that you are trying to achieve?

5

Hello, I’m also having an issue with focusing.
I have a large table where a row contains a period with dates spanning 3 months (e.g., 01/01/25, 02/01/25, and so on).

I want to write a macro that finds the current date, highlights the cell with that date, and scrolls (focuses) the table so I can see it.

Currently, I’m using Select(). The cell gets highlighted, but it’s outside the visible area of the window, so I can’t see where it is.

6

Hello @lusy

Do I understand correctly that in your case there is only one cell is returned? I mean the scenario where you are performing a search which supposedly should return a cell or several cells.

7

Hello @Constantine
The search should return only one cell, in my case, the one containing the current date.

I am using code that searches for the current date within a specified range.
Then, I use Select() to highlight the found cell.
The cell gets highlighted, but it is located outside my visible area, and the focus (scrolling) does not happen automatically.
As a result, I have to manually scroll and look for the highlighted cell on the right.

(function HighlightTodayCel() {
    try {
        console.log("Запуск макроса");
        var oWorksheet = Api.GetActiveSheet(); //записываем в переменную текущий лист  
    
        //Переводим текущую дату в формат 01.янв
        var today = new Date();
        // Создаем массив с русскими сокращенными названиями месяцев
        var months = ["янв", "фев", "мар", "апр", "май", "июн", "июл", "авг", "сен", "окт", "ноя", "дек"];
        // Получаем день и месяц
        var day = today.getDate();
        var month = months[today.getMonth()]; // Получаем название месяца
        // Форматируем дату в формат "01.янв"
        var formattedDate = day + "." + month;
    
        console.log("Отформатированная дата:", formattedDate); // Вывод в консоль
    
        var oRange = oWorksheet.GetRange("C2:LQ2"); //Диапазон поиска
        var oSearchData = {
            What: formattedDate, //что ищем, переменнная текущей даты
            After: oWorksheet.GetRange("C2"), //Ячейка, после которой вы хотите начать поиск. Если этот аргумент не указан, поиск начинается после ячейки в верхнем левом углу диапазона.
            LookIn: "xlValues", //Тип данных для поиска (формулы или значения).
            LookAt: "xlWhole", //Указывает, будет ли соответствовать весь текст поиска или любая его часть.
            SearchOrder: "xlByRows", //Порядок поиска по диапазону — по строкам или по столбцам.
            SearchDirection: "xlNext", //Направление поиска диапазона - следующее или предыдущее совпадение.
            MatchCase: true //Учитывать регистр или нет. Значение по умолчанию - "false".
        };
        var oSearchRange = oRange.Find(oSearchData);
        console.log("Результат oSearchRange", oSearchRange);
        if (oSearchRange) {
            console.log("Дата найдена в ячейке:", oSearchRange.GetAddress());
    
            // установить фокус
            oSearchRange.Select();   // Затем выделяем её
        } else {
            console.log("Дата не найдена.");
        }
        //---------------
    } catch (e) {
            console.error("Ошибка выполнения макроса:", e);
        }
})();
8

Thanks for the sample, I see what the problem is. I’ll check out possible solutions, please await for my feedback.

10

Hello again @lusy

Unfortunately, there is no way to set a focus to specific cell. Based on that, we have registered an enhancement suggestion to make Select also focus on the cell. I will let you know once it is implemented.

Sorry for the inconvenience.