PDA

View Full Version : Problem with "Fill combobox with ordered list"



Simon-ch
10-20-2008, 06:43 AM
Hi,

I adapted the following code:
"Fill combobox with ordered list"
Can't link to the vbaexpress article as it's my first post :(

I am not very proficient with VBA, I can just modify some code for my own use, what I've added is let the combobox appear when the workbook is opened. What I DON'T want is the combobox to appear when you change the sheet. How can I do that please ?
Thanks for the help : )

Simon

CreganTur
10-20-2008, 07:12 AM
Welcome to the forum! Always good to see new members.

While you can't insert a link into your posts (until you've made 5 posts) you can still put the URL- just remove the 'http://' part of the code.

Is your combobox on a UserForm, or is it a control placed directly on the worksheet?

Simon-ch
10-20-2008, 07:30 AM
Thanks for the reply. It's a popup userform that I've adapted.

it's vbaexpress dot com/kb/getarticle.php?kb_id=824
II would just like to remove the option that it reappears when you change to the sheet.

CreganTur
10-20-2008, 07:48 AM
Well the code in the kb entry doesn't control when the UserForm appears.

If you want the UserForm to open only when the workbook is opened, then you need to put the code to open the UserForm in the Workbook's Open event.

Simon-ch
10-20-2008, 07:59 AM
Hm... I'm just too stupid sometimes I'm afraid..
Here is the code in userform1, which pops up whenever you change to sheet1. (I've added some code for it to pop up whenever you open the workbook too, but I want only that)

Option Explicit

Dim FArray()
Dim DataList As Range
Dim MyList As String

Private Sub UserForm_Initialize()
Dim Found As Long, i As Long
Dim cel As Range

'Set Range Name to suit
MyList = "choice"

Set DataList = Range(MyList)
ReDim FArray(DataList.Cells.Count)
i = -1

For Each cel In DataList
On Error Resume Next
Found = Application.WorksheetFunction.Match(CStr(cel), FArray, 0)
If Found > 0 Then GoTo Exists
i = i + 1
FArray(i) = cel
Exists:
Found = 0
Next
ReDim Preserve FArray(i)
Call BubbleSort(FArray)
ComboBox1.ListRows = i + 1
ComboBox1.List() = FArray
End Sub

Private Sub ComboBox1_AfterUpdate()
Dim MyAdd As String
Dim Found As Long

On Error Resume Next
Found = Application.WorksheetFunction.Match(ComboBox1, FArray, 0)
If Found > 0 Then
DoEvents
Else
DataList.End(xlDown).Offset(1) = ComboBox1
Set DataList = Union(DataList, DataList.End(xlDown))
MyAdd = "=" & ActiveSheet.Name & "!" & DataList.Address
ActiveWorkbook.Names.Add Name:=MyList, _
RefersTo:=MyAdd
End If
End Sub

Private Sub CommandButton1_Click()
Call Hide1(ComboBox1)
Set DataList = Nothing
Unload UserForm1
End Sub

Sub BubbleSort(MyArray As Variant)

Dim First As Integer
Dim Last As Integer
Dim i As Integer
Dim j As Integer
Dim Temp As String
Dim List As String

First = LBound(MyArray)
Last = UBound(MyArray)
For i = First To Last - 1
For j = i + 1 To Last
If MyArray(i) > MyArray(j) Then
Temp = MyArray(j)
MyArray(j) = MyArray(i)
MyArray(i) = Temp
End If
Next j
Next i
End Sub

What do I have to modify exactly ?
Thanks

CreganTur
10-20-2008, 08:01 AM
Can you post a copy of your workbook? When you reply click "Go Advanced" and there's a manage Attachments button near the bottom of the screen.

Simon-ch
10-20-2008, 11:58 PM
yes of course, although this isn't my actual workbook but it's the test sheets containing my original code. I want to remove the option that it pops up when you change to sheet1.
thanks

CreganTur
10-21-2008, 05:21 AM
Yep, exactly as I thought :)

The reason why your UserForm is appearing is because your code to show the UserForm is in the Activate event for Sheet1:

Private Sub Worksheet_Activate()
Load UserForm1
UserForm1.Show
End Sub

This means that every time your worksheet is activated the UserForm will appear.

If you only want it to appear when you open the workbook, then you need to put your code into the ThisWorkbook module, and place your code in the Workbook's Open event:

Private Sub Workbook_Open()
'Load UserForm1
UserForm1.Show
End Sub

Also, the code 'Load UserForm1' is not valid VBA, so I commented it out.

HTH:thumb

Simon-ch
10-21-2008, 05:34 AM
Hehe, so easy sometimes ! :-)
Thanks again