PDA

View Full Version : Solved: List Boxes in any Office Application



samuelwright
10-17-2005, 07:02 AM
:hi:

Here is the problem: I have one UserForm1 with a list box on it, and a button next to it. This button, when pressed, opens a new Userform2 with a text box in it. I type into the text box what I want, and then the code I use enters this text into the list on userform1 (using the additem property). So far so good, I can do this as many times as I need, for one session.

However, I want the list box to be able to remember, from session to session (e.g. I leave Outlook/Excell etx, and then come back and open the userform), what I typed in the textbox in Userform2. Currently it does not do this. I was thinking of exporting the text into an excell spreadsheet as well as the list box, saving the spreadsheet, and then when the userform is next initialized, populate those list entries with the entries in the spreadsheet cells...seems a bit heavey handed to me though...any better ways of doing this? E.g is a list box the best way to go? Would a combo box be better?

xCav8r
10-17-2005, 07:54 AM
:hi:

When I need to remember what a user entered through a UserForm that can't be stored in the visible portion of a document, workbook, etc., then I store it using something more hidden: hidden sheets, custom properties, document variables, etc. When I want to remember something across applications, I might store these things in an Access database, since it's easier to grab what I want via ADO or DAO from a database than it is from a flat text file. My preference, however, would be to use the existing Office documents to store the information, since an extra file is often an unnecessary complication.

List boxes are good ways of letting a user select multiple things at once. Combo boxes only allow for single selections. Determining which is better depends on what you want your users to be able to do. Perhaps if you shared that...

Killian
10-17-2005, 08:55 AM
Yes, you're going to need to store this information somewhere so that the listbox (or combo box - they behave more or less the same, just look different) can be populated.
Where to store this info depends on how you want to access it (which applications need to read it) and how much of it there is.
Saving to a file of some kind is one option and I'd agree with what's already been suggested - except that I'm old-skool and I particularly like reading and writing flat text and INI files :whistle:
Another option is using the Windows Registry. This has the advantage of holding entries for each Windows user. I have to say that I'm not a fan of bloating the registry with huge lists of data (although I don't know why, since Microsoft don't seem to care!) but it's a handy alternative, provided you don't operate under a corporate IT department fascist dictatorship that doesn't allow this kind of sharing of information.
Here's some code:Private Sub CommandButton1_Click()
ListBox1.AddItem InputBox("Enter something", "Add new list item")
End Sub

Private Sub UserForm_Initialize()

Dim i As Long

If GetSetting("TESTAPP", "ListBox1", "EntryCount", "None") <> "None" Then
For i = 0 To GetSetting("TESTAPP", "ListBox1", "EntryCount", "None") - 1
ListBox1.AddItem GetSetting("TESTAPP", "ListBox1", i, "Unable to read entry")
Next
End If

End Sub

Private Sub UserForm_Terminate()

Dim i As Long

'remove old entries first
If GetSetting("TESTAPP", "ListBox1", "EntryCount", "None") <> "None" Then
DeleteSetting "TESTAPP", "ListBox1"
End If

SaveSetting "TESTAPP", "ListBox1", "EntryCount", ListBox1.ListCount
For i = 0 To ListBox1.ListCount - 1
SaveSetting "TESTAPP", "ListBox1", i, ListBox1.List(i)
Next

End Sub

samuelwright
10-17-2005, 09:10 AM
Hi xCav8r

Thanks for your reply. I agree that opening/refering to data in new applications would be counter productive for what I am trying to achieve, which is this.

The aim of my code is to allow users in Outlook to populate their subject fields in a speeded up manner in accordance with our company naming policy
<date (yyyymmdd)>-<Subject>-<Author>. This is to enable easier email retrieval in our file structure.

The combo box in the userform (this is what I want now, thanks for the tip!) is intended to give a list of possible "names" of the author, e.g. John Smith, or Head of Accounts, or Football Captain. The "add new button" in the userform will allow the author to add new descriptions as and when he requires. Therefore the author can do all his titles in one go at his first use of the form and whenever he reopens the forms, he simply needs to select the author title he wants, based on the context of the email.

If I was trying to do the same in Excell, it would be easier, I would just use a hidden sheet as you suggest. However, where in Outlook can I "export" the data in the text box? Here is what I have got, for what it is worth:

'---This is the code assciated with the UserForm2 that is raised when I press the button in Userform1 which adds a new item to the ComboBox in UserForm 1

Private Sub CommandButton1_Click()

UserForm1.ComboBox1.additem (TextBox1.value)

unload me

end sub



Thanks for your help so far...

