Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Drop Down/List w/Code and Definition, only code entered when selec | Excel Worksheet Functions | |||
Code to find code | Excel Discussion (Misc queries) | |||
Convert a Number Code to a Text Code | Excel Discussion (Misc queries) | |||
Unprotect Code Module in Code | Excel Discussion (Misc queries) | |||
copying vba code to a standard code module | Excel Discussion (Misc queries) |