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

[crick1988]

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:

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

 

[crick1988]

UPDATE:
I have figured out why it as not working, but have no clue how to fix it.

It wasn't working because the textboxes are in a Multipage. If I move the textboxes out of the Mulitpage, it works perfectly, however that is not a option as a different tab is needed so the user can identify the stage/type of note needed.

Does anyone have any idea how to make the code work for Multipage?

 

[RoryA]

I suspect you need to test if fmUserform.ActiveControl is a Multipage and if so, use fmUserform.ActiveControl.Activecontrol. Something like (untested):

If typename(fmUserform.ActiveControl) = "MultiPage" then
fmUserform.ActiveControl.activecontrol.paste
else
fmUserform.ActiveControl.paste
end if

 

[crick1988]

Hi Rory,


Thank you for your reply.


I tried this, and it no longer errors out on the ActiveControl line, but not it does on the initial "If" statement, this time with a type mismatch error. I tried the name of the tab as well as the index page.


Anymore thoughts?

**YODA is the name of the form.

Private Sub cmdPasteButton_Click(ByVal Ctrl As Office.CommandBarButton, CancelDefault As Boolean)
If YODA(fmUserform.ActiveControl) = "Receiving" Then
    fmUserform.ActiveControl.ActiveControl.Paste
Else
    fmUserform.ActiveControl.Paste
End If
   CancelDefault = True
End Sub

Error:
"Run-time error '-2147352571 (800200005)':
Type Mismatch

 

[RoryA]

What is fmUserform then?

Also, please use code tags when posting code.

 

[crick1988]

I'm not sure what the fmUserform is referencing, however I initially tried changing it to match my form name (YODA), but then it just did not run at all. It only works if I leave it as fmUserform, but it doesn't work on the textboxes in the Multipage.



I apologize for no tags. I will be sure to use tags in the future, this was my first post here.

 

[RoryA]

So the form appears as YODA in the project Explorer window?

 

[crick1988]

Correct

 

[RoryA]

So what did you give the name fmUserform to?

 

[crick1988]

I was able to get an answer to this that works over at Stackoverflow (link below).

Than you all for your help.

https://stackoverflow.com/questions/51212288/vb-right-click-copy-paste-in-multipage