Note that when you press the return key, a line is skipped and then you see “End Sub”.

I’m going to name my Macro “mySecondMacro”

In your VBE, type “Sub” then the name of your macro followed by two brackets.

There are many options when it comes to creating Macros, for now we will be doing them all this way.

You are now ready to start writing your Macro.Īll the Macros we will be dealing with for now will be created by typing “Sub”, then the Macro name, followed by two brackets”()”. Double click on your module name (first defaults to “Module1”).With the file open, go to your Visual Basic Editor (VBE) by pressing Alt+F11 together. The finished report will look like this….ĭownload this file, which you will be working from : Macros_Example_2Įxcel VBA Macros : How To Change Text In A Cell Create A Moduleīefore you start writing a Macro, you need somewhere to create it! In prior posts, we were working on a report that had to be formatted. The last post contained a lot of information and don’t worry if you feel like it was too much to take in at once – over the coming weeks we are going to build up the Macro one piece at a time. If you have not already read them, I would suggest going through them now before continuing. Recording your first Macro (and challenge question).OCellColorMonitor.Our series of posts on Excel VBA Macros began with these posts: Set oCellColorMonitor = New C_CellColorChange Cells(1, 1).End(xlDown).Offset(0, 2).Interior.Color = NewColor Cells(1, 1).End(xlDown).Offset(0, 1).Interior.Color = PrevColor Cells(1, 1).End(xlDown).Offset(1) = Cell.Address If MsgBox(MSG1 & Cell.Address & vbNewLine & MSG2, vbQuestion + vbYesNo) _ Public Sub CellColorChanged(Cell As Range, PrevColor As Variant, NewColor As Variant, Cancel As Boolean)Ĭonst MSG1 As String = 'You are trying to change the Color of Range : 'Ĭonst MSG2 As String = 'Do you want to go ahead ?' Private Sub Workbook_BeforeClose(Cancel As Boolean) Private oCellColorMonitor As C_CellColorChange , vCellCurColor(i + 1), bCancelĢ: Place the following code in the Thisworkbook Module : = vCellCurColor(i + 1)Ģ- This goes in the worksheet module where the cells are being monitored for color changes : Private WithEvents cmb As Office.CommandBars If you like this, I can try and improve it by using a timer which is a better approach.

VCellPrevColor(i + 1) = vCellCurColor(i + 1) ReDim Preserve vCellPrevColor(UBound(vCellCurColor)) VCellCurColor(i + 1) = vCellPrevColor(i + 1) If MsgBox(MSG1 & oCell.Address & vbNewLine & MSG2, vbQuestion + vbYesNo) _ If vCellPrevColor(i + 1) vCellCurColor(i + 1) Then Const MSG1 As String = 'You Changed the Color of Range : 'Ĭonst MSG2 As String = 'Do you want to restore the previous color ?'

Excel vba on change