How to turn this macro to work for textboxes too?

Kyletok

New Member
Joined
Sep 16, 2017
Messages
47
I use this
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
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
 

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Forum statistics

Threads
1,215,339
Messages
6,124,375
Members
449,155
Latest member
ravioli44

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top