Execute Command Button "Click" Subroutine 1

[mattj63]

I'm trying to execute a subroutine in a command button's _Click procedure from a separate module in Excel. I want to perform the command button click from code in case the user did not do it manually. I'm using the following code but I get an error.

    Windows("Form-SampleSubmit.xls").Activate
    Sheet1.cmdFill_List.Click

What am I doing wrong?

 

[SkipVought]

Hi,

???

sub button_click()
   TheMacroToRun
end sub

or

    Windows("Form-SampleSubmit.xls").Activate
    TheMacroToRun

Skip,
[sub]
[red][/red]
[/sub]

 

[mattj63]

I didn't explain well. I have a macro in the button_click() procedure. I want to execute the button_click() procedure from another macro instead of having to reproduce the code again. (although I suppose that could be done, but not efficient).

Matt

 

[SkipVought]

sub button_click()
   'some code runs here
end sub

    Windows("Form-SampleSubmit.xls").Activate
    button_click

I would not recommend that. Instead...

sub TheMacroToRun()
   'some code runs here
end sub

sub button_click()
   TheMacroToRun
end sub

    Windows("Form-SampleSubmit.xls").Activate
    TheMacroToRun

Skip,
[sub]
[red][/red]
[/sub]

 

[mattj63]

Makes sense. I'll give it a try - Thanks

 

[fumei]

Not sure what procedure is firing to try and fire the commandbutton, but why can you not just call it?

Sub WhateverIsTryingToFireButton()
    CommandButton1_Click
End Sub

Maybe that does not work in Excel...I'm a Word guy, and that certainly works in Word.

Gerry
My paintings and sculpture

 

[GlennUK]

Or if you really really want to press the buttons:

        Worksheets("Sheet1").OLEObjects("CommandButton" & x).Object.Value = True

x being a number of course.

Cheers, Glenn.

Did you hear about the literalist show-jumper? He broke his nose jumping against the clock.

 

[combo]

If you want to directly call button's procedure, mind that sheet's module is close to class module, so:
- make the sub public first,
- call it as a method, i.e.(Sheet1 is a code name): Sheet1.CommandButton1_Click

combo

 

[mattj63]

Thanks Combo. Yours worked. I had been putting a period before the 'click'. At various points I had tried "public" as the procedure type and '_' in the calling routine but never together.

Working code is:

'In Sheet1
Public Sub cmdFill_List_Click()
    'stuff
End Sub

'In Module2
     Sheet1.cmdFill_List_Click

Thanks