Allowing control behind a userform 1

[AcousticalConsultant]

Hello,

I am trying to make an interactive macro where the user is able to select cells while a userform is prompting him to do so. Similar to the way that Excel allows you to create a graph. When entering data, you can click and drag your mouse over the desired region and the formula will automatically appear in the corresponding textbox.

When I load a userform, I am not able to access the spreadsheet behind it. Is there a way to allow for access to both?

Thanks!

 

[PHV]

straight from the VBA help:
Set myCell = Application.InputBox( _
prompt:="Select a cell", Type:=8)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[AcousticalConsultant]

works great! Thanks!

 

[fumei]

You could also try setting the userform as ShowModal = False. That way you can access the entire application behind the userform. Not just the things explicitly run by instruction - eg. myCell = Application.InputBox( _
prompt:="Select a cell", Type:=8).

You have full access to both the application, and the userform.

Gerry

 

[AcousticalConsultant]

Fumei,

That's excellent! Thanks for the tip!

Pascal

 

[combo]

You can also use refedit control on modal userform.
You have to add it to the control toolbox first. To do it:
1. find refedit.dll file (in office xp it is in Program Files\Microsoft Office\office10 folder,
The rest in Visual Basic Editor:
2. add reference to this file, use 'Browse..' button to locate the file,
3. add userform or open a file with userform, activate it, to enable display toolbox,
4. right-click the toolbox' multipage area, to add controls, find RefEdit.Ctl in the list and mark it, accept.

You should get a new control in the toolbox, used on the userform works similarly to the built-in one for picking range.

combo