Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
save user inputs from userform in a seperate workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
save user inputs from userform in a seperate workbook
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 -- jb "sam" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
save user inputs from userform in a seperate workbook
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" wrote: 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 -- jb "sam" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
save user inputs from userform in a seperate workbook
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. -- jb "sam" wrote: 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" wrote: 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 -- jb "sam" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
save user inputs from userform in a seperate workbook
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" wrote: 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. -- jb "sam" wrote: 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" wrote: 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 -- jb "sam" wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
save user inputs from userform in a seperate workbook
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" wrote: 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. -- jb "sam" wrote: 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" wrote: 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 -- jb "sam" wrote: 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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
save user inputs from userform in a seperate workbook
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) -- jb "sam" wrote: 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" wrote: 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. -- jb "sam" wrote: 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" wrote: 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 -- jb "sam" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Save workbook button on Userform | Excel Programming | |||
save nonactive worksheet as a seperate workbook? | Excel Programming | |||
How do I share a workbook and allow user to save? | Excel Discussion (Misc queries) | |||
userform inputs | Excel Programming | |||
userform inputs | Excel Programming |