Activating A UserForm

[MikeG]

I have inherited a workbook with a UserForm in. When I press Ctrl L, the form activates and it has an OK button which I use to exit it.

What I want to do is to add an oval shape to my spreadsheet and when the user clicks on the shape, this will activate the UserForm.

My problem is, I don't know how to "link/assign" the UserForm to the shape. When I right-click on the oval and choose "Assign Macro", the list of macros shown does not seem to inlcude one to activate the user form. (I also do not see where Ctrl L got assigned.)

When I go to Visual Basic off the Developer Ribbon, I can see the UserForm itself, and the code:

======================================
Private Sub UserForm_Initialize()

Me.TextBox1.Text = ActiveCell.NoteText

End Sub
======================================

Can anyone tell me how to "assign" the UserForm to the Oval shape?

Thanks,

MikeG

 

[HalfAce]

Sure. First you'll need to determine the actual name of your userform. (By default they get named 'UserForm1', 'UserForm2', etc., but the person who built the userform may have given it a name of their choice.) You can go into the VBE and locate the userform of interest in the VBAProject window and note the Name property.

Then, when you right click your oval, choose Assign Macro > New, you'll be brought to the VBE in a standard code module and it will say something like:
Sub Oval1_Click()

End Sub
Simply insert this in between those lines:
UserForm1.Show so you end up with a one line routine like this:

Sub Oval1_Click()
UserForm1.Show
End Sub

Of course, you'll want to use the real name of the userform if it's anything different than UserForm1

Does that help?

 

[MikeG]

Sure. First you'll need to determine the actual name of your userform. (By default they get named 'UserForm1', 'UserForm2', etc., but the person who built the userform may have given it a name of their choice.) You can go into the VBE and locate the userform of interest in the VBAProject window and note the Name property.

Then, when you right click your oval, choose Assign Macro > New, you'll be brought to the VBE in a standard code module and it will say something like:
Sub Oval1_Click()

End Sub
Simply insert this in between those lines:
UserForm1.Show so you end up with a one line routine like this:

Sub Oval1_Click()
UserForm1.Show
End Sub


Of course, you'll want to use the real name of the userform if it's anything different than UserForm1

Does that help?

Perfect!
Thanks HalfAce

 

[HalfAce]

Most welcome. (Glad it helped too because my version of 07 is at home and I'm working off of memory here with 03, but I think all this stuff's pretty much the same between 'em.)

 

[MikeG]

Most welcome. (Glad it helped too because my version of 07 is at home and I'm working off of memory here with 03, but I think all this stuff's pretty much the same between 'em.)

One other question, just out of curiousity...

I looked through all the macros in the workbook, and I could not see one assigned to Ctrl L.

Also, speaking to users, I suspect that the UserForm was originally created in another workbook (that I do not have.) Is it possible that the Ctrl L is a "legacy" or something like that from another workbook?

Maybe I'm completely off-base, but just wondered.

Thanks,

Mike

 

[GTO]

One other question, just out of curiousity...

I looked through all the macros in the workbook, and I could not see one assigned to Ctrl L ...

When you look through all the standard and sheet modules, do you see a procedure that has

UserForm1.Show

...where 'UserForm1' is your userform's name?

 

[MikeG]

When you look through all the standard and sheet modules, do you see a procedure that has

UserForm1.Show

...where 'UserForm1' is your userform's name?

GTO

No - I don't - other than the new one I just added.

Thanks,

Mike

 

[GTO]

Hi Mike,

Well, I am at a loss.

Sorry,

Mark

 

[xenou]

AFAIK keyboard shortcut assignments are created via the Excel GUI (macro dialog box). As such, it's not visible in the code modules. You have to find the macro, view it in the macro dialog, and then look at the keyboard shortcut assignment (if any).

It *is* possible to set a keyboard shortcut assignment in code (for instance, in a workbook_open event). This probably done less often. And since you can't find it it's probably not the case here.

HTH,
ξ

 

[MikeG]

Thanks GTO and Xen.

I'll kepp looking.

Mikeg