Creating buttons in Excel to run Macro?

Question

So, I am creating a tracking system for work in excel.

I have created macro's to run, there are two - One (Macro1) which initiates the initial event (the object being sent), saving it into the spreadsheet.
Two (Macro2) which I want to come up when the object is returned.
I have managed to create a button on the spreadsheet to prompt Macro1 to open.
However, for Macro2, I want a button to be generated after the information from Macro1 is entered into the spreadsheet in a cell beside the row the information was entered onto (ie. Macro1 enters it's information in row 4, I want the button to appear in cell N4 after this). The button is to initiate Macro2. Is this at all possible?

If this IS possible, after the macro is run, I want that particular row (in this example,row 4) to change colour based on the command buttons in the macro. But I want it to affect only this row. One must turn green, one must turn red. Will I need individual macros for each row?

Thanks for any help! :-)

Answer 1

To begin, objects (buttons, etc) do not reside 'in' cells, but float above the grid. You can size and position your button to appear as if it is actually in the cell, but it is not.



The following example assumes the buttons are Forms control buttons named Button 1 and Button 2. If your button numbers are different, modify the example code line as needed.



You would put this line of code just before 'End Sub' in Macro 1:



ActiveSheet.Shapes("Button 2").Visible = True



This will display Button 2 on the worksheet.



Button 2 can then be pressed to run the second macro.



At this point, you have made Button 2 visible and called the macro assigned to it. Now what? Do you want to leave the button visible? If so, you are good to go. If not, then you might want to place this line at the very end of macro 2:



ActiveSheet.Shapes("Button 2").Visible = False



With regard to updating the fill color in the row(s), will the red and green be applied to different rows based on user input or the result of conditions being met? If so, you will need to clear all fill colors before you apply the green and red each time. Otherwise, your worksheet will begin to fill up with red and green stripes. Of course, if that is what you want... no problem.



Here is the code line that would fill row 4 with Red:



ActiveSheet.Range("4:4").Interior. ColorIndex = 3



To clear the fill color in row 4:



ActiveSheet.Range("4:4").Interior. ColorIndex = xlNone



Or, for the entire worksheet:



ActiveSheet.Cells.Interior. ColorIndex = xlNone



For a list of the color index numbers:



http://dmcritchie.mvps.org/excel/colors.htm



If you need additional assistance, please drop a note to [e-mail]

Answer 2

I would recommend creating Button2 and setting his Visible to False.

Then the last line of Macro1 have him set button2 Visible to True. Then I assume when Macro 2 runs, you will want to set the button2 back to Visible False.



No you don't need separate macros to change font color. you can do that directly in macro 1 or macro 2. Just use the range select command on the cell in question and perform and do a With Selection, .color =

Answer 3

Click INSERT, then OBJECT. Click the CREATE tab. Browse to where you have the macro stored. Click "display as icon".