Values in drop-down combo box 
Author Message
 Values in drop-down combo box

Hi!

I have made a form with drop-down combobox that has
certain values. When somebody uses the form and doesn't
find the value he/she is looking for from the combobox how
can the user add and save the value to the combobox list
so that when he uses the form in future the value can be
found from the combobox list? I don't understand much
about VBA so I would really appreciate if somebody could
give very simple instruction how this is done.

Thanks!



Tue, 03 Feb 2004 16:31:26 GMT  
 Values in drop-down combo box
This is actually not simple at all. You must decide where and how you want
to store the values for the combo box -- individual to each user or global
for all users? files, hidden message, database?  -- and then provide code
for the Item_Open event to load the values and code for the Item_Write event
to save the values.

If you want to save the values to a text file for each user, I can post some
code for that.

--

Sue Mosher, Outlook MVP
Author of
     Teach Yourself Microsoft Outlook 2000 Programming in 24 Hours
     Microsoft Outlook 2000 E-mail and Fax Guide
Outlook and Exchange solutions at http://www.slipstick.com


Quote:
> Hi!

> I have made a form with drop-down combobox that has
> certain values. When somebody uses the form and doesn't
> find the value he/she is looking for from the combobox how
> can the user add and save the value to the combobox list
> so that when he uses the form in future the value can be
> found from the combobox list? I don't understand much
> about vba so I would really appreciate if somebody could
> give very simple instruction how this is done.

> Thanks!



Tue, 03 Feb 2004 22:07:05 GMT  
 Values in drop-down combo box
I would also be interested in this type of code.  Probably saving to a text
file is the simplest way.

Thanks,
Greg

Quote:
> Hi!

> I have made a form with drop-down combobox that has
> certain values. When somebody uses the form and doesn't
> find the value he/she is looking for from the combobox how
> can the user add and save the value to the combobox list
> so that when he uses the form in future the value can be
> found from the combobox list? I don't understand much
> about vba so I would really appreciate if somebody could
> give very simple instruction how this is done.

> Thanks!



Tue, 03 Feb 2004 22:44:43 GMT  
 Values in drop-down combo box
Hi Sue!

If possible post the code.

Thank You!

Quote:
>-----Original Message-----
>This is actually not simple at all. You must decide where
and how you want
>to store the values for the combo box -- individual to
each user or global
>for all users? files, hidden message, database?  -- and
then provide code
>for the Item_Open event to load the values and code for

the Item_Write event
Quote:
>to save the values.

>If you want to save the values to a text file for each

user, I can post some
Quote:
>code for that.

>--

>Sue Mosher, Outlook MVP
>Author of
>     Teach Yourself Microsoft Outlook 2000 Programming in
24 Hours
>     Microsoft Outlook 2000 E-mail and Fax Guide
>Outlook and Exchange solutions at http://www.slipstick.com



>> Hi!

>> I have made a form with drop-down combobox that has
>> certain values. When somebody uses the form and doesn't
>> find the value he/she is looking for from the combobox
how
>> can the user add and save the value to the combobox list
>> so that when he uses the form in future the value can be
>> found from the combobox list? I don't understand much
>> about vba so I would really appreciate if somebody could
>> give very simple instruction how this is done.

>> Thanks!

>.



Fri, 06 Feb 2004 13:29:20 GMT  
 Values in drop-down combo box
Hi
I'd prefer the data reside on excel sheet, but I'd be glade to have it also...
Thanx
Kobi Idove
Quote:

> Hi Sue!

> If possible post the code.

> Thank You!

> >-----Original Message-----
> >This is actually not simple at all. You must decide where
>  and how you want
> >to store the values for the combo box -- individual to
>  each user or global
> >for all users? files, hidden message, database?  -- and
>  then provide code
> >for the Item_Open event to load the values and code for
>  the Item_Write event
> >to save the values.

> >If you want to save the values to a text file for each
>  user, I can post some
> >code for that.

> >--

