I m fairly new to VBA and have created a user form for inputing data using a
number of comboboxes and check boxes. I have a control "Enter" buton on the
form which when acivated copies all the inputed data to a list on a seperate
sheet, and then clears the form for the next entry.

I also want to be able to attach documents to the userform(mostly word and
excel or jpegs) and I have created a button "Attach Doc's" on the userform
using the code below:

I would like the user to be able to select more than one document by
repeated use of the control buton and for the names of selected documents to
be visible on the form after selection.

Finally when the user activates the Enter button I would like the attached
documents to be copied along with other data on the form to the last blank
columns on the end of my list as hyperlinks.

Private Sub attachdocs_Click()
'GetImportFileName2()
Dim Filt As String
Dim FilterIndex As Integer
Dim FileName As Variant
Dim Title As String
Dim i As Integer
Dim Msg As String

' Set up list of file filters
Filt = "Text Files (*.txt),*.txt," & _
"Lotus Files (*.prn),*.prn," & _
"Comma Separated Files (*.csv),*.csv," & _
"ASCII Files (*.asc),*.asc," & _
"All Files (*.*),*.*"

' Display *.* by default
FilterIndex = 5

' Set the dialog box caption
Title = "Select a File to Import"

' Get the file name
FileName = Application.GetOpenFilename _
(FileFilter:=Filt, _
FilterIndex:=FilterIndex, _
Title:=Title, _
MultiSelect:=True)

' Exit if dialog box canceled
If Not IsArray(FileName) Then
MsgBox "No file was selected."
Exit Sub
End If

' Display full path and name of the files
For i = LBound(FileName) To UBound(FileName)
Msg = Msg & FileName(i) & vbCrLf
Next i
MsgBox "You selected:" & vbCrLf & Msg
link1 = Msg

I would be most grateful for any help with coding necessary to allow my
"Attach Doc's" button to function correctly and also the coding necessary to
add to my "Enter" button to copy the hyperlinks for each entry into my list.


P.s - I plan to create an "Email" button on the userform which when
activated would open outlook and attach any documents previously selected
with the "Attach Doc's" button.

I am using the following code at the minute and would be grateful for any
input as to how to amend although I know it practically spells it out -
Private Sub emailbutton_Click()

' You must add a reference to the Microsoft outlook Library
' Don't forget to copy the function RangetoHTML in the module.
' Is not working in Office 97
Dim sh As Worksheet
Dim rng As Range
Dim OutApp As Outlook.Application
Dim OutMail As Outlook.MailItem

'To send the selection use this example (NB: this only works if the
sheet is unprotected)
Set sh = Sheets("report1")
Set rng = sh.Range("report1")




'If you know the sheet/range then use this two lines
' Set sh = Sheets("Sheet1") '<<< Change
' Set rng = sh.Range("A1:D10") '<<< Change

Application.ScreenUpdating = False
Set OutApp = CreateObject("Outlook.Application")
Set OutMail = OutApp.CreateItem(olMailItem)
With OutMail
.To = "
.CC = ""
.BCC = ""
.Subject = "This is the Subject line"
.HTMLBody = RangetoHTML(sh, rng)
'You can add other files also like this
'.Attachments.Add ("C:\test.txt")

.Display 'or use .Display
End With
Set OutMail = Nothing
Set OutApp = Nothing
Application.ScreenUpdating = True
End Sub


Apologies for length of post - hope I have explained myself clearly -
Grateful for any help - Please keep replies as simple as possible as I am
very new to VBA.

Hi,

You could create a listbox on your form, and keep adding the items fro
the selection to that list box.

In your loop, you can add a simple line refering to your list box an
add the selected items by using the propery AddItem, for example,

For i = LBound(FileName) To UBound(FileName)
Msg = Msg & FileName(i) & vbCrLf
mylistbox.Additem FileName(i)
Next i

Then in the Enter button, copy the contents of the ListBox to th
worksheet using following loop:

For i = 1 to mylistbox.Listcount
j=12
Range("A" & j) = mylistbox.List(i-1)
j=j+1
Next

--
a7n

-----------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...fo&userid=3214
View this thread: http://www.excelforum.com/showthread.php?threadid=56326

Thks a7n9

Listbox is working a treat.

I have added the code to the enter button but it is not looping correctly -
i want to enter the file names as hyperlinks and am using the following code
- the first hyperlink is to be inserted in column 22 with the next in column
23 and so on -
Where am I going wrong? at present it just enters the last file selected
into column 22
' Transfer the hyperlinks

For i = 1 To mylistbox.ListCount
j = 22
ActiveSheet.Hyperlinks.Add Anchor:=Cells(nextrow, j),
Address:=mylistbox.List(i - 1)
j = j + 1
Next i

Also is it possible to show the filenames only without the paths being
visible?

Thanks for your help
"a7n9" wrote:


Hi,

You could create a listbox on your form, and keep adding the items from
the selection to that list box.

In your loop, you can add a simple line refering to your list box and
add the selected items by using the propery AddItem, for example,

For i = LBound(FileName) To UBound(FileName)
Msg = Msg & FileName(i) & vbCrLf
mylistbox.Additem FileName(i)
Next i

Then in the Enter button, copy the contents of the ListBox to the
worksheet using following loop:

For i = 1 to mylistbox.Listcount
j=12
Range("A" & j) = mylistbox.List(i-1)
j=j+1
Next i


--
a7n9


------------------------------------------------------------------------
a7n9's Profile: http://www.excelforum.com/member.php...o&userid=32149
View this thread: http://www.excelforum.com/showthread...hreadid=563262