VBA User Input by selecting cell(s)

O

OldOne

New Member
Joined
Oct 21, 2008
Messages
12
Hello,

I am trying to create a macro that will allow employees to sort a product matrix in excel. The user would select up to six criteria for sorting by simply clicking on the headers over the columns. I would prefer not to use a list box, since the list is quite long and sub-catagorized.

Idealy, a message box or input box (or something similar) would ask the user to select the six criteria in order of priority by clicking on the header cells in row 3, then pass execution back to the macro.

I am struggling with finding a way to pass control to the mouse (mid-macro execution), capture/select the cells, then pass execution back to the macro. I searched the forum database and web, but found nothing similar.

Can anyone, please help? :confused:

Thank you in advance!

OldOne
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Sort by date Sort by votes
You can use Application.Inputbox and specify Type:=8 to get a cell selector, but I'm not sure that is the best way, unless you get them to select the criteria cells one at a time.
 
Upvote 0
Thanks, Rorya.

I'm not familiar with that, but will give it a try. I appreciate your suggestion and will provide feedback and/or additional questions after making an attempt. :)

OldOne
 
Upvote 0
Rorya,

I can make that work by making the selected cell the active cell then deriving the cell location. Thanks.

...curious, however. Is there a shortcut that would return the cell location directly from application.inputbox, without the additional steps? The actual cell location shows up in the inputbox when selected.

Thanks again. :)

OldOne
 
Upvote 0
The inputbox should return a range object - you can then simply use its address property - for example:
Code: 
   Dim rng As Range
   Set rng = Application.InputBox(prompt:="Select a cell", Type:=8)
   MsgBox rng.Address
 
Upvote 0
Rorya,

Awesome, sir.

Thanks, so much for helping this "OldOne" out.

I greatly appreciate the timely solution. :)

OldOne
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Replacing delete with a value
Replies
3
Views
160
Alex Blakenburg
A
Sumeluar
  • Solved
Input box with selected text from cell
Replies
8
Views
146
Akuini
Akuini
S
  • Question
Sorting data from one sheet to multiple others
Replies
0
Views
214
saeriefaeriexx
S
GMC The Macro Man
  • Solved
VBA Script - Return to named file
Replies
6
Views
124
mumps
mumps
Z
  • Solved
Excel VBA: Using OR with <>
Replies
3
Views
107
rlv01
R

Forum statistics

Threads
1,214,981
Messages
6,122,565
Members
449,089
Latest member
Motoracer88

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top