|
Help with Code
Your help will be greatly appreciated. Here is what I am trying to accomplish.
I would like to give User's of the workbook an ability to "sort and filter" while maintaining protection of the worksheet and workbook and ability to utilize the Userform, but am running into some problems. 1. When I save, close then reopen workbook, engage the Userform and once I click the commandbutton am promted for the password. Needless to say, the other users won't have the password. 2.Also, have been unable to figure out vba "sort and filter", intergration with # 1. Below is the code I'm using: Private Sub CommandButton1_Click() Dim CurrSheet As Worksheet Set CurrSheet = ActiveSheet CurrSheet.Protect Contents:=True, Password:="1234" CurrSheet.Unprotect Password:="1234" CurrSheet.Protect Contents:=True, Password:="1234" CurrSheet.Protect UserInterfaceOnly:=True Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Offset(1, 0).Value = ComboBox1.Text LastRow.Offset(1, 1).Value = TextBox3.Text LastRow.Offset(1, 2).Value = TextBox1.Text LastRow.Offset(1, 3).Value = TextBox2.Text LastRow.Offset(1, 4).Value = ComboBox2.Text LastRow.Offset(1, 5).Value = ComboBox3.Text LastRow.Offset(1, 6).Value = ComboBox4.Text LastRow.Offset(1, 7).Value = DTPicker2.Value MsgBox "One record written to Sheet1" response = MsgBox("Do you want to enter another record?", _ vbYesNo) If response = vbYes Then ComboBox1.Text = "" TextBox3.Text = "" TextBox1.Text = "" TextBox2.Text = "" ComboBox2.Text = "" ComboBox3.Text = "" ComboBox4.Text = "" DTPicker2.Value = Date TextBox1.SetFocus Else Unload Me End If End Sub Private Sub CommandButton1_Click() Dim CurrSheet As Worksheet Set CurrSheet = ActiveSheet CurrSheet.Protect Contents:=True, Password:="1234" CurrSheet.Unprotect Password:="1234" CurrSheet.Protect Contents:=True, Password:="1234" CurrSheet.Protect UserInterfaceOnly:=True Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Offset(1, 0).Value = ComboBox1.Text LastRow.Offset(1, 1).Value = TextBox3.Text LastRow.Offset(1, 2).Value = TextBox1.Text LastRow.Offset(1, 3).Value = TextBox2.Text LastRow.Offset(1, 4).Value = ComboBox2.Text LastRow.Offset(1, 5).Value = ComboBox3.Text LastRow.Offset(1, 6).Value = ComboBox4.Text LastRow.Offset(1, 7).Value = DTPicker2.Value MsgBox "One record written to Sheet1" response = MsgBox("Do you want to enter another record?", _ vbYesNo) If response = vbYes Then ComboBox1.Text = "" TextBox3.Text = "" TextBox1.Text = "" TextBox2.Text = "" ComboBox2.Text = "" ComboBox3.Text = "" ComboBox4.Text = "" DTPicker2.Value = Date TextBox1.SetFocus Else Unload Me End If End Sub |
Help with Code
On Dec 10, 6:29 am, Alpineman2
wrote: Your help will be greatly appreciated. Here is what I am trying to accomplish. I would like to give User's of the workbook an ability to "sort and filter" while maintaining protection of the worksheet and workbook and ability to utilize the Userform, but am running into some problems. 1. When I save, close then reopen workbook, engage the Userform and once I click the commandbutton am promted for the password. Needless to say, the other users won't have the password. 2.Also, have been unable to figure out vba "sort and filter", intergration with # 1. Below is the code I'm using: Private Sub CommandButton1_Click() Dim CurrSheet As Worksheet Set CurrSheet = ActiveSheet CurrSheet.Protect Contents:=True, Password:="1234" CurrSheet.Unprotect Password:="1234" CurrSheet.Protect Contents:=True, Password:="1234" CurrSheet.Protect UserInterfaceOnly:=True Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Offset(1, 0).Value = ComboBox1.Text LastRow.Offset(1, 1).Value = TextBox3.Text LastRow.Offset(1, 2).Value = TextBox1.Text LastRow.Offset(1, 3).Value = TextBox2.Text LastRow.Offset(1, 4).Value = ComboBox2.Text LastRow.Offset(1, 5).Value = ComboBox3.Text LastRow.Offset(1, 6).Value = ComboBox4.Text LastRow.Offset(1, 7).Value = DTPicker2.Value MsgBox "One record written to Sheet1" response = MsgBox("Do you want to enter another record?", _ vbYesNo) If response = vbYes Then ComboBox1.Text = "" TextBox3.Text = "" TextBox1.Text = "" TextBox2.Text = "" ComboBox2.Text = "" ComboBox3.Text = "" ComboBox4.Text = "" DTPicker2.Value = Date TextBox1.SetFocus Else Unload Me End If End Sub Private Sub CommandButton1_Click() Dim CurrSheet As Worksheet Set CurrSheet = ActiveSheet CurrSheet.Protect Contents:=True, Password:="1234" CurrSheet.Unprotect Password:="1234" CurrSheet.Protect Contents:=True, Password:="1234" CurrSheet.Protect UserInterfaceOnly:=True Dim LastRow As Object Set LastRow = Sheet1.Range("a65536").End(xlUp) LastRow.Offset(1, 0).Value = ComboBox1.Text LastRow.Offset(1, 1).Value = TextBox3.Text LastRow.Offset(1, 2).Value = TextBox1.Text LastRow.Offset(1, 3).Value = TextBox2.Text LastRow.Offset(1, 4).Value = ComboBox2.Text LastRow.Offset(1, 5).Value = ComboBox3.Text LastRow.Offset(1, 6).Value = ComboBox4.Text LastRow.Offset(1, 7).Value = DTPicker2.Value MsgBox "One record written to Sheet1" response = MsgBox("Do you want to enter another record?", _ vbYesNo) If response = vbYes Then ComboBox1.Text = "" TextBox3.Text = "" TextBox1.Text = "" TextBox2.Text = "" ComboBox2.Text = "" ComboBox3.Text = "" ComboBox4.Text = "" DTPicker2.Value = Date TextBox1.SetFocus Else Unload Me End If End Sub I'm sorry I couldn't understand you very well. If you just want to protect the worksheet, but allow user to use sort and filter function. so you could allow user to sort when set worksheet protect options in code. When you change the worksheet's content. you should unprotect it firstly. and swap the above protecting back after you finish your content changing. |
| All times are GMT +1. The time now is 09:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com