Referring to the states of windows

[GSMike]

If you've ever used the wizard that Excel provides that helps people enter functions into their spreadsheets, you know how it allows you to select a cell, and then it returns the address of the cell to the form that the wizard is?
I am trying to duplicate that functionality by allowing the user to set their range for a report that will run.
I've made it as far as allowing the user to select the spreadsheet while a User Form is open (that was an accomplishment, by my standards!!), now I just can't seem to be able to return the address of the user's selection on the spreadsheet.
I've tried using the timer on the form to constantly test the spreadsheet and use
set rng=activewindow.selection
label=rng.address
etc., but this is so sloppy because of the infinite for...next loop. I would like to return the address of the user's selection whenever they make a selection, so when the form is just sitting there, no code is running.
Hope this makes sense.
Any suggestions?
Thanking you in advance.
Mike Kemp
michael.kemp@gs.com

 

[ismael]

If I understood well your question, it has an easy (but not obvious) answer:

simply assing de refEdit control to the current region before to display your form:

something like:
Sub driver()
UserForm1.RefEdit1.Text = ActiveCell.CurrentRegion.Address UserForm1.Show
End Sub

when the form is activated it shows the region selected, and as soon as the user selects another one the refedit content is updated.

 

[GSMike]

Thank you, Ismael.
I will look further into this.
I appreciate your help.
-Mike Mike Kemp
michael.kemp@gs.com