CODEWITHSUNDEEP
excelvba
Shyam
Shyam

Reputation: 682

How to call a function in vba which is already assigned to a button

I tried to create a ribbon in excel and I was successful. Now I have assigned a macro to a button.

Function delete_cells(control As IRibbonControl)

Now, I created another macro, from which I need to call the function delete_cells. I tried to call it as below.

Function modify_cells(control As IRibbonControl)
delete_cells
End Sub

I am getting an error saying Argument not optional. Please help me with this error.

Upvotes: 7

Views: 3772

Answers (2)

Doug Glancy
Doug Glancy

Reputation: 27478

I suggest that you create a separate subroutine that you call from the button's OnAction and anywhere else you want to call it from, e.g.:

'button macro
Sub cmdDeleteCells_OnAction(control as iRibbonControl)
DeleteCells
End Sub

'another Sub that calls the delete routine
Sub SomeOtherSub
DeleteCells
End Sub

'the one they're all talking about
Sub DeleteCells
msgbox "All your cells are toast, bwah hah hah ha!"
End Sub

EDIT: If you really want to just call the button's OnAction sub, you need to pass it an iRibbonControl object as well, so declare a fake one:

Sub CallTheButtonsCode()
Dim FakeControl As IRibbonControl

cmdDeleteCells_OnAction FakeControl
End Sub

I really don't recommend this for code maintenance reasons, but it works.

Upvotes: 7

kolcinx
kolcinx

Reputation: 2233

In your Function delete_cells(control As IRibbonControl) you have an REQUIRED argument ...(control As IRibbonControl). When you call the function it should look like this :

Function modify_cells(control As IRibbonControl)
delete_cells(myControl) 'myControl is your variable. Define what control you want to pass to the function.
End Sub

Upvotes: 0

Related Questions