VBA to stop dsiplaying the debugging option on pressing CTRL+Pause Break

[kevvukeka]

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

[westconn1]

set the application or workbook window or userform .visible to false until finished?

[kevvukeka]

Hi Westconn1,

Thanks for the reply. But I am not sure how exactly should I do it.. Can you help me with it. Can you please edit the above code for me.



On clicking submit it basically is open the related workbook kept in folder and copying the details from userform. But if they haven't filled all the fields properly, it will give a prompt message to fill the required fields on the userform. Below is that section.




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





Thank you..

Praveen Pagadala