Button Click on Spreadsheet commanded by Button Click on Userform

Hi Guys,

So I have code within a button on a Spreadsheet(Production Assign). I want this code to run after a button is clicked on a Userform(Monitors Office Entry). The button on the userform inserts data into the spreadsheet, and the button on the spreadsheet makes calculations according to the inserted data and sends an email.

I have tried this code within the MonitorsOfficeEntry_Click():

Please Login or Register  to view this content.

Any feedback on what I can do to make this work would be great. Thanks.

Cool, I have attached the file. What I would like to achieve is for the "Assign Batch Record" button on the "Monitors Office Entry" userform, to activate the "Send Email" button on the "Production Assign" spreadsheet.
Likewise, the "Assign Batch Record" button on the "Production Office Entry" userform, to activate the "Send Email" button on the "QA Assign" spreadsheet.
And,the "Assign Batch Record" button on the "QA Office Entry" userform, to activate the "Send Email" button on the "QP Assign" spreadsheet.

Thank you in advance if you can find a solution.

I think that is what I tried to do. I inserted the code I have above in my first post into the click event on the userform, but I just keep getting an error when trying to run it

Wow...and its as easy as that haha. Thanks very much Norie

Norie, i have 3 separate spreadsheets, one button on each spreadsheet, I put my code in to each button and they all work perfect, then I go to my userforms (there are 3 userforms that interact with their appropriate spreadsheet) and I put in the code

Please Login or Register  to view this content.

But I receive compile error: Sub or Function not defined. Have you any idea why?

Apologies for what could be silly questions, but I am a very very novice programmer trying to make a project work.

I did not create a macro, I just created an ActiveX button and placed my code inside the view code section.
This is on the spreadsheet. I click the button on the spreadsheet and it carries out my task.
It says Private Sub.

The user will not have access to the spreadsheets, this is why I want to activate the button from the userform.

I have tried literally removing it and that did not work, is there another way I am supposed to take it off of private? Am I supposed to replace it with "Public"?

Cool. Any questions about it don't hesitate to ask.

Ok so just to try get my head around that, What I should do is:
Create Module 1, create Module 2, create Module 3.
Put the exact same code I have in my CommandButton1_Click code into Module 1, and same for cmd2 and 3 with mod 2 and 3?
And then use the code you posted above to call it? or do I make it call the module somehow?

Ok just to try get my head around it, I should:
Create Module 1, Module 2 and Module 3?
Copy and paste the exact same code from the command buttons 1,2 and 3 into the respective modules?
Use your code above then to make that work? Or is there a different way to the module maybe?

Thanks for your help Norie.

The 2,3 refers to CommandButton2_Click and CommandButton3_Click that are in the respective userform button_click code.
I have Sub CommandButton1 , 2, and 3 all in the one module now.

OK to explain what is happening:
There are 3 Offices that will use this function on the userform; the Monitors Office Entry, Production Office Entry, and the QA Office Entry. Each of these 3 userforms has the same layout. Each userform interacts with a separate spreadsheet.
On the userform, the user will complete the Textbox and Comboboxes, when they click the "Assign batch record button", the results from the userform go into the appropriate spreadsheet(depending what Entry userform the user is on) and preform a calculation, that in turn will choose a name from one of 3 tables.

What the button on that spreadsheet then does, is send an email, move the name on that particular table down the list and a new name on top, and also paste the value from the Textbox value and the Chosen name from the table in another spreadsheet(again depending what spreadsheet the button is being clicked on, will decide what spreadsheet that data gets pasted to).

I'm still having no luck calling a click on a spreadsheet button from a userform button click :'( why is this proving so difficult!

Do I put the Userform Button click code into the module with the Spreadsheet Button click code?

Was just looking at the difference between a standard and class module there, and maybe it is a class module I should be using as I want it to do 3 separate clicks on different sheets?

I have tried the above...I even tried recording new macros on each spreadsheet for each click, and then tried to just call those macros from the userforms...but no no luck with that either.
Haha this function of my program has me stressed as feck

No not exactly the same.
The bulk of the code is all the same for the calculations on the spreadsheet, but each button sends information from its spreadsheet to another spreadsheet.

So, cmd btn1 does:

Please Login or Register  to view this content.

cmd btn2 does:

Please Login or Register  to view this content.

cmd btn3 does:

Please Login or Register  to view this content.

Yes, each userform button interacts with a separate sheet.

C26, A1 and A26 are all on the spreadsheet of which ever button is been pressed. Keep in mind each spreadsheet with the buttons I am trying to click are all the exact same, except in the code they copy the information from A1 and A26 to different sheets again.

Yes they are.
And depending what user form is used(to click the button) will decide what worksheet is interacted with.

Well from the "Monitors Office Entry" userform button - "Assign Batch Record", I want to call the "Prod Assign" spreadsheet button- "Sub CommandButton1_Click()", and then when that button is clicked it will carry out the sub Iterate code. (This code decides which table out of 3 tables to choose, and then from what ever table is chosen it will take the name from the top of the table to assign the batch record to. And that is the information that is sent to the "Production Office" spreadsheet then- the name and the batch record number.)

And the same then for the "Production Office Entry" userform and "QA Office Entry" userform, that also have "Assign Batch Record" buttons that will interact with "Sub CommandButton2_Click()" on "QA Assign" spreadsheet and "Sub CommandButton3_Click()" on "QP Assign" spreadsheets.