Posts by PatrickOfLondon

Hi people,

Just to bring this episode to a (somewhat unsatisfactory) close: I tried all suggestions, to no avail.

Then suddenly, a couple of weeks or so ago, things started working normally, as you'd expect them to, without any problems of the kind that previously existed. I hesitated to report it in case it was just a temporary thing, but the now-correct operation seems to have remained in place.

Whether this is due to some automatic, bug-fixing, online update of Excel 2021, or Windows 11, or the mouse driver, or all of these, I can't say, but that would be my suspicion at this point.

So I thank you all for your interest in this case, and your advice and assistance. There's simply, now, no fault, any more.

Wishing you all a great New Year.

Pending my further tests, brief answers to recent responses

(and thank you for those):

Rory: I work in Normal view.

Carim (Very eager...): so am I...

Pecoflyer (adding DWORD): yes, and I will be trying this soon.

Hi all,

Thank you all for your further suggestions. I will be experimenting with them in the next few days and will report back.

As an aside, for some reason I don't understand, I am not getting emails to say that someone has posted something new to this thread, even though I am "subscribed" to it, and the "Watch thread" box is ticked. There are no related emails in my spam folder. My account settings on this forum have "Yes" for "Automatically watch threads when I have replied to them". Any suggestions as to why I might not be receiving email notifications for new posts and replies would be welcome -- thank you in anticipation.

Hi Carim et al,

No, it's not a sticky keys thing... I've tried disabling, enabling, disabling, and leaving it disabled. It's not that.

Hi Pecoflyer,

Thank you for your suggestion.

Unfortunately, the DisableHardwareAcceleration option seems to have been removed from the relevant Options area in the Excel 2021 I'm using, so I can't try it. I've also searched the Registry for it and it doesn't seem to be there either.

Thanks for trying to help!

Hi Carim,

1. Many thanks, again, for your willingness to help, and your time and effort in looking at this.

2. Unfortunately, having -- at your suggestion -- tried both repairing and reinstalling Office 2021, using the Microsoft tools you pointed out, the behaviour remains unchanged, with the problem continuing to manifest itself on my installation.

3. Sometimes just a single click on the orange cell in my demonstration workbook (as well as different cells in other workbooks of similar general structure) will cause a selection of several cells to appear; sometimes it will behave as expected and select just the one intended cell. Using the Shift+PageDown method described in the workbook will always produce an "upwards selection" instead of the expected downwards one.

4. I also tried uploading and converting my demonstration file to Google Sheets, and then exporting it from Sheets as a new, Excel format file, in the hope that that might remove any possible file corruption. But after opening that version in Excel, the problem still remained.

5. Thinking that In case it could possibly be a mouse problem, I also tried an entirely different make and type of mouse (cable connected rather than wireless). But that made no difference either: the problem was still evident. Using the touchpad rather than a mouse usually produced a briefly extended selection which would then revert, by itself, to the single target cell.

6. I suppose it is still possible that this might be mouse-driver-related rather than Excel-related, but I can only use the mouse drivers that are right for my system, so I think that question remains open.

7. I think I'll just have to live with it. Using Ctrl+G to go to the intended cell address is a possible, but cumbersome, workaround.

8. It's clear that other people do encounter this problem, as evidenced by the links below.

a: https://www.quora.com/Why-does…150615&target_type=answer

b: https://www.mrexcel.com/board/…ultiple-cells-bug.693868/

c: https://answers.microsoft.com/…73-452f-ac21-6c8bf19afcfc

To conclude: thank you once again, Carim, for your helpfulness.

Patrick

Hi Carim,

Thank you for your suggestion, but that is not the reason, as stated in my original post ("...It has nothing to do with whether Extend mode (F8) or Add/Remove mode (Shift F8) are on or off...").

The bug (try the workbook I attached in my post) presents itself without activating either Extend Selection or Add or Remove Selection modes.

Hello everyone, just wondering if anyone can help...

There seems to be a years-old bug in Excel (many online posts refer to it, in successive versions going back to Excel 2007) whereby Excel selects multiple cells instead of one, when trying to select a new, single, target cell (or, sometimes, range).

From my experiments, the bug seems to be triggered somewhat randomly when the number of rows in a worksheet exceeds what can be displayed in a single screenful, and when Freeze Panes is on. It has nothing to do with whether Extend mode (F8) or Add/Remove mode (Shift F8) are on or off.

I'm attaching a small Excel workbook which I hope will demonstrate to anyone who's interested (Microsoft...????) an example of similar, probably related, and equally unexpected selection-change behaviour. In this workbook I suggest using Shift+PageDown which seems to fairly reliably show the bugginess in changing selection with extensive lists and frozen panes... at least it does on my system*. When the bug occurs it sometimes also seems to disable the operation of Shift+PageUp.

