How's your UI?

Lewiy

Lewiy

Well-known Member
Joined
Jan 5, 2007
Messages
4,284
I've recently been playing around with interesting custom built user interfaces (primarily in Access, but some in Excel too). These have included a full menu system on a form that mimics the standard windows top menu bars and some funky "sliding" forms that swipe across the screen when you click a button. These have all been built with inherant functions and controls (e.g. the menu system is a whole load of labels that appear/disppear at appropriate times, etc.)

Anyway, this got me thinking, "I can't be the only one who has spent hours creating these kinds of things". So my question to you all is, what are the best custom built user interfaces you have ever come up with in Excel or Access and how do they work?

This might help us all build nicer looking applications!!!
 

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce
As soon as I see the word 'funky' in relation to UI, I groan, hold my head in my hands, and then grab the Paracetamol ...
 
xld said:
As soon as I see the word 'funky' in relation to UI, I groan, hold my head in my hands, and then grab the Paracetamol ...
Click to expand...

I have to agree with you when it comes to final production but you can learn an awful lot by trying to do all these things. Then you pick the bits that actually add something to the design when you come to building a new interface and it can make it much easier (or perhaps more intuitive to someone non-technical) to use.

Regards

Nick
 
The coolest UI I ever built involved displaying "paired" electrical connector part numbers. I have a list of Male connectors, and a second list of Female connectors. I wanted to allow the user to select EITHER a single male or female connector from either list. But I also wanted the two lists to scroll "in lockstep" using arrow keys, or slaved to a single common scroll-bar. And also, the total number of connectors pairs is fluid and may change over time.

I ended up storing the two lists on a hidden sheet, under two named ranges. The code ends up clearing and reloading two Listboxes from the two named ranges accoridng to events.

Hard to 'splain. If anyone wants to play (it's actually pretty neat) here's the code:

Code: 
Private Const Display_Lines As Long = 10
Private L_1 As String
Private L_2 As String
Private Sub ListBox1_Click()
    Me.ListBox2.ListIndex = -1
    L_2 = ""
End Sub
Private Sub ListBox2_Click()
    Me.ListBox1.ListIndex = -1
    L_1 = ""
End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
    Select Case KeyCode
    
        Case 40 'down
            If Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 And _
                Me.ScrollBar1.Value < Me.ScrollBar1.Max Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value + 1
                    Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1
            End If
            
        Case 38 'up
            If Me.ListBox1.ListIndex = 0 And _
                Me.ScrollBar1.Value > 1 Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value - 1
                    Me.ListBox1.ListIndex = 0
            End If
                    
        Case 39 'right
            Me.ListBox2.ListIndex = Me.ListBox1.ListIndex
            Me.ListBox1.ListIndex = -1
            L_1 = ""
            Me.ListBox2.SetFocus
        
    End Select
End Sub
 
Private Sub ListBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
    Select Case KeyCode
    
        Case 40 'down
            If Me.ListBox2.ListIndex = Me.ListBox2.ListCount - 1 And _
                Me.ScrollBar1.Value < Me.ScrollBar1.Max Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value + 1
                    Me.ListBox2.ListIndex = Me.ListBox2.ListCount - 1
            End If
            
        Case 38 'up
            If Me.ListBox2.ListIndex = 0 And _
                Me.ScrollBar1.Value > 1 Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value - 1
                    Me.ListBox2.ListIndex = 0
            End If
                    
        Case 37 'left
            Me.ListBox1.ListIndex = Me.ListBox2.ListIndex
            Me.ListBox2.ListIndex = -1
            L_2 = ""
            Me.ListBox1.SetFocus
        
    End Select
End Sub
Private Sub ScrollBar1_Change()
    If Not Me.ListBox1.Value = "" Then
    
        L_1 = Me.ListBox1.Value
        
    End If
    
    If Not Me.ListBox2.Value = "" Then
    
        L_2 = Me.ListBox2.Value
        
    End If
    
    Stp = False
    
    Me.ListBox1.Clear
    Me.ListBox2.Clear
    
    For Cnt = Me.ScrollBar1.Value To Me.ScrollBar1.Value + Display_Lines - 1
    
        Me.ListBox1.AddItem Range("Special_List_m").Item(Cnt).Value
        Me.ListBox2.AddItem Range("Special_List_s").Item(Cnt).Value
        
    Next Cnt
    
    On Error Resume Next
    
    Me.ListBox1.Value = L_1
    Me.ListBox2.Value = L_2
    
    On Error GoTo 0
    
    Stp = True
    
End Sub
Private Sub UserForm_Initialize()
    
    L_1 = ""
    L_2 = ""
    
    Me.ScrollBar1.Min = 1
    Me.ScrollBar1.Max = Range("Special_List_m").Count - Display_Lines + 1
    
    Me.ScrollBar1.LargeChange = Me.ScrollBar1.Max / 5
    
    Me.ScrollBar1.Value = 1
    
End Sub

To make it work, you need (2) listboxes and a scrollbar, sid-by-side-by-side on a userform. The height of all three should be set to 105, so teh displayed lines fill up the complete control without causing the native vertical scrollbar to be displayed. And you need to lists in teh workbook called Special_List_m and Special_List_s, having the same number of members each.

