Popup new workbook w/ subset of data from original, workbook

[jmichaelp]

Hi, I'd like to create a popup that contains a subset of data from the open workbook when a user clicks on a cell. I want the functionality of a spreadsheet, so a userform won't work. I also want the new workbook to open up to a specified size each time. I don't need to save the newly opened workbook.


I assume my starting point would be:
Workbooks.Add xlWorksheet
and then refer to a template file set up the way I want. However, can a template be set up to open a workbook as a certain size??

Is there a better way to accomplish opening up a new worksheet (or something else)?

Thanks!

 

[WillR]

Re: Popup new workbook w/ subset of data from original, work

I want the functionality of a spreadsheet, so a userform won't work.

You can place a spreadsheet control in a userform. It has pretty much full functionality... really depends how much data you are wanting to show.

A bit more info would help.

 

[jmichaelp]

Re: Popup new workbook w/ subset of data from original, work

WillR - thanks for the quick reply! I haven't used the worksheet control in a userform, but will check it out.

In the meantime, I've got it using the following code:

Sub test()
Application.Workbooks.Add
With ActiveWindow
    .WindowState = xlNormal
    .Top = 1
    .Left = 1
    .Height = 300
    .Width = 300
End With
End Sub

I can then turn off all of the menus, shhet tabs etc. in code to just leave the cells. I assume I'll need a template file to which to refer that will house whatever vba I want to run in this popup window. Now I just need to stop this code from un-maximizing the currently open sheet...