Excel VBA: How To: Floating VBA Control Panel for Initialization [RESOLVED]

[Webtest]

Esteemed Forum Participants and Lurkers:
========================================
Excel '97 VBA

I am trying to set up an "Initialization Panel" for an Excel Workbook. To get started, I am trying to set up a small floating panel (NOT attached to any worksheet ... just part of the workbook) with TWO pusbuttons ... "Mode 1" and "Mode 2". I want to initialize some Global Variables in the button code, and then close/delete the panel.

I haven't had much success with the panel approach, but I have successfully used pushbutton controls on a SHEET before. I can make that work.

Here is what I have tried so far:

1)I created and saved my TEST Workbook.
2)I opened the "Controls Toolbox" Toolbar.
3)I didn't find a "Panel" or a "Form" in the standard toolbar, so I opened the "More Controls" Icon.
4)The only resource I could find in the "More Controls" list that looked reasonable was the "Microsoft Forms 2.0 Frame", so I clicked on that and was able to open a Popup Panel/Form/Frame(?) and set the Caption to "Select a Mode".
5)I was able to install a MsgBox test message in the Click Event for the frame ... "Frame1_Click()".

From here, it gets sticky. I am able to get TWO Control Toolbars ... 1 from Excel ("Control Toolbox"), and evidently a different one from the "Microsoft Forms 2.0 Frame" reference ("Toolbox" with a tab "Controls"). I have created pushbuttons from both toolboxes and set the captions for both, but I can not get to the CODE for either one of them! Right clicking on either an MS(?) button or an Excel button pops up a menu that gives me a "Properties" choice, but the "Code" choice is grayed out. How can I get to the Code template for one or both of these button types.

Another problem is that the Panel/Form/Frame is only visible while Sheet1 is open ... the sheet that was open when I created the form. When I select any other sheet, the form disappears. How can I keep the form visible REGARDLESS of which sheet is open? Question 2 is how can I 'destruct' the form when I am done with it?

Thank you for any and all of your gracious comments and assistance with this inquiry.

[Static]

to add a real form...

go to the code window (alt-F11) then the second button to the left...
add USER FORM

to make it appear.. put code in the

Private Sub Workbook_Open()
    Form1.Show
End Sub

[Ecniv]

Alternatively look at the Office Toolbars/Menu.

I think there is an object there, where you can create the buttons etc.

For code, use the record macro then create the menu, and the buttons, stop recording then check what code was created.

[Webtest]

go to the code window (alt-F11) then the second button to the left...
add USER FORM

Hmmmmm ... <Alt><F11> brings the Excel Worksheet window to the front ("View > Code" is F7). If your suggested function is really on the Worksheet window, walk me through the MENU BAR path because I don't know what "second button" you are referring to.

Ahhhh ... I just found it ... In the VB Editor: View > Code > Insert > User Form.

I'll give that a try. Thanks!

P.S.: Thanks Static ... I got a basic test working!

Also, for future reference, "Unload UserFormName" is the destructor code to get rid of the form when done with.

[Webtest]

I got the basics working, but the Form that is generated is MODAL ... you have do do something with it before you can get back into the sheet.

Is it possible to open the Form NOT modal so that you can at least thumb through the sheets while the Form is open?

[DKenny]

Art

Is it possible to open the Form NOT modal so that you can at least thumb through the sheets while the Form is open?

Change the "ShowModal" porperty of the Form to FALSE.

[Webtest]

Change the "ShowModal" property of the Form to FALSE.

No beer Declan. (no cigar either). When I add "UserForm1.showmodal = False" I only see "Show" in the hint list. "ShowModal" is not in the list ... in fact, "PictureSizeMode" and "WhatsThisMode" are the only options with mode in them. If I try to run it anyway, I get the following error:

Compile error: Method or data member not found

Any other suggestions?

[DKenny]

Art
I don't think you can sent the ShowModal property through code. If I need a non-modal Form I always set the value of the ShowModal to FALSE in the properties window during design-time.
If you cannot see the proerties window in the VBA editor, press F4. The ShowModal property is in the "Behaviour" section.

[Webtest]

That did it Declan! I overlooked it in the 'Alphabetic' Properties list, but when I set it to 'Categorized' it was right there at the bottom of the 'Behaviour' section. Thanks again for your always superb assistance.