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

 

[emers21]

As a further note to this, I have amended the code as follows:- I am now getting a run time 438 error. Have I just worsened the coding?

Private Sub Cmdbutton_data_Click()
New_Restraint_EntriesPass = "PW"


Application.ScreenUpdating = False

Dim ThisWB As Workbook
Dim DiffWB As Workbook
Set ThisWB = ActiveWorkbook
Set DiffWB = Workbooks.Open("C:\New_Restraint_Entries.xlsx", , , , New_Restraint_EntriesPass)

LastRow = DiffWB.Sheets("Sheet1").Cells.SpecialCells(xlLastCell).Row






If Trim(Me.frm_pupil.Value) = "" Then
Me.frm_pupil.SetFocus
MsgBox "Please complete the form"
Exit Sub
End If


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


MsgBox "Data added", vbOKOnly + vbInformation, "Data Added"


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



DiffWB.Close (True)
ThisWB.Activate


Application.ScreenUpdating = True
End Sub







Private Sub cmdbutton_cancel_Click()
Unload Me
End Sub




Private Sub frm_pupil_Change()


End Sub

 

[Trevor_S]

DiffWb is a workbook. The rest of the code line should refer to a worksheet. You've used ws elsewhere, so I'd suggest the line should be:

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

But ... I can't see ws dimensioned or set anywhere. So you probably need this code immediately after the Set DiffWb... line:

Dim ws as Worksheet
Set ws = DiffWb.ActiveSheet

 

[emers21]

Perfect, I added this code and the dimension. Then I reverted my form filling modes back to ws and all now works how I want. Only issue I have is although the workbook I put the data in is password protected I am able to open that without being prompted for the password. (I have checked and it is still turned on) Any ideas?

Thanks for all your help.

John

 

[emers21]

Perfect, I added this code and the dimension. Then I reverted my form filling modes back to ws and all now works how I want. Only issue I have is although the workbook I put the data in is password protected I am able to open that without being prompted for the password. (I have checked and it is still turned on) Any ideas?

Thanks for all your help.

John

NB do not worry about this mate, my excel being a bit stupid! Whole sheet is all good to go... I just need to edit file locations etc.. Thanks for all your help I have learnt a lot!

Cheers

John

 

[Trevor_S]

No problem, glad its working!