User Form

[Adrae]

I want to use an Excel user form in conjuction with Frontpage. Is it possible for the user, upon clicking the command button to open the workbook and run the macro, never to see the actual workbook, only the userform?

Also, how can I direct the information being provided in the user form to a whole different workbook? Again, I know it will have to open it to update it but I do not want the user to see this.

To add the record, I currently have the following:

Private Sub CommandButton1_Click()
Dim LastRow As Object
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
MsgBox "Record has been changed"
response = MsgBox("Do you want to change another record?", vbYesNo)
If response = vbYes Then
TextBox1.Text = ""
TextBox2.Text = ""
TextBox3.Text = ""
TextBox4.Text = ""
TextBox5.Text = ""
TextBox1.SetFocus
Else
Unload Me
End If
End Sub

[Jack in the UK]

Hi

dont know if this is any help option Excel drops into FTP can run in Kiosk mode so no Excel as such shows..

work arround...

[Adrae]

Can anyone tell me what I'm doing wrong with this bit of code. I want the workbook that is opened to be updated, Not the workbook containing the macro.

Application.ScreenUpdating = False
Dim LastRow As Object
Workbooks.Open Filename:="D:PersonalVBA_PracticeHeadcountData.xls"
Set LastRow = Sheet1.Range("a65536").End(xlUp)
LastRow.Offset(1, 0).Value = TextBox1.Text
LastRow.Offset(1, 1).Value = TextBox2.Text
LastRow.Offset(1, 2).Value = TextBox3.Text
LastRow.Offset(1, 3).Value = TextBox4.Text
LastRow.Offset(1, 4).Value = TextBox5.Text
Application.ScreenUpdating = True
MsgBox "Record has been added"

[davers5]

There are many ways to write to a file. I would suggest you write to a CSV file (which can easily be opened by excel). The user never sees this. Here is an example of code, try it out.

Sub FileWrite()
'declare variables
Dim strFirstName As String
Dim strLastName As String
Dim strSchool As String

'open file for append (this will add new entries), you can also open for output but that will overwrite all existing records
'assign the file a number
Open "c:TesOutput.csv" For Append As #1
strFirstName = "Juan"
strLastName = "Dixon"
strSchool = "University of Maryland"

'write to the file by stating it's number and then the values seperated by commas
Write #1, strFirstName, strLastName, strSchool
Close #1
End Sub

Good luck.

Dave