Help with Code

A

Alpineman2

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
 
F

fujing1003

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
Click to expand...

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

make a data entry into hyperlink using vba? 3
code crashing my programme??????? 4
Please check my code!!!! 1
userform question 3
selecting the next cell down 2
Removing duplicates 6
lock cells after entry 1
MsgBox Code Error 8

Top