VBA Windows 7 style buttons

Question

I'm pretty sure this question has been asked a lot around the web and I've read a lot of the questions and their "answers" on several forums but I've never seen a clear answer so I'd like to know:

Is is possible, to use Windows 7 style buttons

in Excel VBA or do I have to use these grey things looking like they come from

?

I dont want to use images, I mean importing these "ActiveX Controls", I think thats their name.

Answer 1

I am unaware of a solution where you can make use of the "Windows 7 style buttons". Yet, I would like to note that programming does not require you to use the "Developer" tab exclusively. In other words: just because you want a button doesn't mean that you have to use just that from the Developer tab. In fact, almost any shape within Excel can be assigned a macro.

The easiest way to get a button similar to the "Windows 7 style button" is to Insert a Rectangle or a Rounded Rectangle from the Shapes menu. The "fancy" grey coloring can be easily achieved when you click on that shape and then on the Format tab for that shape select one of the predefined grey-scale shape style. These buttons look extremely similar to what you want and can be easily assigned a macro by right-clicking on these shapes.

Answer 2

Buckle up, you're in for a ride.

---

First, create a new C# (or VB.NET.. whatever rocks your boat) class library, and add a new WPF UserControl, and design your UI:

<UserControl x:Class="ComVisibleUI.UserControl1"
             xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
             xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
             xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006" 
             xmlns:d="http://schemas.microsoft.com/expression/blend/2008" 
             mc:Ignorable="d" d:DataContext="ViewModel1"
             d:DesignHeight="200" d:DesignWidth="300">

    <Grid Background="White">

        <Grid.RowDefinitions>
            <RowDefinition Height="*" />
            <RowDefinition Height="32" />
        </Grid.RowDefinitions>

        <TextBlock Text="actual content here" Foreground="DimGray" HorizontalAlignment="Center" VerticalAlignment="Center" />

        <StackPanel Grid.Row="1" Orientation="Horizontal" HorizontalAlignment="Right" Margin="2">
            <Button Width="128" Command="{Binding Command1}">
                <TextBlock Text="Button1" />
            </Button>
            <Button Width="128" Command="{Binding Command2}">
                <TextBlock Text="Button2" />
            </Button>
        </StackPanel>

    </Grid>
</UserControl>

Build the project.

Then, add a new Form, dock a WPF Interop ElementHost control, and you should be able to add your WPF UserControl1 (whatever you called it) as the hosted WPF control.

The WPF control uses data bindings to hook up Command1 and Command2 (and everything else, really - read up on the Model-View-ViewModel pattern), so you'll need a class to implement the managed code part. If your logic is all VBA then this should be pretty slim:

public class ViewModel1
{
    public ViewModel1()
    {
        _command1 = new DelegateCommand(ExecuteCommand1);
        _command2 = new DelegateCommand(ExecuteCommand2);
    }

    private readonly ICommand _command1;
    public ICommand Command1 { get { return _command1; } }

    public event EventHandler ExecutingCommand1;
    private void ExecuteCommand1(object parameter)
    {
        ExecuteHandler(ExecutingCommand1);
    }

    private readonly ICommand _command2;
    public ICommand Command2 { get { return _command2; } }

    public event EventHandler ExecutingCommand2;
    private void ExecuteCommand2(object parameter)
    {
        ExecuteHandler(ExecutingCommand2);
    }

    private void ExecuteHandler(EventHandler eventHandler)
    {
        var handler = eventHandler;
        if (handler != null)
        {
            handler.Invoke(this, EventArgs.Empty);
        }
    }
}

A DelegateCommand is a very nice little thing that's all over Stack Overflow, so don't hesitate to search if you have any questions:

public class DelegateCommand : ICommand
{
    private readonly Action<object> _execute;
    private readonly Func<object, bool> _canExecute;

    public DelegateCommand(Action<object> execute, Func<object,bool> canExecute = null)
    {
        _execute = execute;
        _canExecute = canExecute;
    }

    public event EventHandler CanExecuteChanged;
    public bool CanExecute(object parameter)
    {
        return _canExecute == null ? true : _canExecute.Invoke(parameter);
    }

