Gegevens uit .ini file in combobox zetten

[Schorrie]

Hoi,

zit met een vraag, momenteel vul ik mijn combobox vanuit mijn vba procedure,

is het mogelijk om de combobox te vullen met gegevens uit een ini file,
hiervoor zou het programma bij opstarten in een bepaalde ini file moeten
gaan kijken,
---
bvb combobox fruit

en ik het ergens op de schijf een .ini of .txt file met volgende gegevens

appel
peer
citroen

Moest dit gaan heb ik een tweede vraag, iets moeilijker,
combobox betreft één met een invoervak,

wordt in dit invoervak manueel een naam getypt, bvb Banaan, en deze staat
nog niet in de .ini of .txt file, dan wordt dit nieuw item door de procedure
toegevoegd aan de .ini file zodat bij heropstarten van het programma er een
item meer in de combobox aanwezig is

hopelijk is het een beetje duidelijk wat ik wil bereiken

 

[Perry]

This is an english orientated newsgroup. If your purpose is to get rapid
answers from respondees, you would be better off addressing the accordingly
....

Your 2-fold question:
1) how do I populate a combobox from an INI or TXT file?
2) how can I add a non existing listitem to the combo after this has been
entered in the combobox?

3 steps are needed to facilitate your above needs:
- populating a combobox, reading out the textfile
- function to check existence of your new item
- mechanism to append new items to textfile

Additionally, I wouldn't use an INI file, instead I would utilize a TXT file
for this purpose instead.

Below are some userform examples on how to accomplish this ...
If not helpfull, kinldy repost indicating where you got stuck.

Krgrds,
Perry

Situation:
Add a combobox and a textbox to a userform, don't rename the controls
and paste below code in the userform codemodule.
Create a textfile "c:\temp\fruits.txt" containing following lines:

apple
pear
banana
lemon
pineapplepears

and run the userform

== begin VBA
Function ListItem_Exists(ByVal NewItem As String) As Boolean
Dim x As Integer
'run through combobbox listitems to check
'whether NewItem exists
For x = 0 To Me.ComboBox1.ListCount - 1
'match, function returns true
If NewItem = Me.ComboBox1.List(x) Then
ListItem_Exists = True
Exit Function 'step out
End If
Next
'function remains false
End Function

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim NewItem As String
Dim i As Integer

If Trim(Me.ComboBox1) = vbNullString Then Exit Sub

If Not ListItem_Exists(Trim(Me.ComboBox1)) Then
NewItem = Me.ComboBox1
If MsgBox("Do you want to add: '" & NewItem & "'", _
vbQuestion + vbYesNo) = vbYes Then
i = FreeFile
'add to textfile
Open "c:\temp\fruits.txt" For Append As #i
Print #i, NewItem
Close #i
'repopulate
UserForm_Initialize
'show new listitem
Me.ComboBox1 = NewItem
Else
Cancel = True
End If
End If

End Sub

Private Sub UserForm_Initialize()
Dim i As Integer
Dim arrFruits
i = FreeFile
Open "c:\temp\fruits.txt" For Input As #i
'store contents in array
arrFruits = Split(Input(LOF(i), i), vbCrLf)
Close #i
'transfer array to list property of combo
Me.ComboBox1.List = arrFruits

End Sub

== end VBA

 

[Perry]

one line was missing in previously provided UserForm_Initialize event:

Me.ComboBox1.MatchEntry = fmMatchEntryNone

Else you can't enter anything new in the combo ... )

Succes!

Krgrds,
Perry

 

[Schorrie]

next problem,

i want to check more than one combobox in the userform using this

the problem is that when i can append items in the list, but with the
function ListItem_exists

i get an compile error

double name exists: listitem_exists

anyone has a solution,
thanks

 

[Schorrie]

thanks, found it already,

next problem,

i want to check more than one combobox in the userform using this

the problem is that when i can append items in the list, but with the
function ListItem_exists

i get an compile error

double name exists: listitem_exists

anyone has a solution,
thanks

 

[Perry]

If you want to use the ListItem_Exists function for more
comboboxes in your userform, you'll have to adjust the function to read:

Function ListItem_Exists(ByVal NewItem As String, _
ByVal MyCombo As MSForms.ComboBox) As Boolean

Dim x As Integer
'run through combobbox listitems to check
'whether NewItem exists
For x = 0 To Me.MyCombo.ListCount - 1
'match, function returns true
If NewItem = Me.MyCombo.List(x) Then
ListItem_Exists = True
Exit Function 'step out
End If
Next
'function remains false
End Function

Now you can input any combobox on the userform as parameter to the function.

Using the function in the ComboBox1_Exit routine as previously forwarded:

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim NewItem As String
Dim i As Integer

If Trim(Me.ComboBox1) = vbNullString Then Exit Sub

'<<< here's the adjustment, using the corrected function >>>
If Not ListItem_Exists(Trim(Me.ComboBox1.Value), Me.ComboBox1) Then

NewItem = Me.ComboBox1
If MsgBox("Do you want to add: '" & NewItem & "'", _
vbQuestion + vbYesNo) = vbYes Then
i = FreeFile
'add to textfile
Open "c:\temp\fruits.txt" For Append As #i
Print #i, NewItem
Close #i
'repopulate
UserForm_Initialize
Show New listitem
Me.ComboBox1 = NewItem
Else
Cancel = True
End If
End If
End Sub

Advise:
First try to make it work for one combobox only using the code sequence as
previously forwarded.
After this try to understand the concept of that code.

Next, y're ready to take it one step further by utilizing above function on
multiple comboboxes
on yr userform.

Krgrds,
Perry

 

[Schorrie]

OK,

next question

same as before, but now i want to fill a textbox with the data that was in
it the last time the program started,

eg:
i have a textbox with 'TEST 1" in it
next time the program starts, textbox has to be filled with "TEST 1"

if anything else entered, then question: 'Do You want to use this data "TEST
2" again next time? Y/N'

i think its nearly the same

thanks

 

[Perry]

Sure.

You'll have to store the value as entered in the textbox.
In below example, I'm using "c:\temp\text.INI" as intermediaire file
to store what's been entered in a textbox.

When the userform popsup, the textbox gets loaded with the value
stored in the INI file.
Note: if the INI file isn't present, it will be created on the fly and
the textbox will be empty.

When the userform unloads (in below example I used UserForm_QueryClose but
can be any other validating or closing event), VBA tests whether the current
text
of the textbox matches the corresponding entry in the INI file.
If it doesn't, user has to confirm to save the new value.

Krgrds,
Perry

Situation of below example:
INI file = c:\temp\text.INI
UserForm1 containing a textbox: TextBox1

== begin VBA code
Dim sINI As String

Private Sub UserForm_Initialize()
sINI = "c:\temp\text.INI"
Me.TextBox1 = _
System.PrivateProfileString(sINI, "Values", "Textbox1")
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)

If Me.TextBox1 <> System.PrivateProfileString(sINI, "Values",
"Textbox1") Then

If MsgBox("Do you want to save the new value?", vbYesNo) = vbYes
Then

System.PrivateProfileString(sINI, "Values", "Textbox1") = _
Me.TextBox1
End If
End If
End Sub
=== end code