kevvukeka
06-25-2014, 11:41 PM
Hi All,
I have created a user form for submitting timesheet data. Whenever the submit button is clicked it opens the workbook related to the user and moves the data from userform to the workbook of that particular user. Now these workbooks are kept in a folder which are not accessible to the users. My userform is working fine but if someone uses CTRL+Pause break keys on keyboard it will open the debug option and displays the code. Users can then access the folders as well as the excel files, which should be avoided.
Below is the code of "Submit" button of that userform.
Kindly suggest:
1. can we disable the debug option for the users while submitting the userfom.
or
2. is there a way of submitting the data from userform to a particular excel workbook without opening that workbook.
Below is the code of the "Submit Button"
Private Sub CommandButton3_Click()
Dim wb As Workbook
Dim lrcount As Long
Dim inptres As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Workbooks.Open("[URL="file:\\path\folder1\folder2\folder3\users\ Timesheets\" & TSUF.TextBox1.Value & ".xlsx")
lrcount = wb.Sheets("Timesheet").Cells(Rows.Count, "A").End(xlUp).Row
wb.Sheets(1).Unprotect Password:="XYZTS123"
If Trim(TextBox9.Value) <> "" And ListBox5.ListIndex = -1 Then
wb.Sheets(1).Protect Password:="XYZTS123"
wb.Sheets(1).Visible = False
'If inptres = vbNo Then
MsgBox "Please select a Status for the Claim"
ListBox5.SetFocus
wb.Sheets(1).Unprotect Password:="XYZTS123"
wb.Sheets(1).Visible = True
wb.Close savechanges:=False
Exit Sub
End If
If TSUF.ListBox1.ListIndex < 4 And (Trim(TSUF.TextBox5.Value) = "" Or Trim(TSUF.TextBox6.Value) = "" Or Trim(TSUF.TextBox7.Value) = "" Or Trim(TSUF.TextBox8.Value) = "") Then
wb.Sheets(1).Protect Password:="XYZTS123"
wb.Sheets(1).Visible = False
MsgBox "Please fill the required fields"
TextBox5.SetFocus
wb.Sheets(1).Unprotect Password:="XYZTS123"
wb.Sheets(1).Visible = True
wb.Close savechanges:=False
Exit Sub
End If
With wb.Sheets("Timesheet")
.Range("A" & lrcount + 1) = TSUF.TextBox1.Value
.Range("B" & lrcount + 1) = TSUF.TextBox2.Value
.Range("C" & lrcount + 1) = TSUF.ListBox1.Value
.Range("D" & lrcount + 1) = TSUF.ListBox2.Value
.Range("E" & lrcount + 1) = TSUF.ListBox3.Value
.Range("F" & lrcount + 1) = TSUF.ListBox4.Value
.Range("G" & lrcount + 1) = TSUF.TextBox3.Value
.Range("H" & lrcount + 1) = TSUF.TextBox4.Value
.Range("I" & lrcount + 1) = TSUF.TextBox12.Value
.Range("J" & lrcount + 1) = TSUF.TextBox13.Value
.Range("K" & lrcount + 1) = TSUF.TextBox5.Value
.Range("L" & lrcount + 1) = TSUF.TextBox6.Value
.Range("M" & lrcount + 1) = TSUF.TextBox7.Value
.Range("N" & lrcount + 1) = TSUF.TextBox8.Value
.Range("O" & lrcount + 1) = Trim(TSUF.TextBox9.Value)
.Range("P" & lrcount + 1) = TSUF.ListBox5.Value
.Range("Q" & lrcount + 1) = TSUF.TextBox11.Value
End With
wb.Close savechanges:=True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub
I have created a user form for submitting timesheet data. Whenever the submit button is clicked it opens the workbook related to the user and moves the data from userform to the workbook of that particular user. Now these workbooks are kept in a folder which are not accessible to the users. My userform is working fine but if someone uses CTRL+Pause break keys on keyboard it will open the debug option and displays the code. Users can then access the folders as well as the excel files, which should be avoided.
Below is the code of "Submit" button of that userform.
Kindly suggest:
1. can we disable the debug option for the users while submitting the userfom.
or
2. is there a way of submitting the data from userform to a particular excel workbook without opening that workbook.
Below is the code of the "Submit Button"
Private Sub CommandButton3_Click()
Dim wb As Workbook
Dim lrcount As Long
Dim inptres As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set wb = Workbooks.Open("[URL="file:\\path\folder1\folder2\folder3\users\ Timesheets\" & TSUF.TextBox1.Value & ".xlsx")
lrcount = wb.Sheets("Timesheet").Cells(Rows.Count, "A").End(xlUp).Row
wb.Sheets(1).Unprotect Password:="XYZTS123"
If Trim(TextBox9.Value) <> "" And ListBox5.ListIndex = -1 Then
wb.Sheets(1).Protect Password:="XYZTS123"
wb.Sheets(1).Visible = False
'If inptres = vbNo Then
MsgBox "Please select a Status for the Claim"
ListBox5.SetFocus
wb.Sheets(1).Unprotect Password:="XYZTS123"
wb.Sheets(1).Visible = True
wb.Close savechanges:=False
Exit Sub
End If
If TSUF.ListBox1.ListIndex < 4 And (Trim(TSUF.TextBox5.Value) = "" Or Trim(TSUF.TextBox6.Value) = "" Or Trim(TSUF.TextBox7.Value) = "" Or Trim(TSUF.TextBox8.Value) = "") Then
wb.Sheets(1).Protect Password:="XYZTS123"
wb.Sheets(1).Visible = False
MsgBox "Please fill the required fields"
TextBox5.SetFocus
wb.Sheets(1).Unprotect Password:="XYZTS123"
wb.Sheets(1).Visible = True
wb.Close savechanges:=False
Exit Sub
End If
With wb.Sheets("Timesheet")
.Range("A" & lrcount + 1) = TSUF.TextBox1.Value
.Range("B" & lrcount + 1) = TSUF.TextBox2.Value
.Range("C" & lrcount + 1) = TSUF.ListBox1.Value
.Range("D" & lrcount + 1) = TSUF.ListBox2.Value
.Range("E" & lrcount + 1) = TSUF.ListBox3.Value
.Range("F" & lrcount + 1) = TSUF.ListBox4.Value
.Range("G" & lrcount + 1) = TSUF.TextBox3.Value
.Range("H" & lrcount + 1) = TSUF.TextBox4.Value
.Range("I" & lrcount + 1) = TSUF.TextBox12.Value
.Range("J" & lrcount + 1) = TSUF.TextBox13.Value
.Range("K" & lrcount + 1) = TSUF.TextBox5.Value
.Range("L" & lrcount + 1) = TSUF.TextBox6.Value
.Range("M" & lrcount + 1) = TSUF.TextBox7.Value
.Range("N" & lrcount + 1) = TSUF.TextBox8.Value
.Range("O" & lrcount + 1) = Trim(TSUF.TextBox9.Value)
.Range("P" & lrcount + 1) = TSUF.ListBox5.Value
.Range("Q" & lrcount + 1) = TSUF.TextBox11.Value
End With
wb.Close savechanges:=True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub