VBA form data

[emers21]

Hi,

i have recently created a worksheet on excel with a VBA coded form. When the form is completed and the command button for add is pushed the data is put in a different tab on the worksheet. I have come up against an obstacle though. My boss wants the data now when added to go in to a totally different worksheet. This meaning once users have added information they are unable to look at said information or edit it. (Other people's entries are confidential). Can anyone offer me any assistance with this? Also if I make the new worksheet password protected will that effect the data being entered in my form?

thanks John

 

[Trevor_S]

I'm guessing that you mean different workbooks rather than worksheets?
You could get the macro to open the different workbook, add the necessary data, then save and close it again. If you preceded this with:
Application.ScreenUpdating = False
and followed it with:
Application.ScreenUpdating = True
then users would not see the other workbook being opened and closed. The other workbook could be passwords protected, but you'd need to build unprotecting and reprotecting it into your macro. Which means that the password would be visible in the macro coding ... so you'd need to password protect the macro project too.

 

[emers21]

I'm guessing that you mean different workbooks rather than worksheets?
You could get the macro to open the different workbook, add the necessary data, then save and close it again. If you preceded this with:
Application.ScreenUpdating = False
and followed it with:
Application.ScreenUpdating = True
then users would not see the other workbook being opened and closed. The other workbook could be passwords protected, but you'd need to build unprotecting and reprotecting it into your macro. Which means that the password would be visible in the macro coding ... so you'd need to password protect the macro project too.

Thanks trevor, this is a bit above my skill set but I get the gist of what your saying. I will things a try...

 

[Trevor_S]

The trickiest stage is getting your existing code to open the different workbook, put the information in the right place there and close it again. So try that first.
Once that is working, make the other workbook password protected. The password is then an argument in Workbooks.Open(Filename,,,,Password).
Finally, insert the ScreenUpdating lines before and after the code you've just written.

I can produce a couple of sample workbooks along these lines ... obviously you'd need to amend the code as I don't know what fields are on the form or where they should go in the workbook. Unfortunately I won't be able to do that until Monday, so if you manage to work it out before then, please update the thread.

 

[emers21]

The trickiest stage is getting your existing code to open the different workbook, put the information in the right place there and close it again. So try that first.
Once that is working, make the other workbook password protected. The password is then an argument in Workbooks.Open(Filename,,,,Password).
Finally, insert the ScreenUpdating lines before and after the code you've just written.

I can produce a couple of sample workbooks along these lines ... obviously you'd need to amend the code as I don't know what fields are on the form or where they should go in the workbook. Unfortunately I won't be able to do that until Monday, so if you manage to work it out before then, please update the thread.

Thanks Trevor, that would be really helpful. I will try and make them myself over the next few days if I'm successful I will update you

 

[Trevor_S]

Here are a couple of sample files:

FormWB.xlsm: https://drive.google.com/file/d/0B_dlXxlE0XVBczI3YzN6ay04cEE/view?usp=drivesdk
DifferentWB.xlsm: https://drive.google.com/file/d/0B_dlXxlE0XVBaXlpemxUbXVrNk0/view?usp=drivesdk

Save both. DifferentWB.xlsx should be saved direct to C:\ (if not, you will need to amend the macro on the userform of FormWB.xlsm), and has a password of PW to open. Note that if you do not have full admin rights over C:\, you'll probably have to save it elsewhere and amend the macro.

FormWB can be saved anywhere and currently has no password.

FormWB contains a button which launches a userform containing one text box and an OK button. Entering anything in the box enables the OK button. Clicking the OK button will open DifferentWB, put the value of the text box into Sheet 1 column A on the row following the last row, and save/close DifferentWB. This will all be done with screen updating switched off, so that the user does not see. Then a message box will ask whether there is any more data to add. If there is, the user form will be left open, so that that the process can be repeated. If there is not, the user form will close.

Note that there is currently nothing stopping the user looking at the macro in FormWB.xlsm and seeing the other workbook name/path and password. I would therefore recommend password protecting the macro by right clicking on WBAProject(FormWB.xlsm) in the Project area of the VBA editor screen, select VBAProject Properties, then Protection tab. Tick Lock Project For Viewing, and enter a password.

At present, DifferentWB.xlsx is blank, other than a column heading in cell A1 of Sheet1. I have added a password of PW to open (you add it by selecting "Tools>General Options" or "Options" from the File SaveAs window). I don't know whether it will keep its password when you download, so you may need to do a File SaveAs to add it again.

As you test it, you can of course open DifferentWB.xlsx directly using the PW password, in order to see how what you've entered has appeared.

Hope that this helps! Obviously this is a very basic example, I'm sure your form has many more fields to complete. But hopefully it will give you enough of the "theory" to be able to build it into your workbooks.

 

[Trevor_S]

Further to the edit to my last post to this thread, if you find that DifferentWB.xlsx isn't closing, save it to another folder, and amend the following line in the userform code Private Sub CommandButton1_Click():

Set DiffWB = Workbooks.Open("C:\DifferentWB.xlsx", , , , DiffWBPass) 'Amend the filepath/name of the other workbook as required

 

[emers21]

Hi Trevor,

I have just sat down to this and I must say your workbook is exactly what I am looking for. I have tried to re-code this in to 2 workbooks I have.

The form I have made is in a Workbook file named New Restraint Log. This is then putting the data in to a file called New_Restraint_Entries.

The coding I have done is as attached (For ease I have set a password in New_Restraint_Entries as "PW")

I have attached the coding, would you mind having a look for me. I think there is a problem with the line highlighted in yellow, however I am struggling to rectify.

Thanks

John

Private Sub Cmdbutton_data_Click()
New_Restraint_EntriesPass = "PW" 'This is the password to open the other workbook

'Stop changes appearing on screen (so users don't see what is happening)
Application.ScreenUpdating = False

