Hi NG

Imagine this: You have n different versions of a table in a worksheet. To
ease the user experiance you want to make some sort of file-chooser like
gui, where you first select your sheet and then select some type of data
(tables, parameters...). If you choose tables, then you can use the mouse to
select one of the n different versions. The active cell is then set to be
right next to the table in question.

Like this:
Choose worksheet: User selects a worksheet
Choose data type : User selects a data type
Chose a version : User selects 1 or 2, or ... , n

Is there a nifty way to make a GUI that works like this an looks like a file
chooser?

Regards
Mark

Not with any built in controls (to get the hierarchical effect) - you could
put a listview control on a userform, but a listview control is not
distributed with office.

Beyond that you could do what you describe with a userform and comboboxes or
listboxes. The refedit control allows the user to select a cell from a
userform.

here are some general resources on userforms and userform controls:

http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

Microsoft(R) Visual Basic(R) for Applications Examples for Controlling
UserForms in Microsoft Excel 97

This Application Note is an introduction to manipulating UserForms in
Microsoft Excel 97. It includes examples and Microsoft Visual Basic for
Applications macros that show you how to take advantage of the capabilities
of UserForms and use each of the ActiveX controls that are available for
UserForms

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.



http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


John Walkenbach's site:
http://j-walk.com/ss/excel/tips/userformtips.htm
Userform Tips

Peter Aiken Articles:

watch word wrap. the URL should all be one line.
Part I
http://msdn.microsoft.com/library/en...FormsPartI.asp
Part II
http://msdn.microsoft.com/library/en...ormsPartII.asp


--

Regards
Tom Ogilvy

"Mark" wrote in message
...
Hi NG

Imagine this: You have n different versions of a table in a worksheet. To
ease the user experiance you want to make some sort of file-chooser like
gui, where you first select your sheet and then select some type of data
(tables, parameters...). If you choose tables, then you can use the mouse
to
select one of the n different versions. The active cell is then set to be
right next to the table in question.

Like this:
Choose worksheet: User selects a worksheet
Choose data type : User selects a data type
Chose a version : User selects 1 or 2, or ... , n

Is there a nifty way to make a GUI that works like this an looks like a
file
chooser?

Regards
Mark

Wow. Thanks a lot for that in depth reply. I really appreciate it. :-)
I will look into it right away.

Mark


"Tom Ogilvy" wrote in message
...
Not with any built in controls (to get the hierarchical effect) - you
could
put a listview control on a userform, but a listview control is not
distributed with office.

Beyond that you could do what you describe with a userform and comboboxes
or
listboxes. The refedit control allows the user to select a cell from a
userform.

here are some general resources on userforms and userform controls:

http://support.microsoft.com/?id=168067
XL97: WE1163: "Visual Basic Examples for Controlling UserForms"

Microsoft(R) Visual Basic(R) for Applications Examples for Controlling
UserForms in Microsoft Excel 97

This Application Note is an introduction to manipulating UserForms in
Microsoft Excel 97. It includes examples and Microsoft Visual Basic for
Applications macros that show you how to take advantage of the
capabilities
of UserForms and use each of the ActiveX controls that are available for
UserForms

http://www.microsoft.com/ExcelDev/Articles/sxs11pt1.htm
Lesson 11: Creating a Custom Form
Excerpted from Microsoft® Excel 97 Visual Basic® Step by Step.



http://support.microsoft.com/default.aspx?kbid=161514
XL97: How to Use a UserForm for Entering Data

http://support.microsoft.com/default.aspx?kbid=213749
XL2000: How to Use a UserForm for Entering Data


John Walkenbach's site:
http://j-walk.com/ss/excel/tips/userformtips.htm
Userform Tips

Peter Aiken Articles:

watch word wrap. the URL should all be one line.
Part I

http://msdn.microsoft.com/library/en...FormsPartI.asp
Part II

http://msdn.microsoft.com/library/en...ormsPartII.asp


--

Regards
Tom Ogilvy

"Mark" wrote in message
...
Hi NG

Imagine this: You have n different versions of a table in a worksheet.
To
ease the user experiance you want to make some sort of file-chooser like
gui, where you first select your sheet and then select some type of data
(tables, parameters...). If you choose tables, then you can use the
mouse
to
select one of the n different versions. The active cell is then set to
be
right next to the table in question.

Like this:
Choose worksheet: User selects a worksheet
Choose data type : User selects a data type
Chose a version : User selects 1 or 2, or ... , n

Is there a nifty way to make a GUI that works like this an looks like a
file
chooser?

Regards
Mark