    public void Execute(object parameter)
    {
        _execute.Invoke(parameter);
    }
}

The WinForms form will need to assign the WPF control's DataContext - expose a method to do that:

public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }

    public void SetDataContext(ViewModel1 viewModel)
    {
        hostedWPFControl.DataContext = viewModel;
    }
}

Other than that, there shouldn't be any code in here.

---

WPF likes the MVVM pattern, WinForms likes MVP (lookup Model-View-Presenter). The WPF part being hosted in WinForms, we'll make a presenter - that's the object the VBA code will use:

[ComVisible(true)]
public interface IPresenter1
{
    void Show();
}

Yes, that's just an interface. Hold on, we need another:

[InterfaceType(ComInterfaceType.InterfaceIsIDispatch)]
[Guid("18F3B8A8-EC60-4BCE-970A-6C0ABA145705")]
[ComVisible(true)]
public interface IPresenterEvents
{
    void ExecuteCommand1(object message);
    void ExecuteCommand2();
}

The IPresenterEvents interface is your "event sink" interface, that the VBA code will need to implement, but I'll get to it. First we need to implment the actual presenter:

public delegate void Command1Delegate(string message);
public delegate void Command2Delegate();

[ComSourceInterfaces(typeof(IPresenterEvents))]
[ClassInterface(ClassInterfaceType.None)]
[ComVisible(true)]
[Guid("FAF36F86-7CB3-4E0C-A016-D8C84F6B07D7")]
public class Presenter1 : IPresenter1, IDisposable
{
    private readonly Form _view;

    public Presenter1()
    {
        var view = new Form1();
        var viewModel = new ViewModel1();
        viewModel.ExecutingCommand1 += viewModel_ExecutingCommand1;
        viewModel.ExecutingCommand2 += viewModel_ExecutingCommand2;

        view.SetDataContext(viewModel);

        _view = view;
    }

    public event Command1Delegate ExecuteCommand1;
    private void viewModel_ExecutingCommand1(object sender, EventArgs e)
    {
        var handler = ExecuteCommand1;
        if (handler != null)
        {
            handler.Invoke("Hello from Command1!");
        }
    }

    public event Command2Delegate ExecuteCommand2;
    private void viewModel_ExecutingCommand2(object sender, EventArgs e)
    {
        var handler = ExecuteCommand2;
        if (handler != null)
        {
            handler.Invoke();
        }
    }

    public void Show()
    {
        _view.ShowDialog();
    }

    public void Dispose()
    {
        _view.Dispose();
    }
}

Now, go to the project's properties, and check that "Register for COM interop" checkbox, then build the project; in the [Debug] tab, select start action "Start external program", and locate the EXCEL.EXE executable on your machine: when you press F5, Visual Studio will launch Excel with the debugger attached, and then you can open up the VBE (Alt+F11), add a reference to the .tlb (type library) that you just built (you'll find it in your .net project directory, under \bin\debug\theprojectname.tlb, assuming a debug build), and that should do it.

There are a number of issues here, that I'll come back to fix later:

• The Dispose() method isn't exposed, and won't be explicitly or implicitly called at any point, which is... dirty.
• While everything seems like it's working from the C# debugger's point of view, I couldn't get the darn VBA handlers to run. That's probably a big problem if you intend to implement the logic in VBA, not just bring up the UI. OTOH you have access to .net code, might as well implement the presenter logic in the presenter itself, in C#/VB.NET, and then you don't need to get these event handlers to work.

Anyway, I've added this code to ThisWorkbook:

Option Explicit
Private WithEvents view As ComVisibleUI.Presenter1

Public Sub DoSomething()
    Set view = New ComVisibleUI.Presenter1
    view.Show
End Sub

Private Sub view_ExecuteCommand1(ByVal message As Variant)
    MsgBox message
End Sub

Private Sub view_ExecuteCommand2()
    MsgBox "Hello from WPF!"
End Sub

And when I run ThisWorkbook.DoSomething from the immediate window (Ctrl+G), I get this:

In theory (at least according to MSDN), that's all you need to do. As I said these event handlers aren't getting called for some reason, but hey, you get your shiny buttons! ...and all the power of WPF to design your UI now :)