Naive programmer unable to get DoubleClick to work

[Chollymum]

I undertook a few computer programming projects many decades ago & am returning to try to write some VBA in Excel to enable us to play bridge with another couple during the lockdown. The choice of Excel is to enable one of the others to enter the decisions of their disabled spouse. I have declared a Public variable, ‘RngChoice’, that successfully stores the non-contiguous cells to select from (at least for the first bid!) but DoubleClick refuses to operate. I have probably made a naïve mistake but I have made strenuous efforts to find a solution online as well as experimenting with possible solutions myself. During development, the loop calling DoubleClick should be passed through just once but the pass is not completed. The loop code is:

Do

' Start with NB always an option

Set RngChoice = Cells(5, 6)

' Set choice area if no bids been made or calculates RngChoice

If MaxBid = 0 Then Set RngChoice = Union(Range(Cells(1, 1), (Cells(7, 5))), RngChoice) Else BidArea

Call Worksheet_BeforeDoubleClick(Nothing, False)

…….

Loop Until NoBidCount = 4 Or (MaxBid > 0 And NoBidCount = 3)



The DoubleClick procedure currently includes a checking message which correctly reports that ‘RngChoice’ is “$A$1:$E$7,$F$5”; the next line fails, whether or not the message line is included. The procedure is fairly standard:

Public Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

MsgBox RngChoice.Address

If Not Intersect(Target, RngChoice) Is Nothing Then

Target.Interior.Color = vbGreen

MsgBox "You clicked in Double Click Range!"

Cancel = True

End If

End Sub

I have tried experimenting with putting this procedure elsewhere but it is currently sitting with the other procedures for the sheet I’m working on.

The process freezes at the “If Not Intersect ….” line. The stepping-through, checking process confirms it is that line causing the problem, producing the message, “Run-time error ‘5’: Invalid procedure call or argument.”

Replacing ‘RngChoice’ with simple ranges in various formats (e.g. Cells(1, 1), Range(“A1”), Range(“A” & 1)) produces the same failure.

Any help would be much appreciated.

 

[Norie]

How are you trying to execute this code?

The BeforeDoubleClick event is normally triggered when a user, well, double clicks on a cell in a sheet but it looks like you are trying to call the event from another procedure.

 

[Chollymum]

How are you trying to execute this code?

The BeforeDoubleClick event is normally triggered when a user, well, double clicks on a cell in a sheet but it looks like you are trying to call the event from another procedure.

So great to have some external involvement after over 2 days of despare! From a book & the Net I'd learned how to create the DoubleClick procedure as a separate procedure & how to call it so thought that was the way to go. Having created the cell area of choice, it would be great if you could show me how I should incorporate DoubleClick within the required procedure. If it works, it would make selections so much simpler for users & avoid subsequent validation. I was only ever an amateur programmer ... in a very distant age! Many thanks in advance.

 

[Chollymum]

So great to have some external involvement after over 2 days of despare! From a book & the Net I'd learned how to create the DoubleClick procedure as a separate procedure & how to call it so thought that was the way to go. Having created the cell area of choice, it would be great if you could show me how I should incorporate DoubleClick within the required procedure. If it works, it would make selections so much simpler for users & avoid subsequent validation. I was only ever an amateur programmer ... in a very distant age! Many thanks in advance.

So short of sleep & desperate; I can't even spell despair!

 

[Norie]

When do you want the code to run?

If it's whenever a cell within a particular range is double clicked then you should only need the BeforeDoubleClick event itself.

 

[Chollymum]

When do you want the code to run?

If it's whenever a cell within a particular range is double clicked then you should only need the BeforeDoubleClick event itself.

Simple answer to your query: every time a bid is made or a card is played.
I am creating a set of non-contiguous cells ('RngChoice') of appropriate options, from which the user selects either the current bid or the current card to be played. Due to the situation, the user has to enter on their own computer everyone’s bid & choice of card (though, with the other pair, each has their own screen but only one of them is able to use a keyboard). The user appears to have the complete pack of cards to choose from but the DoubleClick will only register on a card that is in the hand concerned. This makes selection simple (once I can code the DoubleClick), limits the possibility of the user selecting the wrong card & avoids subsequent validation of the selection made. The process of choosing the cells in 'RngChoice' seems to work Ok. I hope this helps to explain what should be going on..

 

[Chollymum]

At long last I've understood what to do & it is so simple, as your comment indicated so thanks for nudging me in the right direction.

 

[Chollymum]

I thought I was there but once the DoubleClick is set up, it remains in situe with the user able to make as many choices as they want when I just want one choice to be made. How do I get everything to wait until an appropriate choice has been made & not to permit more than one choice? I shall then want to record the row & column of the cell that's been chosen, which determine what happens next. Unfortunately, this selection process is key to the whole project.

 

[Chollymum]

The creation of my range (RngChoice) works Ok & for the user to choose from it I am calling on DoubleClick with:

RngChoice.Activate
Application.DoubleClick

From the Net I have also found a way of creating a pause until data is entered using:

On Error Resume Next
Set MyRange = Application.InputBox(prompt:="Please select a bid", Title:="Choose", Left:=1000, Top:=1000, Type:=0)
Cells(12, 20).Value = Target.Column
On Error GoTo 0
If MyRange Is Nothing Then
Exit Sub
End If

BUT whilst I was able to obtain the row & column of the chosen cell prior to adding this delay, I now no longer can.
The location appears in the Input box but how do I get at that information to use it & is it possible to replace what appears there with the content of the cell rather than the cell location?
Ideally I want to know the column & row of the selected cell & the contents of that cell though, if necessary, the former can be obtained from the latter & vice versa.
Sorry not to use the <vba> option before but I really would appreciate some help with this. I need to make the user's tasks as simple as possible. Thanks.