need direction on excel/vba project

[sf123]

I am working on an excel/vba project. I have data taken from sql displayed in a worksheet in excel. I created an addition menu to my excel toolbar and want to add code to the menu items in that menu that will pull up a form that was written in vb. In that form I want the user to be able to select different options of what they would like to view from the data in the spreadsheet so they don’t have to look at 5000 lines of data. When user presses submit I would like to create a new worksheet from the original one based on user specifications. My problem is calling the vb form from excel and creating something in excel from the form. information that I stored in variables. Can someone direct me to information (helpful links) that would be useful for me to proceed with this project. Thanks!

 

[ETID]

Have you tried using a pivot table ?...

 

[sf123]

Using pivot tables is the next step but first we were trying to get this working. Any suggestions?

 

[kylua]

I would:
Put all the data used to provide the report in a particular place on a sheet, say sheet 10, cells 1,1 to 20,20
Open the VB program with a shell call
In the VB, create an Excel object.
Within that object, read thru all those cells.
Produce the reporting form.

I am not guaranteeing the syntax as you only asked for direction

set ExcelOb = createobject(Excelsession)
dim myarray(20,20) as string
for n = 1 to 20
for y = 1 to 20
set curcell = excelob.reportworkbook.sheets(10).cells(n,y)
myarray(n,y) = curcell.value
next y
next n

Will put all the info into myarray. There'll be more to it that, but it's a direction.
Personally, I'd have just used a VBA form.