PDA

View Full Version : Context Menu on UserForm



kclewis0614
02-19-2013, 01:50 AM
I apologize if this is a rehash, but the only code I found was from 2007.

I have the following code running on the right-click event of a user form. The context menu appears, and the buttons can be clicked, but they produce no message box nor any other code I have attempted on the OnAction.



Private Sub Call_Txt_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Button = 2 Then
ThisWorkbook.MakePopUp
Application.CommandBars("MyPopUp").ShowPopup
End If
End Sub

Sub MakePopUp(obj As Object)
'Remove any old instance of MyPopUp
Excel.Application.CommandBars("MyPopUp").Delete

With Excel.Application.CommandBars.Add(Name:="MyPopUp", Position:=msoBarPopup)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Coyp"
.FaceId = 19
.Caption = "Copy"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Paste"
.FaceId = 21
.Caption = "Paste"
End With
End With
End Sub

Sub Coyp(obj As Object)
MsgBox "DataForm " & obj.Name
End Sub



I have included these together for ease of reading.

Call_Txt_MouseUp is called within the UserForm, the rest is in ThisWorkbook.

p45cal
02-19-2013, 04:20 AM
in the userform code-module:Private Sub TextBox1_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
If Button = 2 Then
MakePopUp
Application.CommandBars("MyPopUp").ShowPopup
End If
End Sub
in a Standard code-module:Sub Coyp() '(obj As Object)
MsgBox "DataForm " ' & obj.Name
End Sub
Sub MakePopUp() '(obj As Object)
'Remove any old instance of MyPopUp
Excel.Application.CommandBars("MyPopUp").Delete

With Excel.Application.CommandBars.Add(Name:="MyPopUp", Position:=msoBarPopup)
With .Controls.Add(Type:=msoControlButton)
.OnAction = "'" & ThisWorkbook.Name & "'!" & "Coyp" '"Coyp" '
.FaceId = 19
.Caption = "Copy"
End With
With .Controls.Add(Type:=msoControlButton)
.OnAction = "Paste"
.FaceId = 21
.Caption = "Paste"
End With
End With
End Sub For the moment, I've commented out the obj you seem to be trying to pass around, because I'm not sure what the intention is (are you trying to get this working with multiple textboxes on the userform by using a class module?). As it stands it works on a right-click of the text box called TextBox1
As you can see my textbox's name was TextBox1
Private Sub TextBox1_MouseUp(ByVal B…
You should make sure the event handler is properly matched to your textbox too.
I'm guessing you got this code from the internet? Can you provide a link?
Oh, and what version of Excel are you using?

p45cal
02-19-2013, 04:21 AM
For others, cross-posted a couple of weeks ago at
http://www.ozgrid.com/forum/showthread.php?t=174651

kclewis0614
02-19-2013, 05:58 AM
Yeah the obj was because it didn't seem to be passing the ref... :S

I am using Excel 2010. I did get it from the internet...
Ozgrid/forum/showthread.php?t=22696

sorry about the obfuscation... I cannot post links.

It does not seem to be crawling over to the .OnAction Event when I click.

p45cal
02-19-2013, 06:45 AM
Are you wanting this to work on just a given textbox or several textboxes on the userform? If neither of the above - where?
What are you wanting to Copy?

kclewis0614
02-19-2013, 06:48 AM
I have 3 textboxes on the user form. It needs to detect on which it was activated and execute the command on that textbox. for example,it knows if I right-clicked on Call_Txt and can copy or cut the value residing there, or paste into it

kclewis0614
03-30-2013, 10:31 PM
Are there any suggestions? I am at my wit's end and about to tell the client the dreaded statement that I cannot fulfill this ask.

SamT
03-31-2013, 05:27 AM
.OnAction calls a macro, therefore you need macros named like CopyMe and PasteMe, which C&P ThisWorkbook.

.OnAction = CopyMe
Sub CopyME()
ThisWorkbook.Copy
End Sub

kclewis0614
03-31-2013, 07:00 AM
Thanks for replying, however as I said earlier, it would need to detect the text box that was clicked and run the copy and paste operations on those objects. Correct me if I am wrong but it does not seem like that code would execute a copy person on the userform, much less a child object on that userform.:dunno

snb
03-31-2013, 07:32 AM
It seems rather hard to return the calling object if you do not pass it to the method:

ThisWorkbook.MakePopUp Call_text

Sub MakePopUp(obj As Object)

p45cal
03-31-2013, 12:48 PM
I have this 90% done with a class module (adapted this (http://spreadsheetpage.com/index.php/tip/handle_multiple_userform_buttons_with_one_subroutine/)); but what is it that you want to copy? Is it something different depending on which textbox you right-click on?
And when you choose Paste from the pop-up, what do you want to paste, and where?

p45cal
04-01-2013, 03:00 AM
I have this 90% done with a class module (adapted this (http://spreadsheetpage.com/index.php/tip/handle_multiple_userform_buttons_with_one_subroutine/)); but what is it that you want to copy? Is it something different depending on which textbox you right-click on?
And when you choose Paste from the pop-up, what do you want to paste, and where?Ignore the above, I've just re-read a previous response from you..

SamT
04-01-2013, 10:32 AM
In UserForm Code
'Declare Form Property
Dim f_TextBoxClicked As String
Public Prpoety Get_TextBoxClicked() AS String
TextBoxClicked = f_textBoxClicked
End Property
For each clickable TextBox Code
'Set Form Property Value
Sub 'On click event
f_TextBoxClicked = 'Value to pass to Coyp

In Coyp Code to retrieve value
Value = UserForm(by Name).TextBoxClicked

p45cal
04-01-2013, 11:31 AM
Take a look at the code in the attached, in Module8, UserForm3's code-module and Class1 code.

p45cal
04-19-2013, 06:23 AM
I wonder if the time I spent on this was wasted?

kclewis0614
04-19-2013, 06:25 AM
Sorry, I hadn't gotten notified that there was a response...I will look a this stuff when I get a chance. Again, I apologize.

kclewis0614
05-07-2014, 10:33 AM
This project was scrapped without my input so... all this work was for naught... damn politics

Kenneth Hobs
05-07-2014, 12:35 PM
p45cal, no your time was not wasted. The code is handy.

kclewis0614
05-07-2014, 01:52 PM
I agree... the code is handy Thank you p45cal. I apologize for the time taken to respond I am a no good slacker:banghead: