VB error "Object doesn't support this property or method"

crick1988

New Member
Joined
Jul 5, 2018
Messages
6
Hello all,
Please let me preface my question with the fact that I am completely self taught, so please bear with me if I ask for clarification questions.

Using VB (Microsoft Visual Basic for Application 7.0) I created a notation tool for my team at work a few years ago, however the only complaint that I still get about it is the fact that right click does not work. I Google and was able to find code that creates Copy/Paste pop-ups like right click. It work perfectly in the dummy form that I was testing it in, however once I pasted it in the notation tool, it started erroring out.


When I right click to either paste or copy (I get the same error regardless of which one I choose, it just goes to that specific portion of the code when I debug it), I get the pop-up, however once I make a selection I get the error of 'Object doesn't support this property or method'. When I debug it, it highlights the specified code of whichever action I tried to do. When I reset it, it completes the action (either copy or paste) in the first cell of the workbook.


I am going completely crazy and would love any assistance.


This is the code that I am using:


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 textbox
 Private colControls As Collection

 'Textbox Control
 Private WithEvents tbControl As MSForms.TextBox
 'Adds all the textbox 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) = "TextBox" 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 textbox
 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)
 fmUserform.ActiveControl.Copy
 CancelDefault = True
 End Sub

 'Click event of the paste button
 Private Sub cmdPasteButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
 fmUserform.ActiveControl.Paste
 CancelDefault = True
 End Sub

 'Right click event of each textbox
 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 Textbox and the CommandBar to this instance of the class
 Sub AssignControl(TB As MSForms.TextBox, Bar As CommandBar)
 Set tbControl = TB
 Set cmdBar = Bar
 End Sub
 
Last edited by a moderator:
When working in a Frame/Multipage environment, I've found these two functions to be useful.

Code:
Function ReallyActiveControl(Optional Container As Object) As MSForms.Control
    If Container Is Nothing Then Set Container = Me: Rem defaults to userform
    On Error Resume Next
    Do
        If TypeName(Container) = "MultiPage" Then
            Set Container = Container.Pages(Container.Value)
        Else
            Set Container = Container.ActiveControl
        End If
    Loop Until Err
    Set ReallyActiveControl = Container
End Function

Function UFParent(someControl As MSForms.Control) As Object
    Set UFParent = someControl.Parent
    On Error Resume Next
    Do
        Set UFParent = UFParent.Parent
    Loop Until Err
    On Error GoTo 0
End Function
 
Last edited:
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Forum statistics

Threads
1,223,231
Messages
6,170,885
Members
452,364
Latest member
springate

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