chocobochick
10-17-2005, 10:27 AM
I'd think saving and loading the data with a text file would be the easiest method. Use the userform1's Initialize and Terminate events (like Killian demonstrated above) to perform these actions, and just pick a specific path location for the file in question. If you really want to pick a folder that works among varied Windows computer systems, this link (http://www.developerfusion.co.uk/show/2561/5/) shows you a function that can retrieve the Windows System directory for you.

samuelwright
10-19-2005, 01:52 AM
Killian

Works a treat thank you very much!!

samuelwright
11-03-2005, 09:26 AM
Hi everyone!

What Killian suggested in his code worked really well for my stand alone computer that I have got, but it doesnt seem to work when I try to use it on a networked PC.

I also had a hunch that if it did work, it would only work for one workstation, e.g. if I moved to a different computer, would the combo box still remember what was written in the text box? As a VBA newbie, I am not sure.

I was wondering how would one actually export a text file with the entry in a list box using the VBA code...and then store it in, say, My Documents (hidden to prevent deletion if possible)...and then retrieve it everytime the userform with the combobox on it was loaded. I really haven't got the foggiest idea:think:

Howard Kaikow
11-03-2005, 08:19 PM
Hi everyone!

What Killian suggested in his code worked really well for my stand alone computer that I have got, but it doesnt seem to work when I try to use it on a networked PC.

I also had a hunch that if it did work, it would only work for one workstation, e.g. if I moved to a different computer, would the combo box still remember what was written in the text box? As a VBA newbie, I am not sure.

I was wondering how would one actually export a text file with the entry in a list box using the VBA code...and then store it in, say, My Documents (hidden to prevent deletion if possible)...and then retrieve it everytime the userform with the combobox on it was loaded. I really haven't got the foggiest idea:think:

I would do one of the following:

1. Plop the stuff in a text file, but the file's path or name should not be hardcoded. For each user, you can programmatically determine where to create files and make sure the filename is unique.

2. Depending on the app, you could put stuff in the registry. This would provide much faster execution, but you would need to provide a means to clean up unnecessary stuff from the registry.

samuelwright
11-04-2005, 01:45 AM
Ok...how do I use the windows registry? I really do not know where to begin to use the registry:(

Also, I am not sure how to export information into a text file..please could I see a code example? Thanks

Killian
11-04-2005, 03:52 AM
I think there's some confusion over the requirement here...
I first thought you wanted to store this info per user on a PC - which the Registry can help with because it has an area to save info based on which user is logged in. However it's local to that PC.
If you need this to work for all users across all workstations on a network, you will need to store the info for each user in a network location that is accessable to all users.

The best method for this will depend on how much info you store for each user and how many users there may be.
I'll give an example of how to do it with text files - it may not be the best method - holding it as some kind of database that you reference might be more feasible.
(Note that I'm going to use a basic example with a hard-coded path name for the file, You'll need to add code to make sure that path is accessible and the user can write to it, etc. The code is for a UserForm with the controls listed)
Option Explicit

Const INI_FILE_PATH As String = "C:\Documents and Settings\Killian\Desktop\UserLists\"

'code for Userform1
'Userform1 has:
'ListBox named ListBox1
'TextBox named TextBox1
'CommandButton named cmdAdd
'CommandButton named cmdDel
'CommandButton named cmdDone

Private Sub UserForm_Initialize()
GetUserList
End Sub

Private Sub cmdAdd_Click()
ListBox1.AddItem TextBox1.Text
TextBox1.Text = ""
TextBox1.SetFocus
End Sub

Private Sub cmdDel_Click()
If ListBox1.ListIndex <> -1 Then
ListBox1.RemoveItem ListBox1.ListIndex
End If
End Sub

Private Sub cmdDone_Click()
SaveUserList
Unload Me
End Sub


Private Sub GetUserList()

Dim strTemp As String

On Error GoTo errortrap
Open INI_FILE_PATH & Environ("USERNAME") & ".txt" For Input As #1
Do While Not EOF(1)
Line Input #1, strTemp
ListBox1.AddItem strTemp
Loop
Close #1
errortrap:
End Sub

Private Sub SaveUserList()

Dim strOutput As String
Dim i As Long

On Error GoTo errortrap
Open INI_FILE_PATH & Environ("USERNAME") & ".txt" For Output As #1
For i = 1 To ListBox1.ListCount
Print #1, Me.ListBox1.List(i - 1)
Next i
errortrap:
Close #1
End Sub

samuelwright
11-04-2005, 06:58 AM
This works well Killian: I have used the savelist and getlist subs in the project I am doing: thank you, as a VBA newbie I am learning loads!

In terms of the hard wired path name issue, I have created an input box which users can paste their desired path location name in there (usually My Documents since that is the only folder that "roams" with the user-our network X:/ drive has been locked because we are gradually migrating to Windows Sharepoint Services Web based document saving-which is interesting...).

Is there a way for the input box text information being pasted directly into the code? i.e.

Const INI_FILE_PATH As String = UserForm1.Textbox1.value

Thinking aloud, I guess I could use the same procedures as in the previous messages, and write that piece of information from the input box to a txt file? And then retrieve it each time? Or is there a more subtle way?

Thanks for all your help so far!!

Killian
11-04-2005, 07:30 AM
Well a constant has to be hard-coded so you should replace this line with a variable declaration then initialize that variable when you have the value i.e. with an input box
There's not much point in writing the path to a text file because the issue you're trying to solve is getting the path to the profile
This is more of a deployment issue than anything... If all your users have thier "My Documents" folder mapped to a network drive at windows startup, then the best solution may be to get this value with the Environ function 'Const INI_FILE_PATH As String = UserForm1.Textbox1.Text
'declare variable (at the top of the module, outside the routines
Dim INI_FILE_PATH As String

'Get the path with an input box
INI_FILE_PATH = InputBox("Enter profile path:", "Userform name...")

'Get the user's "Home" directory
INI_FILE_PATH = Environ("HOMEDRIVE") & Environ("HOMEPATH") & "\"