Pause vba and select cell as value for variable 1

[telephoto]

Hi guys, not something I've tried before.
Situation: I play a bowls league, several teams in the league, and the organiser produces an excel sheet showing all matches. In order to compile our club's annual handbook I only need our matches.
Progress to date: Using vba I copy the current sheet (to preserve original data)
Focus is shown on new sheet, msgbox states click next button.
Next button starts inputbox for the team name to be inserted.
All matches are examined and targetclub's matches are shown on top rows, remainder deleted.
SUCCESS
Improvement
I want to cut out the second button.
I would like to be able to have msgbox state "Click on team name to be sorted"
macro pauses until user clicks on a cell with their club's name.
Variable is set to the cell value and the rest operates as above.
Problem:
I can't make it pause until the mouse click, and I don't know how to make the cell value the variable - because it could be any one of a dozen or so different cells.
Any help would be appreciated.

Thank

T

 

[combo]

If all goes witin the code - use either:
1) refedit control from the userform, you can have global variable to store the clicked cell, manage selection in the userform code,
or:
2) Application.Inputbox directly in code, set Type:=8 for range.
If you plan to use refedit, keep the userform modal, otherwise excel can crash.

combo

 

[telephoto]

Thank you for your response Combo,unfortunately I am still puzzled.
1. There is no Userform, I copy the worksheet and work on the copy.

2. Using Inputbox, the only way I have used this is as type input, this is how I am using it in the first version:
targetclub=Inputbox("Enter club name")

What I am trying to do is avoid the typing by picking a cell with the mouse.

T

 

[combo]

There are two InputBox options in excel vba: vba native InputBox, where you enter text, and excel library Application.InputBox method. They have different sets of arguments, in the latter one you have optional Type, if set to 8 you can select range as input.
In your code above you use vba InputBox, that cannot be used for selection.

combo

 

[telephoto]

Thank you.
I have been working with version 2003, but application.inputbox is on 2013 and higher.
works as required. Please find star.

Not all the club match secretaries will be running 2013/6 does anyone know a way of doing it in 2003?

T

 

[combo]

[tt]Application.InputBox[/tt] is an old property, I'm sure it existed before 2003, you can use it in your version. As for 2003:

https://docs.microsoft.com/en-us/pr...e/developer/office-2003/aa195768(v=office.11)

combo

 

[strongm]

> I'm sure it existed before 2003

yep, existed back in Office 97

 

[combo]

Wow, just found in among my old IT books:
Visual Basic User's Guide. Microsoft Excel Version 5.0, Microsoft Corporation, 1993-94, pp. 220-221:
InputBox method: Display a prompt in a dialog box and return the information entered by the user. This method is similar to the InputBox function but provides additional functionality, such as requiring input to be of a specific type.

combo

 

[telephoto]

OK, I surrender.

I tried this over the weekend and it failed, since then I have been a bit tied up, and I just attempted it again.

You are both correct - it works exactly as required.

Thank you.

Telephoto