Saving excel macro based on commandbutton1 click (user form)

Dear all,

I am trying to write macro which would start when open the workbook, then the user make necessary changes and macro will start again after close + save the workbook.

Please find a dummy code below:

Main:

Private Sub Workbook_Open()

UserForm1.Show

End Sub

Userform1:

Private Sub CommandButton1_Click()
Range("A1").Value = "You"
End Sub

Private Sub CommandButton2_Click()
Range("A1").Value = "I"
End Sub
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
     
Dim ws As Worksheet
Dim pwd As String

pwd = "Hi" ' Put your password here
For Each ws In Worksheets
    ws.Protect Password:=pwd
Next ws
     
End Sub

I am struggling with the last part. I need to run different macro when exiting and saving the workbook, depending on Userform CommandButton choice at the begining.
Is there a way to do it?
Thanks a lot!

Hi all!

I though about the last part and came up with something like below:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 If UserForm1.ActiveControl.Name = "CommandButton1" Then
 MsgBox "1"
 Else
 MsgBox "2"
 End If
 End Sub

How properly write this part in order to work? At the moment it does nothing... Any thoughts?
Thanks!

It looks like you can just check the "A1" value, no? If so, you could just use:
If ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = "You" Then
Application.Run "Macro1"
Elseif ActiveWorkbook.Sheets("Sheet1").Range("A1").Value = "I" Then
Application.Run "Macro2"
End If

Or if you prefer checking the ActiveControl property, just swap out the conditionals.
Private Sub Workbook_BeforeClose(Cancel As Boolean) might be appropriate as well.

Hope this helps!

Hi Aule,

Thanks for a reply! However, I can't check values because there will be no values. The one posted is just the "dummy" code. Basically, I need a macro to remember which button was clicked and then act accordingly depending on that when saving an excel workbook.
Could you please clarify remove the conditions for me? If i will remove how to force macro to work according different button clicked?

Thanks a lot!

Thanks a lot! It did a trick. I didn't even know about very hidden