Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User Form
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. Matthew Balch wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
user form | Excel Discussion (Misc queries) | |||
How do I fill a cell in a user form from a selection on same form? | Excel Discussion (Misc queries) | |||
How to: User Form to assign a user defined range to a macro variab | Excel Programming | |||
I am looking to see if anybody has an equivalant user form to Outlooks CONTACT form | Excel Programming |