User Form

[Matthew Balch]

Hi,

Help sorely needed.
Been researching and trying to set up my own userform but always keep
getting stuck. Seen some good examples of bits and bobs of what I need but
when I try to cater them for what I need they never work and I can't solve
why they won't work.

What I have is this:-

One sheet in my workbook is called 'Subs Targets'.

On this sheet I have many columns with data.

On my userform the first drown box I have is for Editor selection. This is
equal to column B. This has many of the editors names repeated and I only
want singular names (no repears).

This first drop down drives the second drop down selection. This colum is
equal to column D and again has repeating items in it for which I only want
to have the individual titles (no repeats) displayed in the drop down.

Then once you choose from the second drop down I would like an editable list
to be displayed showing the values of columns J to AD, but only columns O,T,Y
and AD being editable. The others being fixed. If the user edits the data in
either O.T, Y and AD then this replaces the existing data in the relevant
cell.

I am really struggling and would greatly appreciate any sort of help

Many Thanks
Matthew Balch

 

[Rawce]

The first part of your problem I can help with (I hope), the second (O,
T, Y and AD) may require a bit more investigation. The way I worked out
to sort and remove duplicates from a list box based on a column is as
follows (and uses code I mostly borrowed from places like this, so I
can't take any credit).

Please note that my form is called InputForm, the listbox is called
PrevSpecRefs, SpecCount is started off at 2 to ignore the header,
Last_Row_1 is the number of rows and is determined beforehand and the
working sheet is handled by the Current_Sheet variable (may need to
replace it with "Subs Targets" with the speechmarks).

++++++++++++++++++++++++

' This is a dynamic list that is built from previous entries in the
columns on the working spreadsheet.
' Clear any previous entries on the userform.
InputForm.PrevSpecificRefs.Clear
SpecCount = 2
Do While SpecCount <= Last_Row_1
If Sheets(Current_Sheet).Range("B" & SpecCount) <> "" Then
InputForm.PrevSpecificRefs.AddItem
Sheets(Current_Sheet).Range("B" & SpecCount)
End If
SpecCount = SpecCount + 1
Loop

' This bit was borrowed from the internet and basically loops through
to sort out the list.
LbList = InputForm.PrevSpecificRefs.List
'Bubble sort the array on the first value
For i = LBound(LbList, 1) To UBound(LbList, 1) - 1
For j = i + 1 To UBound(LbList, 1)
If LbList(i, 0) > LbList(j, 0) Then
'Swap the first value
sTemp = LbList(i, 0)
LbList(i, 0) = LbList(j, 0)
LbList(j, 0) = sTemp
'Swap the second value
sTemp2 = LbList(i, 1)
LbList(i, 1) = LbList(j, 1)
LbList(j, 1) = sTemp2
End If
Next j
Next i

' This was also borrowed from some websource and strips out the
duplicates.
' Though I did add the max length check myself.
InputForm.PrevSpecificRefs.List = LbList
' Arbitrary value to get the ball rolling.
MaxLen = 50
For i = InputForm.PrevSpecificRefs.ListCount - 1 To 1 Step -1
Current_i = InputForm.PrevSpecificRefs.List(i)
Len_i = InputForm.PrevSpecificRefs.List(i)
Previous_i = InputForm.PrevSpecificRefs.List(i - 1)
If Current_i = Previous_i Then
InputForm.PrevSpecificRefs.RemoveItem (i)
End If
If (Len(Len_i) > MaxLen) Then
MaxLen = Len(Len_i)
End If
Next i
' This sets the max length of your listbox (provided you have a
horizontal scrollbar on it) to just longer than the longest entry. This
is probably font dependant though, so you may need to change if the
font or its size change.
InputForm.PrevSpecificRefs.ColumnWidths = 15 * MaxLen / 4

++++++++++++++++++++++++

Basically, the above is in three stages; build the list, sort the list
and remove the duplicates. Hope this helps for the first part!

Cheers,

Ross.