Tag: Forms

Excel and Windows Dialogs

8 April 2006 by Ross McLean

Excel allows us to get hold of many builtin dialogs via it’s object model. For example:

[vba]
Sub OpenDialog()

Application.Dialogs(xlDialogOpen).Show

End Sub[/vba]

Shows the open file dialog. More infomation can be found here:

http://support.microsoft.com/?kbid=213371#E6ACAAA

A lot of the time, what Excel is doing is using API layers to get hold of these diologs (i.e. the common dialogs), it then allows us to use them with a few lines of code. But If you can’t get the fuctionality you want from the Excel object, you should be able to find an API method. Here’s a SaveAs one for example:

http://www.mvps.org/access/api/api0001.htm

Note: you will need to change the line “If IsMissing(hwnd) Then hwnd = Application.hWndAccessApp” to “If IsMissing(hwnd) Then hwnd = Application.hWnd” to get it to work, and some of the filters to get it to function as you like in Excel.

Tags: , , ,
Categories: Code, General • No Comments »

Spell checking in user forms

6 March 2006 by Ross McLean

UPDATE!

If you downloaded the example file, you may have noticed that it didn’t quite work properly – somehow a line of code had gone missing – I’ve updated the example file now so it should work ok.

———————

I have a report file I send to colleges, I don’t want it to look like “just another spread sheet”, so I’ve put some strong formatting and images in, when I’m ready I send it out as a PDF. It’s quite nice a looks good. There is a bit of a problem. Part of the report is made up of a set of merged cells, I used this space to add a few sentences, but typing like this in Excel is a bit of a pain.

To make the process a bit simpler I knocked up a simple user form, in which I can type in a more naturally way, I can spell check it right there in the form and them send the whole lot back to an excel range, cool!

I think this implementation of the spell checker falls somewhere between the one suggested by Dick, and Jons [see here].

Anyway here is a demo file.

Tags: , , ,
Categories: Code • 3 Comments »

Moving a captionless userform

12 February 2006 by Ross McLean

UPDATE:
Check Out Andy Popes code, a much better method of moving a userform with out a caption:

Here is some code to move the form around without the need for API coding.
Create a couple of private variables in the userform to remember the position when the left mouse button is pressed.

[vba]
Private m_sngX as single
Private m_sngY as singlePrivate Sub UserForm_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If Button = 1 Then
m_sngX = X
m_sngY = Y
End If
End Sub
Private Sub UserForm_MouseMove(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)

If Button = 1 Then

Me.Left = X + Me.Left – m_sngX
Me.Top = Y + Me.Top – m_sngY

End If
End Sub [/vba]
Orginal post

Yesterday I was playing around with Jan Karel Pieterse Watch Other Cell tool. I wanted to make the user form captionless and transparent. That’s easy enough to do but a problem arose. With the caption removed the form could not be moved.
One work around was to use the forms mouse move event and an API Call to hook the cursor location. Another API call is made to reposition the form. The Down and Up events are used to “turn on” weather or not we what to move the form. For the user it is much the same as a normal move process.

The API routines are included in the example file attached. If your thinking, “why not just use mouse X, Y and top and left?”, it’s because the API’s returns cursor position and will place the form with in your systems co-ordinate structure, not just excels. The results are more reliable.
There is one problem still outstanding with this method. When you first click the form to reposition it the form “jumps” to the places where you clicked the mouse. If it’s near the top left corner, then the result is hardly noticeable, it’s a different story down in the right hand corner. I think code could be written to correct for this but maybe you could simply put in an icon near the top left to allow the move?

Enjoy!
Moveable Captionless Userform

Tags: , , ,
Categories: Downloads, UI Design • 3 Comments »

Captionless worksheets

22 January 2006 by Ross McLean

Big Dick Kusleika over at Daily Does of Excel made some suggestions for Excel 12. One of which was to have captionless worksheets.

The following example sort of does it, it’s a modification of Steven Bullen’s and Tim Clem’s Form fun class.

I didn’t spend very long on this as it’s not really of any use to me, I just wondered if it could be done. To use it in anger you’d have to tightly control the windows index and there protection settings I’d guess. And what about adding transparency?

DOWNLOAD .XLS

Tags: , , , ,
Categories: Code, General, UI Design • 4 Comments »

In-Line Help Methods, some examples

15 January 2006 by Ross McLean

Following a question over at JMT forums, I have literally thrown together some examples of methods for displaying “in-line” help.

By “in-line” I mean the sort of help that’s displayed right next to the area where it’s needed/used. I don’t believe any of these methods should form the base for a well structured help system, and some boarder on the silly. Still, there is clearly a place for such in-line help.

The original question made reference to the “chunkiness” of user forms. I personally quite like that chunkiness. Always keep in mind that it’s the quality of what’s said not only how it is presented!. Be very clear. List each and every step required. Be concise.

Enjoy! – Example WorkBook

Tags: , , ,
Categories: Code, UI Design • No Comments »

Newer Entries »