Dim ThisWB As Workbook
Dim DiffWB As Workbook
Set ThisWB = ActiveWorkbook 'So that we can refer to it later when its no longer active.
Set DiffWB = Workbooks.Open("C:\New_Restraint_Entries.xlsx", , , , DiffWBPass) 'Amend the filepath/name of the other workbook as required

'find first empty row in database
iRow = ws.Cells.Find(What:="*", SearchOrder:=xlRows, SearchDirection:=xlPrevious, LookIn:=xlValues).Row + 1




'check for a Name number
If Trim(Me.frm_pupil.Value) = "" Then
Me.frm_pupil.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If


'copy the data to the database
ws.Cells(iRow, 1).Value = Me.frm_pupil.Value
ws.Cells(iRow, 2).Value = Me.frm_time.Value
ws.Cells(iRow, 3).Value = Me.frm_staff.Value
ws.Cells(iRow, 4).Value = Me.frm_date.Value
ws.Cells(iRow, 5).Value = Me.frm_intreason.Value
ws.Cells(iRow, 6).Value = Me.frm_eventsprior.Value
ws.Cells(iRow, 7).Value = Me.frm_behaviour.Value
ws.Cells(iRow, 8).Value = Me.frm_routine.Value
ws.Cells(iRow, 9).Value = Me.frm_risk.Value
ws.Cells(iRow, 10).Value = Me.frm_bestaction.Value
ws.Cells(iRow, 11).Value = Me.frm_restrainttype.Value
ws.Cells(iRow, 12).Value = Me.frm_bestaction.Value
ws.Cells(iRow, 13).Value = Me.frm_post.Value


MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"
'clear the data
Me.frm_pupil.Value = ""
Me.frm_time.Value = ""
Me.frm_staff.Value = ""
Me.frm_date.Value = ""
Me.frm_intreason.Value = ""
Me.frm_eventsprior.Value = ""
Me.frm_behaviour.Value = ""
Me.frm_routine.Value = ""
Me.frm_risk.Value = ""
Me.frm_bestaction.Value = ""
Me.frm_restrainttype.Value = ""
Me.frm_bestaction.Value = ""
Me.frm_post.Value = ""
Me.frm_pupil.SetFocus


'Save and close the other workbook, then ensure that we are returned to the input workbook.
DiffWB.Close (True)
ThisWB.Activate

'Reinstate screen updates
Application.ScreenUpdating = True
End Sub







Private Sub cmdbutton_cancel_Click()
Unload Me
End Sub




Private Sub frm_pupil_Change()


End Sub

 

[Trevor_S]

Glad it does what you want.

Regarding the yellow code, has ws been dimensioned and set anywhere as a worksheet? Assuming that it has, I would actually use the following code instead.

iRow = ws.Cells.SpecialCells(xlCellTypeLastCell).Row + 1

This finds the row of the last cell used on the sheet, and adds one.

Also look at the Workbooks.Open line above it. The last argument in the brackets should be the variable containing the password. But I've noticed that you've changed the variable name in the first row of the macro, so you need to change it in the Workbooks.Open statement too.

If it helps with debugging macros, comment out the Application.ScreenUpdating = False line by putting a ' at the start of it. You will then see all the workings of the macro rather than them being hidden from view, so if anything is going wrong, it will be clearer to see what. Of course, once its working as you want it to, just remove the ' again.

Good luck!

 

[emers21]

Thanks Trevor, with regards to this, I think I may have not set the dimension correctly.

I have used this iRow = DiffWB.Cells.SpecialCells(xlCellTypeLastCell).Row + 1

I am still getting the debug line on this. Do you think this could be the coding set up incorrectly prior? Also with the extra data i am adding to the form at a later point is this causing a contradiction?

Cheers for your help