Macro button invisible

[saiganesh]

Hi - I have a macro, which is assigned to a macro button that is placed on the reviewing toolbar. For your information, I have placed a new macro button on the reviewing toolbar. The macro button works fine and performs the action I desire for.

When I shared the workbook with my friend, he could not see the same macro button. I think this is a silly question. I am sure I did miss something. I am amateur in dealing with these buttons and macros.

Please help me understand why the button is not visible to others, while it is very much visible to me. My friend, however, is able to see the macro code in VB editor. He pressed Enable macro buttons also, while opening the workbook. FYI, no security protection is enabled for the workbook.

Any help on this is much appreciable. Thanks.

Regards
Sai

 

[saiganesh]

Guys, please help...

 

[Cindy Ellis]

The buttons on the toolbar aren't stored in the macro or with the workbook. If you are using Excel 2003 or earlier, your colleague can simply add a similar button to their reviewing toolbar and link it to the macro.
What version of Excel are you using? For Excel 2007 (and probably 2010, but I haven't used it yet), buttons go on a "ribbon" bar that isn't so easy to manipulate. If you're using one of these newer versions, you could create the new button as a custom Add-In, and distribute that with the macro.
Hope that helps,

 

[saiganesh]

Thanks much, Ma'm. I never ever knew that the placement of a button on the toolbar is applicable only for the user who placed the button. I am using Microsoft Excel 2003. And I want the button to appear in MS Excel 2007.

Ma'm, the main requirement is to make the button visible to anyone who opens up this workbook. By any chance, is there a way to create and place the button on the toolbar upon the opening of the workbook? The macro I wrote is a simple one, pasted it here under. Infact, I googled and found out this code, not mine!!!

If ActiveSheet.Name = "List Of Values" Then
FinalRow = Range("A65536").End(xlUp).Row
With Cells.Range("A1:L" & FinalRow)
.BorderAround xlContinuous, xlThin, xlColorIndexAutomatic
On Error Resume Next 'used in case there are no inside borders
With .Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With .Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
End With
Range("A1:L" & FinalRow).Select
Selection.Sort Key1:=Range("D1"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

For Each Cell In Cells.SpecialCells(xlCellTypeConstants, xlTextValues)
If Left(Cell, 2) = " " Then Cell.ClearContents
Next Cell

wb.Save
End If

Ma'm, can you please help me find out a way to have the button appear to anyone who opens the workbook either in MS Excel 2003 or 2007? And on click of the button, the above code should trigger. Please help.

Regards
Sai

 

[Cindy Ellis]

First, no need to call me Ma'am...Cindy is just fine.
Now, on to more serious things. Excel 2007 doesn't provide a simple way to add a button to a toolbar. You have to create xml code completely outside of Excel's Visual Basic Editor, then link it to a macro that does the work. I've done it for a complex application...it wasn't simple, but for a single button it's not too bad.
If you want to give it a try, take a look at http://www.rondebruin.nl/ribbon.htm.
If it seems intimidating...it is at first, but if you take it step by step you can do what you need to do. If you choose to give it a try, I'll try to help, but I'm not an expert in it, just someone who doesn't get intimidated by software (usually).
If you want an alternative, consider adding a macro button to the worksheet itself.
Hope that helps,
Cindy

 

[saiganesh]

Thanks Cindy for a useful link. I will indeed give a try. At the moment, adding a button to the worksheet would be ideal for me to complete my task. Thanks again for providing proper guidance to an amateur.

Regards
Sai

 

[Domenic]

This might be another way. Place the following codes in your workbook, and a custom button will automatically be added to the "Reviewing" toolbar (or Toolbar Commands in the Add-Ins section for Excel 2007 or later version) when the workbook is opened, and automatically removed when closed.

1) Place the following code in a regular module (Insert > Module)...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] CreateMenuItem()

    [color=darkblue]Dim[/color] Btn [color=darkblue]As[/color] CommandBarButton
    
    [color=darkblue]Call[/color] RemoveMenuItem
    
    [color=darkblue]Set[/color] Btn = Application.CommandBars("Reviewing").Controls.Add(Type:=msoControlButton, temporary:=True)
    
    [color=darkblue]With[/color] Btn
        .Caption = "New Button"   [color=green]'Change the name as desired[/color]
        .OnAction = "MyMacro"     [color=green]'Change the name accordingly[/color]
        .FaceId = "59"
    [color=darkblue]End[/color] [color=darkblue]With[/color]

[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Sub[/color] RemoveMenuItem()
    [color=darkblue]On[/color] [color=darkblue]Error[/color] [color=darkblue]Resume[/color] [color=darkblue]Next[/color]
    Application.CommandBars("Reviewing").Controls("New Button").Delete
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

Sub MyMacro()
    MsgBox "Hello..."   [color=green]'Replace with code for your macro[/color]
[color=darkblue]End[/color] Sub
[/font]

2) Place the following code in the module for 'ThisWorkbook' (right-click 'ThisWorkbook', and select 'View Code')...

[font=Courier New][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_BeforeClose(Cancel [color=darkblue]As[/color] [color=darkblue]Boolean[/color])
    [color=darkblue]Call[/color] RemoveMenuItem
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

[color=darkblue]Private[/color] [color=darkblue]Sub[/color] Workbook_Open()
    [color=darkblue]Call[/color] CreateMenuItem
[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]