0

I am trying to create a context menu item to add a row on a sheet at the position of the selected cell and do some more stuff as well. This is done using a custom object of the class clsMyControls to handle all of my custom controls. The controls created, call a macro in a standard module, which forewards the command to the custom object MyControls. MyControls will foreward the command to another object using CallByName. This way all of my custom Objects can use MyControls to create controls and to route the commands to their own methods.

The routing works fine. I can read the Address of the selected cell, alter the Value etc. However, when I try to insert or delete a row, nothing happens, not even an error.

Below is the code to reproduce the issue. It implements two ways to create the control and to call the Insert Method. The one I have issues with, and the simple way that works.

The second way does not use MyControls and creates its own control. This way it is possible to insert a row. Both ways call the same Insert method of the same object.

EDIT: [ The difference is the parameters that are passed to the macro. As soon as a Parameter is built into the .onAction-String of the control, the insert method fails. WHY? ]

First, the (simplified) Class clsMyControls which is supposed to handle all my custom controls

Option Explicit
Option Base 1
Private myItems As Collection 'the collection to carry all controls created here
Private myObjects As Collection 'the collection of objects that create controls by means of this object
'____________________________    
Private Sub Class_Initialize()
    Set myItems = New Collection
    Set myObjects = New Collection
End Sub
'____________________________    
Public Sub ReturnFromMacro(Optional args As Variant)
    Debug.Print "ReturnFromMacro " & Selection.Address
    'Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'It does not work here
    CallByName myObjects(args(1)), args(2), VbMethod 'forewarding
End Sub
'____________________________
Public Sub CreateEntry(ObjectReference As Object, ProcedureName As String, Caption As String)
Dim control As Object
With Application.CommandBars("Cell").Controls
    Set control = .Add(Type:=msoControlButton, Before:=1, Temporary:=True)
End With
With control
    .Caption = Caption
    .onAction = ThisWorkbook.name & "!myControlsMacro(" & Chr(34) & "InsertTest" & Chr(34) & "," & Chr(34) & "Insert" & Chr(34) & ")"
    .Tag = Application.ThisWorkbook.name & "_clsMyControls"
    .beginGroup = True
End With
myItems.Add control, Caption 'storing the newly created Control in a collection
myObjects.Add ObjectReference, ObjectReference.name 'storing the object in a collection to later call it
End Sub

Next, the Class clsInsertTest that uses myControls to create a custom control and owns the Insert method that is called. In addition it creates an own custom control that bypasses the Object MyControls

Option Explicit
Dim control As Object 'the object to carry the Control that is created in this class
'____________________________    
Property Get name() As String
    name = "InsertTest"
End Property
'____________________________
Private Sub Class_Initialize()
    'asking myControls to create a control, passing Reference THIS object, The method to be called, the name of teh control
    MyControls.CreateEntry Me, "Insert", "InsertTest"
    createOwnControl 'the simple way
End Sub
'____________________________
Public Sub Insert()
    Debug.Print "Insert Called at " & Selection.Address
    Selection.Value = "clsInsertTest was here"
    Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 
End Sub
'____________________________   
Private Sub createOwnControl()

With Application.CommandBars("Cell").Controls
    Set control = .Add(Type:=msoControlButton, Before:=1, Temporary:=True)
End With

With control
    .Caption = "InsertTest2"
    .onAction = ThisWorkbook.name & "!InserTest2Macro"
    .Tag = Application.ThisWorkbook.name & "_clsMyControls"
    .beginGroup = True
End With

End Sub

and last, the module containing initialization, cleanup and the Subs that are called by the custom controls and foreward to the objects

MyControlsMacro is called by the control built by MyControls

InsertTest2Macro is called by the control built by the InserTest Object directly

Option Base 1
Public InsertTest As clsInsertTest 'test object
Public MyControls As clsMyControls 'the Object to handle my controls
'____________________________     
Sub CleanUp() 'what it says
Set InsertTest = Nothing
Set MyControls = Nothing
Dim control As Object
    For Each control In Application.CommandBars("Cell").Controls
        If control.Tag = Application.ThisWorkbook.name & "_clsMyControls" Then
           control.Delete
        End If
    Next control
'Application.CommandBars("Cell").Reset 'just in case...
End Sub
'____________________________     
Sub CreateTestObject() 'create my objects (calld at wb open)
    Set MyControls = New clsMyControls
    Set InsertTest = New clsInsertTest
End Sub
'____________________________     
Public Sub myControlsMacro(ParamArray args() As Variant) 'the Sub to foreward the commands to my Controls handler
Dim handover() As String
Dim wert As Variant

Debug.Print "myControlsMacro called at " & Selection.Address
'Selection.EntireRow.Insert , CopyOrigin:=xlFormatFromLeftOrAbove 'does not work

'transforming the ParamArray "args"  into an Array of strings to be able to pass it to the next method
For Each wert In args
    If Not (Not handover) Then
        ReDim Preserve handover(UBound(handover) + 1)
    Else
        ReDim handover(1)
    End If
    handover(UBound(handover)) = wert
Next
'calling the object to handle my Controls
MyControls.ReturnFromMacro handover

End Sub
'____________________________
Public Sub InserTest2Macro() ' the simple way
    Debug.Print "InserTest2Macro called at " & Selection.Address
    CallByName InsertTest, "Insert", VbMethod
End Sub
2
  • 1
    What is your code? My crystal ball to see your code appears broken :) Commented Apr 16, 2014 at 13:49
  • added the complete code to reproduce the issue. Commented Apr 17, 2014 at 7:53

1 Answer 1

0

I have found a solution here:

Excel VBA CommandBar.OnAction with params is difficult / does not perform as expected

The single quotes in the .onAction and removing brackets made it work.

.onAction = ThisWorkbook.name & "!'myControlsMacro " & Chr(34) & "InsertTest" &_
Chr(34) & "," & Chr(34) & "Insert" & Chr(34) & "'"
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.