How to tell a UserForm button has been clicked (XL365)?

[EdNerd]

Several years ago, I designed an macro-laden Excel file for my work section. I then created a "toolkit" workbook with multiple macros to enable the users to fix these files, rather than piling them up in my lap. %-/

The macro I'm currently working on requires the user to close all Excel files (except the toolkit) before the macro continues. Unfortunately, I can't guarantee that will be done prior to initiating the macro. So I thought to open a UserForm with instructions to close all other workbooks, then click a button on the form when this is accomplished. I'm using a form because that would allow the users to interact with the other XL files, whereas a Message Box would not. I also need to include a Cancel button in case they are not ready to close a certain file.

My problem: I am drawing a blank on how to detect when and which form button is clicked to allow the rest of the macro to continue to run. Everything I can think of seems very convoluted -- which tells me I've got the wrong answer! If someone could drop-kick me in the right direction, I would be very grateful.

 

[6StringJazzer]

Each button on a UserForm has a series of events that code can detect, including the click event. For a button called CommandButton1 you can write a Sub called

Private Sub CommandButton1_Click()

that will be called automatically when the button is clicked. Implement this for each button with the desired code.

 

[EdNerd]

Thank you for responding. I know about the Click events, but I need to keep the actual code inside the workbook. There are several sub-routines that are already there that I will need to call in the course of running this macro.

What I'm looking for is an easy way to use the Click event to communicate back to the workbook-based code that called the user form, and tell that code which way to proceed??

 

[6StringJazzer]

I think I see what you are getting at.

Typically this is done with a public variable at the module level of the UserForm code:

Public Result As Long
Public Button1Click As Long

Private Function UserForm_Initialize()
   Button1Click = 1
End Sub

Private Function Button1_Click()
   Result = Button1Click
End If

And used like this:

UserForm1.Show
If Result = UserForm1.Button1Click Then
    ' Do something here

 

[EdNerd]

Trying to work this out, but coming up with a weird response. (Or maybe it's just been so long since I've worked with Forms that I forgot this "feature"!)

I open the form using .Show vbModeless - that should put the form on top and allow me to interact with other workbooks -- yes??
But when I close a different workbook (not the one with the user form), the original wkb and the form close - but the second file (the one I wanted to close) stays open??

Head scratching .....

 

[6StringJazzer]

No way to diagnose without at least having all your code. Entire file would be better.

BTW my solution was for a modal form. If you Show a modal form, the code does not continue execution until the form is closed. That when my code checks the button status. If you Show a modeless form, the code keeps executing. You would need a completely different solution to detect what button was pressed. I would need to understand more about the code that needs to determine this

 

[EdNerd]

Sorry for the delay in replying. Had to take an emergency day off yesterday.
I decided (mostly due to my inexperience in dealing with forms and passing info) to go another route. Using the form got too confusing, when all I really wanted to do was give the user an opportunity to say "No, I'm not ready to have all my other files closed!" A MsgBox was much easier!

Thank you for trying to help me out. My bad for not being more up to speed.