PDA

View Full Version : Open User Form with Selection of Sheet



bassman71
09-08-2006, 08:29 AM
Hello,

I've built Userforms that correspond with specific sheets. Is it possible for them to open as the sheet receives focus, ie clicking on sheet name:dunno ?

Thank you...............

Zack Barresse
09-08-2006, 08:54 AM
Hi there,

Sure it is. You can do this individually by using the Worksheet_Activate method or for all worksheets using the Workbook_SheetActivate in the ThisWorkbook module.

How do the userforms associate with your sheets? Is it by name? Do you have them pre-programmed? Or do you just want to specify the names via code?

bassman71
09-08-2006, 09:00 AM
Well, I've built userforms with text fields that populate cells in an underlying sheet. (Don't know why the client wanted it that way) and I'd like a the form to appear with the corresponding sheet. Also like to have a button that closes form when updated. Is this do-able?

Regards...........

bassman71
09-08-2006, 09:01 AM
Yes by name. Same name for sheet and form.

Zack Barresse
09-08-2006, 09:06 AM
This code shows a form with the same exact name as the sheet being activated (provided it's a worksheet)...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then
VBA.UserForms.Add(Sh.Name).Show
End If
End Sub

As far as a button that closes the form when updated, you'd have to create a command button on the userform, name it as you wanted, double click it, and in the code enter..

Unload Me

That's pretty much it. Does this help?

NB: The above routine has no error handling in it, which you may want to think about adding for general coding practices.

bassman71
09-08-2006, 09:33 AM
Thanks much. I'll implement OnErr handling. BTW what is the ME object, I've only seen it in Access?

Zack Barresse
09-08-2006, 09:39 AM
Me refers to the object which houses the code. So it can change depending on what module you are in. If you are in the ThisWorkbook module, Me refers to ThisWorkbook, and if you are in a worksheet module (say Sheet1), then Me refers to Sheet1 (object). It works with a Userform as well (as it's an object). Just think of it as whoever's house (module) it's in.

bassman71
09-08-2006, 09:49 AM
Word!

bassman71
09-08-2006, 10:00 AM
One problem: I can neither view nor activate the following

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If TypeName(Sh) = "Worksheet" Then
VBA.UserForms.Add(Sh.Name).Show
End If
End Sub

Zack Barresse
09-08-2006, 11:18 AM
What exactly do you mean? It works for me. What I did was, create three userforms (UserForm1, UserForm2, UserForm3), then named my three worksheets the same names (UserForm1, UserForm2, UserForm3). I put that code into my ThisWorkbook module and it worked great. Did you do all of that?

makako
09-08-2006, 04:28 PM
Firefytr, it works for me but I dont get the Add in VBA.UserForms.Add(Sh.Name).Show. I tried then VBA.UserForms(Sh.Name).Show but it doesnt work. Whats the use of "Add"? add to the application?

Aussiebear
09-09-2006, 09:58 PM
Zack, Is "Me" a shortened version of "Module"?

Zack Barresse
09-13-2006, 07:51 AM
@makako: This is one of those areas that wasn't expanded on very well by MSFT references, so there is not much you'll find on it. In fact, searching yields this (http://support.microsoft.com/?kbid=157609) as probably the best result you'll find. What the Add is for, is basically to add a userform variable at run-time, it's just the syntax needed, as you're adding a UserForm variable (in a hidden instance) not at compile time.

@Aussiebear: Check post #7 of the previous page of this thread. Yes, Me is referring to the module parent from which the routine is being ran (a run-time component).

makako
09-13-2006, 04:18 PM
Thanks