Call command buttons from userform

[pwill]

Hi can anyone help?

I am using a userform and have added a Command button within the form and I want it to call CommandButton1 on Sheet2 that in turn calls other CommandButtons for other sheets.

Is this possible?

I get a compile error saying 'Sub or function not defined' and highlights 'Call CommandButton1_Click'

ie

Private Sub btnUpdt_Click()
Dim dts As Long
Dim i As Long

    dts = Me.tbAdddts
        
    For i = 1 To tbAdddts
        Call CommandButton1_Click
    Next

any help would be apprieciated

Regards

pwill

 

[ranman256]

you are actually calling the macros the command button runs, so just call the macro,
IF CommandButton1_Click is the macro name then just say:

CommandButton1_Click
CommandButton2_Click
CommandButton3_Click

the CALL is not needed.
AND the macro must be public:

Private sub CommandButton1_Click
will not run if it is in separate modules.
If all macros are public, there should be no errors.

 

[pwill]

Thanks ranman for your reply,
that doesn't seem to help?

what I have is command buttons on Sheet2 that have code assigned to them and commandbutton1 calls them in sequence. I have made a userform and added a textbox for inputting values and a commandbutton on the userform to call the commandbutton on Sheet2 the number of times equal to the textbox on the form. the commandbutton1 on Sheet2 runs fine from the actual sheet and calls all the other buttons in order but I can't get the userform to call the commandbutton1 on Sheet2?

Here's what i have for the commandbutton1 on Sheet2

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False

Call CommandButton2_Click
Call CommandButton3_Click

Application.ScreenUpdating = True

End Sub

And here's what I am trying to achive on the UserForm

Private Sub btnUpdt_Click()

Dim ws As Worksheet: Set ws = Sheet02
Dim dt As Long
Dim i As Long
     
    dt = Me.tbAddUpdts 'date = Me.textboxAddUpdates
        For i = 1 To dt
            Call CommandButton1_Click 'Private Sub CommandButton1 Sheet2
        Next

End Sub

I can have this line of code that works fine using a commandbutton on Sheet2 but I'm trying to get it to work from the UserForm

dt= InputBox("Enter No. of dates to Update")
        For i = 1 To dt
            Call CommandButton1_Click
        Next

Any further help would be apprieciated

regards

pwill

 

[Norie]

You should put the code for the command button on the worksheet into a separate sub in a standard module, then you can call it when you click the button and from the userform.

 

[pwill]

Thanks Norie,
I was hoping to avoid doing that but if that's the only way I will use that option. .

pwill

 

[Norie]

Why would you want to avoid doing that?

Putting the code in a separate sub means you can call it from anywhere when required, having it in the Click event of the command button prevents that.

 

[ranman256]

you dont call command buttons, you call macros.

 

[pwill]

Thanks ranman256 and Norie for all your input, much apprieciated.

I managed to solve it by adding the macros for each commandbutton on sheet2 into a new module and then replacing:

Call CommandButton1_Click

with:

Application.Run "Module1.CommandButton1_Click"

dt= InputBox("Enter No. of dates to Update")
        For i = 1 To dt
            Application.Run "Module1.CommandButton1_Click"
        Next

Works fine and have kept them as 'Private Subs'

Regards

pwill