> >Sue Mosher, Outlook MVP
> >Author of
> >     Teach Yourself Microsoft Outlook 2000 Programming in
>  24 Hours
> >     Microsoft Outlook 2000 E-mail and Fax Guide
> >Outlook and Exchange solutions at http://www.slipstick.com



> >> Hi!

> >> I have made a form with drop-down combobox that has
> >> certain values. When somebody uses the form and doesn't
> >> find the value he/she is looking for from the combobox
>  how
> >> can the user add and save the value to the combobox list
> >> so that when he uses the form in future the value can be
> >> found from the combobox list? I don't understand much
> >> about vba so I would really appreciate if somebody could
> >> give very simple instruction how this is done.

> >> Thanks!

> >.



Sat, 07 Feb 2004 02:57:56 GMT  
 Values in drop-down combo box
Well I sent this once and it didn't appear. Let's try again:

Here goes. This code depends on having a valid path set for mstrAppPath in
the Item_Open event handler. If the folder doesn't exist, but the parent
folder does exist, the code will try to create the target folder. The data
for the combo box is stored in a simple text file, named after the combo
box, when the user saves the item.

Option Explicit
Dim mstrAppPath
Dim mfso

Function Item_Open()
    Dim objInsp
    Dim cmb
    Dim colControls
    On Error Resume Next

    ' full path to folder without trailing slash
    mstrAppPath = "C:\Program Files\MyApp"
    ' for new item, read data from MRU list into combo boxes
    Call InitAppPath

    Set objInsp = Item.GetInspector
    Set colControls = objInsp.ModifiedFormPages("Customer Visit").Controls

    Set cmb = colControls("cmbCustCompanyName")
    If Err = 0 Then
        Call SetComboListFromFile("CustCompanyName.txt", cmb)
    Else
        Err.Clear
    End If

    Set cmb = Nothing
    Set colControls = Nothing
End Function

Function Item_Write()
    Dim objInsp
    Dim colControls
    Dim cmb
    On Error Resume Next

    Set objInsp = Item.GetInspector
    Set colControls = objInsp.ModifiedFormPages("Customer Visit").Controls

    Set cmb = colControls("cmbCustCompanyName")
    If Err = 0 Then
        Call WriteMRUToFile("CustCompanyName.txt", cmb)
    Else
        Err.Clear
    End If
    Set cmb = Nothing
    Set colControls = Nothing
    Set objInsp = Nothing
End Function

' ******************************************************
' Routines for MRU combo boxes
' ******************************************************

