Create a custom Userform Interface with Visual Studio

GreedyGreedo

New Member
Joined
Aug 9, 2017
Messages
1
Hi, I'm creating a project in Excel, programmed in VBA, but I've reached a limit of my understanding, so I thought I'd post for help.

Essentially I'm trying to create one of these. In case the link breaks/ to make it relevant to other users: what I'm trying to create is a GUI with some slightly oddly shaped elements (frames with curved edges, composite shapes like a toolbar etc.). I do not want the standard Userform container design/rectangular shape either.

The complication is that I would like to make these elements raise the same VBA events as command buttons do on userforms. That is, I can click, double click or hover-over (mouse-move) and raise the appropriate events. Now there are many convoluted ways to make these odd-shaped command buttons, here are a few I've explored:


  • Draw the individual elements as Shapes and assign macros
    • This gives a click event as desired
    • However in order make something like a frame, with a non-clickable centre, I have to draw non-joined freeform shapes (very hard to get to look right - I've been experimenting with svgs too), or split up into four overlapping subshapes (still not perfect)
    • This doesn't give mouseMove events
  • Draw shapes then plaster with invisible activeX buttons
    • This is not a simple procedure, because the complexity of the shapes like the frame (with rounded edges) means that to try and get the right button-click area, I have to use loads of command buttons
    • I then either need to create a whole load of classes to handle the button press events, or handle the events individually
  • Use api calls to detect button clicks
    • Clearly this will add complexity

After exhausting those, I realised what I really want to do is turn to a better GUI designer (like VisualStudio I'm guessing) to create either


  • Individual custom-design button elements for each of the components of my GUI
    • I would assemble these all in the right places within Excel
  • A single project in VSTO which I can export to Excel and, hopefully, open up like a userform, in a separate borderless window or directly in the sheet

As I think this would be much more self contained, much easier to maintain, less memory demanding to run (as there's only 1 clickable element raising events rather than 20), the only real drawback I see is that it's slightly less portable (requires a separate file to be installed probably), but I'm sure I can work with that.

So far I've looked at/been directed towards


  • Windows forms
    • These still have the container constraint, I just want my elements individual or floating in space (correct me if I'm wrong)
  • WPF App
    • These look like I won't be able to control them with VBA easily (require command line parameters, hard to get anything back from them)
  • Excel Addin
    • I'm not sure about these, I've only been able to find tutorials on Toolbar-Addins, not standalone graphical addins like I want

I am also intrigued by the idea of a custom activeX control, but that's just a hunch based on the name, I haven't been able to find tutorials on this at all so I don't know what's possible.

Right, sorry for that big post, if anyone has any experience with Visual Studio/ thinks this is possible in any other way that I haven't given, please give me whatever pointers you may have, direct me to tutorials, tell me what VS template I should start with, anything! Unfortunately I know so little about visual studio that I can't even google this :p


PS, if I've posted this in the wrong forum, then feel free to move it/ tell me (how) to do so. I gather that Excel specific questions are not meant to be here, but really I'm after an Excel-related Visual Studio solution, which appears to match this forum quite nicely.

PPS I know I should probably just skip Excel out altogether, but this preview GUI is a small component of a much larger project that is already thoroughly grounded in VBA/Excel, so it's too late to change!
 

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

Watch MrExcel Video

Forum statistics

Threads
1,129,542
Messages
5,636,912
Members
416,950
Latest member
soychristophe

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top