I've found this type of buggy behaviour in Excel 2019 and 2021, and to some extent in Microsoft 365.

The attached workbook was created in Excel 2021 and exhibits the bug in slightly different, but always unexpected, ways when opened and used in each of the above three Excel versions.

Excel single cell click, multiple cell selection - experiments.xlsx

If anyone knows why this happens or knows of a cure for it, that would be very interesting.

*HP laptop, 2020 vintage, Ryzen 5, Windows 11, Microsoft Office 2021

Roy,

Many thanks. I'll explore the Tables concept.

Roy,

Thanks for responding.

I'm not a "professional" programmer, but self-taught, and I occasionally write macros to help friends, charities, etc to automate tasks in Excel. So I'm afraid efficiency of programming is not my strong point, I just write code that I can read and understand if and when I need to go back to it in future.

Function Next_Due_Date(ByRef start_date As Date, ByRef frequency_label As String)

sdate = start_date
 
If sdate >= Date Then
    Next_Due_Date = sdate
    Exit Function
End If

flen = Len(frequency_label)
If flen = 0 Then
    Next_Due_Date = " what repeat frequency?": Exit Function
End If
    

frequency_label_first_space_position = InStr(frequency_label, " ")
fqy = 1 * Left(frequency_label, frequency_label_first_space_position)
' frequency_label comes from a data-validated cell where users pick from a list of prescribed strings like:
' 30 days (use for calendar-monthly repeats)
' 0 days (use for one-off requests)

interval_initial_setting = "unknown"
interval_code_for_DateAdd = interval_initial_setting
' if it's still this after passing through the stages below,
' we don't know what to do, so return an indication of that
' each stage below is listed in order of likelihood of occurrence:
' no point in going through unnecessary stages each time around
    If fqy = 0 Then Next_Due_Date = Space(20) & "--- one-off ---": Exit Function
    If fqy = 30 Then interval_code_for_DateAdd = "m": date_add_qty = 1: GoTo calculate_next_due_date
    If fqy = 7 Then interval_code_for_DateAdd = "d": date_add_qty = 7: GoTo calculate_next_due_date
    If fqy = 60 Then interval_code_for_DateAdd = "m": date_add_qty = 2: GoTo calculate_next_due_date
    If fqy = 90 Then interval_code_for_DateAdd = "m": date_add_qty = 3: GoTo calculate_next_due_date
    If fqy = 14 Then interval_code_for_DateAdd = "d": date_add_qty = fqy: GoTo calculate_next_due_date
    If fqy = 21 Then interval_code_for_DateAdd = "d": date_add_qty = fqy: GoTo calculate_next_due_date
    If fqy = 28 Then interval_code_for_DateAdd = "d": date_add_qty = fqy: GoTo calculate_next_due_date
unknown:
    If interval_code_for_DateAdd = interval_initial_setting Then Next_Due_Date = "???": Exit Function

calculate_next_due_date:
    initdate = sdate
    nextdate = initdate
looper:
    nextdate = DateAdd(interval_code_for_DateAdd, date_add_qty, nextdate)
    
    If nextdate < Date Then GoTo looper


Next_Due_Date = nextdate
End Function

I'm attaching a sample workbook.

Thanks again for seeking to help.

It is supplied with a date (the first date on which a new repeat-delivery arrangement was set up), and a "repeat every..." text string like 1 month, 3 weeks, 60 days...

It then looks at today's date and calculates when the next delivery is due, according to the original set-up date and the requisite interval between successive repeat deliveries.

The set-up date, and the text string, should come from cells on the same row as the cell from which the function is called.

Unfortunately there have been input errors in which this is not the case.

So, as part of the function's process, I want to identify the row numbers from which the input arguments came, and make sure they are on the appropriate row.

Hi everyone,

In the formula of a cell, I call a user-defined function in which the arguments it receives are passed as ByRef.

The function is defined thus:

Function Next_Due_Date(ByRef start_date As Date, ByRef frequency_label As String)

...

End Function

Let's say cell C1 contains:

=Next_Due_Date(A1, B1)

in which A1 contains a date and B1 a text-string.

Inside the function Next_Due_Date, I want to find out what the cell addresses were (A1 and B1) from which start_date and frequency_label came, in addition to the values they hold.

Can this be done, please? I tried:

a = start_date.Address

but that produced an "invalid qualifier" compiler error.

Thanks in anticipation.

I encountered the same problem: my code used to work fine, now didn't.

Then I realised I was working in a list that can be filtered (using auto-filter drop-down arrows). When the filter was off -- that is, the drop-down filtration arrows were present but no filter was being applied, so that all rows were visible -- the code worked fine. When the list was being filtered -- so that some rows were hidden -- I got the error. [INDENT]
Run-time error '1004':
Insert method of Range class failed[/INDENT]

So this method seems not to like working in ranges that are actively being filtered.