Excel - A macro to record button

October 2017



Issue


I would like to create a command button "Record" in an Excel file.
When someone click on the button , the macro takes the cell where the person has entered his name and cell containing the respective date and save the data in a workbook.

Solution

  • Open your workbook
  • Open the VBA Alt F11
  • Double left click on ThisWorkbook in the top left
  • Right click in right window


Copy the code below in this window

Sub Macro_Enregistrement()   
Dim Nom_Fichier, Chemin, Reponse   
'A definir   
Chemin = "C:\............\"   
'A1 et A2 a changer suivant vos besoin   
Nom_Fichier = Worksheets("Feuil1").Range("A1") & Worksheets("Feuil1").Range("A2") & ".xlsm"   

retval = MsgBox("Voulez- vous enregistrer ce fichier: " & Nom_Fichier & " ?", vbYesNo)   
If retval = 6 Then   
Reponse = "Fichier " & Nom_Fichier & " sauvegardé"   
ActiveWorkbook.SaveAs Filename:=Chemin & Nom_Fichier, _   
FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False   
Else   
Reponse = "Fichier non sauvegardé"   
End If   
retval = MsgBox(Reponse)   
End Sub   
  • Create a button and assign the macro.


Thanks to f894009 for this tip.
Published by aakai1056. Latest update on August 19, 2012 at 05:33 AM by deri58.
This document, titled "Excel - A macro to record button," is available under the Creative Commons license. Any copy, reuse, or modification of the content should be sufficiently credited to CCM (ccm.net).