create an interactive pop-out window with buttons from sheet

**cowboy713** · 12-09-2010, 05:39 PM

First I'd like to thank everyone who's helped me along in build my current spreadsheet. The latest bundle of fun change involves trying to change my sheet around so the buttons that the participant interact with all be displayed in a nice pop-out window because the participants "aren't supposed to see anything else". I've been messing around with creating a pop-up window but had no luck whatsoever.

All I really need it do to is be able to click the "Begin SSQ" button and have cells A10:G45 appear in a separate window. Once the user makes their selections, then can click "Click to Finish" and it returns back to the spreadsheet.

As long as the button selections made in the popup window remain on the spreadsheet I can process all the data from there. Haven't been able to find a good way to make it work so for now I have a Hide and Unhide macro set up to hide the stuff their not supposed to see. Everyone just feels it'd be better if participants weren't looking at the excels cells while filling in their survey.

I've uploaded the file for anyone that wants to take a look. Thanks again all!

**Leith Ross** · 12-09-2010, 09:37 PM

Hello cowboy713,

I don't have Excel 2007. When I open macro enabled workbooks in 2007, I can read them. Running them works most of the time and changing them is a gamble. That seems to be the case here. I have it working except the UserForm closes after a entry is made. This does not happen in an Excel 2003 workbook, just the converted workbook. If you can post a 2003 copy, I'll add the changes and you can resave it as a 2007 copy.

**cowboy713** · 12-09-2010, 11:29 PM

Ok heres the 2003 save of the updated document. If it would work better saved as a template I could do that.

**Leith Ross** · 12-10-2010, 12:32 AM

Hello cowboy713,

I went ahead and copied the workbook information into a new 2003 workbook. I have test it and it works correctly in 2003. If it doesn't work when you save in 2007 format, you can copy over the code and the form to your 2007 workbook. Here is the macro code.

UserForm Code

Please Login or Register  to view this content.

Module1 Code

Please Login or Register  to view this content.

**cowboy713** · 12-10-2010, 01:12 AM

The user-form is definitely what I'll have to use, is there any way to have the formatting basically match whats between the A10:G45 range though? The way it's laid out has to resemble an original SSQ. It probably would have been easier to just build it all in VBS from the start but with as much data input as we're getting excel seemed to be a smarter option.

**Leith Ross** · 12-10-2010, 03:03 AM

Hello cowboy713,

There is only one control I know of that looks like a spreadsheet. That is the MSFlexGrid control. However, I don't think this control is available anymore. The next closet control would be a ListView control except it doesn't have grid lines. This is a case where you will have to decide to fish or cut bait.

**cowboy713** · 12-10-2010, 10:08 AM

Well, I'll present both solutions to the other experimenters and see if their willing to sacrifice the format for the windowed survey. Thanks for all your help, I'll make sure to post an update on whatever ends up being decided with it.