Floating Form Embedded in Excel

[yumbelie]

Okay,What I want to do is embed a form into my Excel sheet, but have it so I can lock and unlock it's position via a button (This is so the user can move the form to the bottom of the sheet prior to printing it off, so she gets a summary). I've used cells to hold the values before, but because there is no room in the 'printable' area on the sheet, the user keeps copying and pasting em - factor in merged cells and a none-to-bright user and you've got a mess on your hands - hence the requirement of a dockable undockable form that the user can move into position prior to printing, then move out the way when she's done. What I *don't* want is the user having to go into debug mode to alter the forms position on the sheet - as you can imagine what havoc they could wreck.

Thanks guys

Joseph.

 

[VBAjedi]

Clarification: is this the only purpose for the form? If so, could you just make that position the permanent position of the form and use a button to show/hide it (using MyForm.Show and MyForm.Hide)?

Let me know if that won't work or if you need specifics on how to do that.

VBAjedi

 

[SkipVought]

Piggybacking on VBAjedi, you could pick a cell, that when selected makes the form visible and when any other cell is selected, hides it. Use the Worksheet_SelectionChange event to evaluate whether or not to make form visible.

Hope this helps Skip,
SkipAndMary1017@mindspring.com

 

[yumbelie]

Okay, erm, sounds very stupid but how exactly do you insert a form into excel? Infact how do you even activate a form that you've created in visual basic editor. I can see all the various components on the 'visual basic' toolbar, but doesn't show any form insert button, or anything to run the forms I've created. As I've said before, tis all a bit new; )

Thanks

Joseph.

 

[SkipVought]

What are you trying to do with this "form?" Dies it have a message; some result? Is it tabular data that corresponds to data on the sheet? Is it static or dynamic data? Tell me more? Skip,
SkipAndMary1017@mindspring.com

 

[yumbelie]

Okay, the purpose of the form will be to hold some sum'd data from the form which the user can manuver into position and then print. It needs to be done in a form as opposed to cells, because of formatting and merging issues, not to mention the printable area. Basically they want to print the sheet of data, then have this summary form at the bottom (don't ask me why - thats what they want ) But, as I'm pretty new to Visual Basic I have no idea how to insert or use forms into Excel. I've inserted and used plenty of the other controls, but I can't see how you activate or insert a form created in the visual basic (for applications) editor.

Thanks guys

Joseph.

 

[SkipVought]

Well the form will hide some stuff already there, right? Why not format some rows with the summations that you want, for example 5 rows of summary. Then have a button that hides 5 rows of the original table and unhides 5 rows of summary. That should be real simple! Skip,
SkipAndMary1017@mindspring.com

 

[yumbelie]

Yeah, I see where you are coming from, it's a good idea, but it doesn't answer my other question, how do you use forms in VBA?

Thanks

Yum.

 

[JustinEzequiel]

you call the form's Show Method from code (macro)

UserForm1.Show

 

[RobBroekhuis]

I think the confusion in this thread is because forms really aren't meant to be printed, they're meant to retrieve information from a user. If that's not what you're doing, then you're better off manipulating the worksheet itself on the fly. That's not difficult, but we'll need more information about what you're trying to do in order to help.
Rob

 

[yumbelie]

Thanks guys, I took your advice and manipulated the sheet itself, and your right, it was considerably less hassle then I'd anticipated - as always, many thanks - your all a mine of knowledge

Joseph.