save user inputs from userform in a seperate workbook

[sam]

Hi All, Can I save user inputs received from a userfrom in a seperate
workbook on shared drive?
For eg: My Userform workbook is in C drive(local) and I want to save the
inputs from the userform to a shared drive H drive(shared)

Thanks in Advance

 

[john]

You will need to open workbook on shared drive - something like following
should work. add code to your submit button (or whatever it is called)

Dim DestWB As Workbook
Dim DBFile As String
Dim MyPassword As String


MyPassword = "ABCD1234" '<< change as required

DBFile = "H:\WhichDirectory\WhichFile.xls" '<< change as required


Set DestWB = Workbooks.Open(DBFile, Password:="mypassword")

'do your stuff from userform here

DestWB.Close True '<< Close & save changes

 

[sam]

Hey John, Can you help me a little more with your code.. I tried using your
code but I am getting an error msg:

Run-time error '1004':
Application-defined or object-defined error

for this line:

iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row


Here is what my code looks like:

Dim DestWB As Workbook
Dim DBFile As String
Dim MyPassword As String
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Sheet1")

MyPassword = "new"

DBFile = "H:\myfiles\Demo.xlsm"
Set DestWB = Workbooks.Open(DBFile, Password:="new")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

‘Doing My Stuff here……

DestWB.Close True

 

[john]

Hi Sam,

you set your workbook reference incorrectly. Try this:

Dim DBFile As String
Dim MyPassword As String
Dim iRow As Long
Dim ws As Worksheet

MyPassword = "new"

DBFile = "H:\myfiles\Demo.xlsm"
Set DestWB = Workbooks.Open(DBFile, Password:=MyPassword)

Set ws = DestWB.Worksheets("Sheet1")

'find first empty row in database

With ws
iRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'do all your stuff here that refers to the shared workbook

End With



DestWB.Close True

You will note that within the with statement I placed a period . (full stop)
infront of Rows - this is to fully qualify to worksheet - not a problem in
2003 but understand it may cause problems in 2007

Hope works ok for you.

 

[sam]

Thanks John.. This worked out great.
One thing I want to change is,, When i run the code, a popup window is
displayed asking me to input a password for the workbook where we are storing
the input data. Where as I have already provided the password in the code.
Everything else is working great.. Once I feed in the password 'new' it
populates the workbook.

 

[sam]

Thanks John.. This worked out great.
One thing I want to change is,, When i run the code, a popup window is
displayed asking me to input a password for the workbook where we are storing
the input data. Where as I have already provided the password in the code.
Everything else is working great.. Once I feed in the password 'new' it
populates the workbook.

 

[john]

Morning Sam,

the quickest way to add a password popup is to use an input box. Downside to
this is that the password is fully visible ie you cannot change text for
"****" characters.

More involved would be to create another userform with textbox & button.
Change the textbox PasswordChr property to "*" and then test user input
against your password.

code for inputbox would look something like following:

Dim DBFile As String
Dim MyPassword As String
Dim PassWrd As Variant
Dim iRow As Long
Dim ws As Worksheet

MyPassword = "new"

PassWrd = InputBox("Enter Password : ", "Sams System")
If PassWrd = "" Then Exit Sub


If PassWrd = MyPassword Then

DBFile = "H:\myfiles\Demo.xlsm"

Set DestWB = Workbooks.Open(DBFile, Password:=MyPassword)

Set ws = DestWB.Worksheets("Sheet1")

'find first empty row in database

With ws
iRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'do all your stuff here that refers to the shared workbook

End With



DestWB.Close True

Else

msg = MsgBox("Inavalid Password", 16, "Sams System")

End If

if you need further help with this contact me directly nospamdt @ btinternet
..com (close spaces)