Not very spectacular, however, as with any well-designed UI, it's subtle, effective and natural for the user.
 
Perhaps it's just me but.....

No 'subscribed threads'

OK so I didn't clean up my list but it appears that the list is cleared daily (not ideal). Is the issue my account or the board
 
hatman said:
The coolest UI I ever built involved displaying "paired" electrical connector part numbers. I have a list of Male connectors, and a second list of Female connectors. I wanted to allow the user to select EITHER a single male or female connector from either list. But I also wanted the two lists to scroll "in lockstep" using arrow keys, or slaved to a single common scroll-bar. And also, the total number of connectors pairs is fluid and may change over time.

I ended up storing the two lists on a hidden sheet, under two named ranges. The code ends up clearing and reloading two Listboxes from the two named ranges accoridng to events.

Hard to 'splain. If anyone wants to play (it's actually pretty neat) here's the code:

Code: 
Private Const Display_Lines As Long = 10
Private L_1 As String
Private L_2 As String
Private Sub ListBox1_Click()
    Me.ListBox2.ListIndex = -1
    L_2 = ""
End Sub
Private Sub ListBox2_Click()
    Me.ListBox1.ListIndex = -1
    L_1 = ""
End Sub

Private Sub ListBox1_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
    Select Case KeyCode
    
        Case 40 'down
            If Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1 And _
                Me.ScrollBar1.Value < Me.ScrollBar1.Max Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value + 1
                    Me.ListBox1.ListIndex = Me.ListBox1.ListCount - 1
            End If
            
        Case 38 'up
            If Me.ListBox1.ListIndex = 0 And _
                Me.ScrollBar1.Value > 1 Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value - 1
                    Me.ListBox1.ListIndex = 0
            End If
                    
        Case 39 'right
            Me.ListBox2.ListIndex = Me.ListBox1.ListIndex
            Me.ListBox1.ListIndex = -1
            L_1 = ""
            Me.ListBox2.SetFocus
        
    End Select
End Sub
 
Private Sub ListBox2_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    
    Select Case KeyCode
    
        Case 40 'down
            If Me.ListBox2.ListIndex = Me.ListBox2.ListCount - 1 And _
                Me.ScrollBar1.Value < Me.ScrollBar1.Max Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value + 1
                    Me.ListBox2.ListIndex = Me.ListBox2.ListCount - 1
            End If
            
        Case 38 'up
            If Me.ListBox2.ListIndex = 0 And _
                Me.ScrollBar1.Value > 1 Then
                    Me.ScrollBar1.Value = Me.ScrollBar1.Value - 1
                    Me.ListBox2.ListIndex = 0
            End If
                    
        Case 37 'left
            Me.ListBox1.ListIndex = Me.ListBox2.ListIndex
            Me.ListBox2.ListIndex = -1
            L_2 = ""
            Me.ListBox1.SetFocus
        
    End Select
End Sub
Private Sub ScrollBar1_Change()
    If Not Me.ListBox1.Value = "" Then
    
        L_1 = Me.ListBox1.Value
        
    End If
    
    If Not Me.ListBox2.Value = "" Then
    
        L_2 = Me.ListBox2.Value
        
    End If
    
    Stp = False
    
    Me.ListBox1.Clear
    Me.ListBox2.Clear
    
    For Cnt = Me.ScrollBar1.Value To Me.ScrollBar1.Value + Display_Lines - 1
    
        Me.ListBox1.AddItem Range("Special_List_m").Item(Cnt).Value
        Me.ListBox2.AddItem Range("Special_List_s").Item(Cnt).Value
        
    Next Cnt
    
    On Error Resume Next
    
    Me.ListBox1.Value = L_1
    Me.ListBox2.Value = L_2
    
    On Error GoTo 0
    
    Stp = True
    
End Sub
Private Sub UserForm_Initialize()
    
    L_1 = ""
    L_2 = ""
    
    Me.ScrollBar1.Min = 1
    Me.ScrollBar1.Max = Range("Special_List_m").Count - Display_Lines + 1
    
    Me.ScrollBar1.LargeChange = Me.ScrollBar1.Max / 5
    
    Me.ScrollBar1.Value = 1
    
End Sub

To make it work, you need (2) listboxes and a scrollbar, sid-by-side-by-side on a userform. The height of all three should be set to 105, so teh displayed lines fill up the complete control without causing the native vertical scrollbar to be displayed. And you need to lists in teh workbook called Special_List_m and Special_List_s, having the same number of members each.

Not very spectacular, however, as with any well-designed UI, it's subtle, effective and natural for the user.
Click to expand...

Yeah it's okay, but why not just have a 2 column listbox and let the system keep them in step?
 
xld said:
Yeah it's okay, but why not just have a 2 column listbox and let the system keep them in step?
Click to expand...

A 2 column listbox allows selection of an entire row. I needed to the user to be able to select either field that made up the row.
 
hatman said:
A 2 column listbox allows selection of an entire row. I needed to the user to be able to select either field that made up the row.
Click to expand...

Believe me, if I could have doen this withj a 2-column listbox, I would have happily avoided the extra coding.
 
You must log in or register to reply here.

Forum statistics

Threads
1,214,383
Messages
6,119,198
Members
448,874
Latest member
Lancelots

Share this page

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
Back
Top