Sub InitAppPath()
    Dim lngLoc
    Dim strParentPath
    Dim strFolderName
    ' mstrAppPath set in Item_Open

    ' create new folder if necessary; code handles just
    ' two levels of folders
    Set mfso = CreateObject("Scripting.FileSystemObject")
    If Not mfso.FolderExists(mstrAppPath) Then
        lngLoc = InStrRev(mstrAppPath, "\")
        strParentPath = Left(mstrAppPath, lngLoc - 1)
        strFolderName = Mid(mstrAppPath, lngLoc + 1)
        If Not mfso.FolderExists(strParentPath) Then
            mfso.CreateFolder strParentPath
        End If
        mfso.CreateFolder mstrAppPath
    End If

    ' add trailing slash
    mstrAppPath = mstrAppPath & "\"

End Sub

' read data from text file into combo box or list box
Sub SetComboListFromFile(strFileName, ctlList)
    Dim strFilePath, strText
    Dim f
    Dim ts
    Dim arr
    strFilePath = mstrAppPath & strFileName
    If mfso.FileExists(strFilePath) Then
        Set f = mfso.GetFile(strFilePath)
        Set ts = f.OpenAsTextStream
        If Not ts.AtEndOfStream Then
            strText = ts.ReadAll
            If strText <> "" Then
                arr = Split(strText, vbCrLf)
                ctlList.List = arr
            End If
        End If
    Else
        Set f = mfso.CreateTextFile(strFilePath)
        f.Close
    End If
    Set ts = Nothing
    Set f = Nothing
End Sub

' add new entry to combo box MRU list
Sub WriteMRUToFile(strFileName, ctlCombo)
    Dim arr   ' array of combo box entries
    Dim strFilePath, strText
    Dim f
    Dim i
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    On Error Resume Next

    ' see if we need to write data
    If ctlCombo.Value <> "" And Not ctlCombo.MatchFound Then
        ' add item, get array, and sort
        ctlCombo.AddItem ctlCombo.Value
        arr = ctlCombo.List
        QSort arr, LBound(arr), UBound(arr)
        For i = 0 To UBound(arr)
            strText = strText & vbCrLf & arr(i, 0)
        Next
        strText = Mid(strText, 3)

        ' get file to write to
        strFilePath = mstrAppPath & strFileName
        Set f = mfso.OpenTextFile(strFilePath, ForWriting, True)
        f.Write strText
        f.Close
    End If

    Set f = Nothing
End Sub

' quick sort routine for combo box list
Sub QSort(aData, iaDataMin, iaDataMax)
  Dim Temp
  Dim Buffer
  Dim iaDataFirst
  Dim iaDataLast
  Dim iaDataMid

  iaDataFirst = iaDataMin
  iaDataLast = iaDataMax

  If iaDataMax <= iaDataMin Then Exit Sub
  iaDataMid = (iaDataMin + iaDataMax) \ 2

  Temp = aData(iaDataMid, 0)
  Do While (iaDataFirst <= iaDataLast)
      'Comparison here
        Do While aData(iaDataFirst, 0) < Temp
          iaDataFirst = iaDataFirst + 1
          If iaDataFirst = iaDataMax Then Exit Do
      Loop

      'Comparison here
       Do While Temp < aData(iaDataLast, 0)
          iaDataLast = iaDataLast - 1
          If iaDataLast = iaDataMin Then Exit Do
      Loop

      If (iaDataFirst <= iaDataLast) Then
          Buffer = aData(iaDataFirst, 0)
          aData(iaDataFirst, 0) = aData(iaDataLast, 0)
          aData(iaDataLast, 0) = Buffer
          iaDataFirst = iaDataFirst + 1
          iaDataLast = iaDataLast - 1
      End If
  Loop

  If iaDataMin < iaDataLast Then
      QSort aData, iaDataMin, iaDataLast
  End If

  If iaDataFirst < iaDataMax Then
      QSort aData, iaDataFirst, iaDataMax
  End If
End Sub


Quote:
> Hi Sue!

> If possible post the code.

> Thank You!

> >-----Original Message-----
> >This is actually not simple at all. You must decide where
> and how you want
> >to store the values for the combo box -- individual to
> each user or global
> >for all users? files, hidden message, database?  -- and
> then provide code
> >for the Item_Open event to load the values and code for
> the Item_Write event
> >to save the values.

> >If you want to save the values to a text file for each
> user, I can post some
> >code for that.

> >--

> >Sue Mosher, Outlook MVP
> >Author of
> >     Teach Yourself Microsoft Outlook 2000 Programming in
> 24 Hours
> >     Microsoft Outlook 2000 E-mail and Fax Guide
> >Outlook and Exchange solutions at http://www.slipstick.com



> >> Hi!

> >> I have made a form with drop-down combobox that has
> >> certain values. When somebody uses the form and doesn't
> >> find the value he/she is looking for from the combobox
> how
> >> can the user add and save the value to the combobox list
> >> so that when he uses the form in future the value can be
> >> found from the combobox list? I don't understand much
> >> about vba so I would really appreciate if somebody could
> >> give very simple instruction how this is done.

> >> Thanks!

> >.



Mon, 09 Feb 2004 00:07:50 GMT  
 Values in drop-down combo box
Thanks for taking the time to respond on this.  I haven't tried running this
yet, but it is obviously fairly complex code, and I commend work on the
forum.

Regards,
Greg


Quote:
> Well I sent this once and it didn't appear. Let's try again:

> Here goes. This code depends on having a valid path set for mstrAppPath in
> the Item_Open event handler. If the folder doesn't exist, but the parent
> folder does exist, the code will try to create the target folder. The data
> for the combo box is stored in a simple text file, named after the combo
> box, when the user saves the item.

> Option Explicit
> Dim mstrAppPath
> Dim mfso

> Function Item_Open()
>     Dim objInsp
>     Dim cmb
>     Dim colControls
>     On Error Resume Next

>     ' full path to folder without trailing slash
>     mstrAppPath = "C:\Program Files\MyApp"
>     ' for new item, read data from MRU list into combo boxes
>     Call InitAppPath

>     Set objInsp = Item.GetInspector
>     Set colControls = objInsp.ModifiedFormPages("Customer Visit").Controls

>     Set cmb = colControls("cmbCustCompanyName")
>     If Err = 0 Then
>         Call SetComboListFromFile("CustCompanyName.txt", cmb)
>     Else
>         Err.Clear
>     End If

>     Set cmb = Nothing
>     Set colControls = Nothing
> End Function

> Function Item_Write()
>     Dim objInsp
>     Dim colControls
>     Dim cmb
>     On Error Resume Next

>     Set objInsp = Item.GetInspector
>     Set colControls = objInsp.ModifiedFormPages("Customer Visit").Controls

>     Set cmb = colControls("cmbCustCompanyName")
>     If Err = 0 Then
>         Call WriteMRUToFile("CustCompanyName.txt", cmb)
>     Else
>         Err.Clear
>     End If
>     Set cmb = Nothing
>     Set colControls = Nothing
>     Set objInsp = Nothing
> End Function

> ' ******************************************************
> ' Routines for MRU combo boxes
> ' ******************************************************

> Sub InitAppPath()
>     Dim lngLoc
>     Dim strParentPath
>     Dim strFolderName
>     ' mstrAppPath set in Item_Open

>     ' create new folder if necessary; code handles just
>     ' two levels of folders
>     Set mfso = CreateObject("Scripting.FileSystemObject")
>     If Not mfso.FolderExists(mstrAppPath) Then
>         lngLoc = InStrRev(mstrAppPath, "\")
>         strParentPath = Left(mstrAppPath, lngLoc - 1)
>         strFolderName = Mid(mstrAppPath, lngLoc + 1)
>         If Not mfso.FolderExists(strParentPath) Then
>             mfso.CreateFolder strParentPath
>         End If
>         mfso.CreateFolder mstrAppPath
>     End If

>     ' add trailing slash
>     mstrAppPath = mstrAppPath & "\"

> End Sub

> ' read data from text file into combo box or list box
> Sub SetComboListFromFile(strFileName, ctlList)
>     Dim strFilePath, strText
>     Dim f
>     Dim ts
>     Dim arr
>     strFilePath = mstrAppPath & strFileName
>     If mfso.FileExists(strFilePath) Then
>         Set f = mfso.GetFile(strFilePath)
>         Set ts = f.OpenAsTextStream
>         If Not ts.AtEndOfStream Then
>             strText = ts.ReadAll
>             If strText <> "" Then
>                 arr = Split(strText, vbCrLf)
>                 ctlList.List = arr
>             End If
>         End If
>     Else
>         Set f = mfso.CreateTextFile(strFilePath)
>         f.Close
>     End If
>     Set ts = Nothing
>     Set f = Nothing
> End Sub

> ' add new entry to combo box MRU list
> Sub WriteMRUToFile(strFileName, ctlCombo)
>     Dim arr   ' array of combo box entries
>     Dim strFilePath, strText
>     Dim f
>     Dim i
>     Const ForReading = 1, ForWriting = 2, ForAppending = 8
>     On Error Resume Next

>     ' see if we need to write data
>     If ctlCombo.Value <> "" And Not ctlCombo.MatchFound Then
>         ' add item, get array, and sort
>         ctlCombo.AddItem ctlCombo.Value
>         arr = ctlCombo.List
>         QSort arr, LBound(arr), UBound(arr)
>         For i = 0 To UBound(arr)
>             strText = strText & vbCrLf & arr(i, 0)
>         Next
>         strText = Mid(strText, 3)

>         ' get file to write to
>         strFilePath = mstrAppPath & strFileName
>         Set f = mfso.OpenTextFile(strFilePath, ForWriting, True)
>         f.Write strText
>         f.Close
>     End If

>     Set f = Nothing
> End Sub

> ' quick sort routine for combo box list
> Sub QSort(aData, iaDataMin, iaDataMax)
>   Dim Temp
>   Dim Buffer
>   Dim iaDataFirst
>   Dim iaDataLast
>   Dim iaDataMid

>   iaDataFirst = iaDataMin
>   iaDataLast = iaDataMax

>   If iaDataMax <= iaDataMin Then Exit Sub
>   iaDataMid = (iaDataMin + iaDataMax) \ 2

>   Temp = aData(iaDataMid, 0)
>   Do While (iaDataFirst <= iaDataLast)
>       'Comparison here
>         Do While aData(iaDataFirst, 0) < Temp
>           iaDataFirst = iaDataFirst + 1
>           If iaDataFirst = iaDataMax Then Exit Do
>       Loop

>       'Comparison here
>        Do While Temp < aData(iaDataLast, 0)
>           iaDataLast = iaDataLast - 1
>           If iaDataLast = iaDataMin Then Exit Do
>       Loop

>       If (iaDataFirst <= iaDataLast) Then
>           Buffer = aData(iaDataFirst, 0)
>           aData(iaDataFirst, 0) = aData(iaDataLast, 0)
>           aData(iaDataLast, 0) = Buffer
>           iaDataFirst = iaDataFirst + 1
>           iaDataLast = iaDataLast - 1
>       End If
>   Loop

>   If iaDataMin < iaDataLast Then
>       QSort aData, iaDataMin, iaDataLast
>   End If

>   If iaDataFirst < iaDataMax Then
>       QSort aData, iaDataFirst, iaDataMax
>   End If
> End Sub



> > Hi Sue!

> > If possible post the code.

> > Thank You!

> > >-----Original Message-----
> > >This is actually not simple at all. You must decide where
> > and how you want
> > >to store the values for the combo box -- individual to
> > each user or global
> > >for all users? files, hidden message, database?  -- and
> > then provide code
> > >for the Item_Open event to load the values and code for
> > the Item_Write event
> > >to save the values.

> > >If you want to save the values to a text file for each
> > user, I can post some
> > >code for that.

> > >--

> > >Sue Mosher, Outlook MVP
> > >Author of
> > >     Teach Yourself Microsoft Outlook 2000 Programming in
> > 24 Hours
> > >     Microsoft Outlook 2000 E-mail and Fax Guide
> > >Outlook and Exchange solutions at http://www.slipstick.com



> > >> Hi!

> > >> I have made a form with drop-down combobox that has
> > >> certain values. When somebody uses the form and doesn't
> > >> find the value he/she is looking for from the combobox
> > how
> > >> can the user add and save the value to the combobox list
> > >> so that when he uses the form in future the value can be
> > >> found from the combobox list? I don't understand much
> > >> about vba so I would really appreciate if somebody could
> > >> give very simple instruction how this is done.

> > >> Thanks!

> > >.



Mon, 09 Feb 2004 11:14:15 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. Drop Down Combo box with smaller down arrow icon

2. Hiding combo box border and drop-down box

3. Drop Down List Box - Drop Down portion does not always disappear after Click event

4. Combo box drop down on got focus

5. Drop-down(Combo box) text limit?

6. Character limit of Combo Box - Drop Down Item

7. REPOST: How to display a user control on combo box drop down (OwnerDrawn)

8. Thanks for the Help (Combo Box Automatically Drops down)

9. Help Drop Down a Combo box automatically !!?

10. drop-down combo box

11. Drop Down Combo Box Events

12. combo box drop down and without click_event

 

 
Powered by phpBB® Forum Software