I use this
so that with right click on the combobox I have the option to copy or paste, is there a way to use the same method but also work for Textboxes? currently it only works for combobox
Code:
Option Explicit
'Popup objects
Private cmdBar As CommandBar
Private WithEvents cmdCopyButton As CommandBarButton
Private WithEvents cmdPasteButton As CommandBarButton
'Useform to use
Private fmUserform As Object
'Control array of ComboBox
Private colControls As Collection
'ComboBox Control
Private WithEvents tbControl As MSForms.ComboBox
'Adds all the ComboBox in the userform to use the popup bar
Sub Initialize(ByVal UF As Object)
Dim Ctl As MSForms.Control
Dim cBar As clsBar
For Each Ctl In UF.Controls
If TypeName(Ctl) = "ComboBox" Then
'Check if we have initialized the control array
If colControls Is Nothing Then
Set colControls = New Collection
Set fmUserform = UF
'Create the popup
CreateBar
End If
'Create a new instance of this class for each ComboBox
Set cBar = New clsBar
cBar.AssignControl Ctl, cmdBar
'Add it to the control array
colControls.Add cBar
End If
Next Ctl
End Sub
Private Sub Class_Terminate()
'Delete the commandbar when the class is destroyed
On Error Resume Next
cmdBar.Delete
End Sub
'Click event of the copy button
Private Sub cmdCopyButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
ActiveControl.Copy
CancelDefault = True
End Sub
'Click event of the paste button
Private Sub cmdPasteButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
ActiveControl.Paste
CancelDefault = True
End Sub
Function ActiveControl(Optional ctlContainer As MSForms.Control) As MSForms.Control
If ctlContainer Is Nothing Then
'Get the active control on the form
Set ActiveControl = fmUserform.ActiveControl
Else
'Get the active control inside the container
Set ActiveControl = ctlContainer.ActiveControl
End If
'Have we got a text box yet?
If TypeOf ActiveControl Is MSForms.ComboBox Then
Exit Function
Else
'No, so recurse through the container controls
Set ActiveControl = ActiveControl(ActiveControl)
End If
End Function
'Right click event of each ComboBox
Private Sub tbControl_MouseUp(ByVal Button As Integer, ByVal Shift As Integer, _
ByVal X As Single, ByVal Y As Single)
If Button = 2 And Shift = 0 Then
'Display the popup
cmdBar.ShowPopup
End If
End Sub
Private Sub CreateBar()
Set cmdBar = Application.CommandBars.Add(, msoBarPopup, False, True)
'We’ll use the builtin Copy and Paste controls
Set cmdCopyButton = cmdBar.Controls.Add(ID:=19)
Set cmdPasteButton = cmdBar.Controls.Add(ID:=22)
End Sub
'Assigns the ComboBox and the CommandBar to this instance of the class
Sub AssignControl(TB As MSForms.ComboBox, Bar As CommandBar)
Set tbControl = TB
Set cmdBar = Bar
End Sub