Simple UserForm/Worksheet Hide/Unhide

[Chris1201]

Okay, this is probably a lot simpler than I'm thinking. Only problem is, I'm new to programming userforms, so I'll need help along the way.

I have 2 worksheets set up, one that's just a blank worksheet with a button on it that pops up my UserForm. The other worksheet has all the current data on it and is hidden and protected.

One of the buttons on the UserForm I want to use to hide the userform, unhide and view the main worksheet, but not make changes except for autofiltering and sorting(sorting is another issue altogether). Then I want to make a macro on cell select that hides that worksheet and brings the UserForm back up.

Is this possible?

PS. On a side note: When I protect the worksheet, it won't let me sort the data even though I have 'Sort' selected as an allowed action. Any way around this?

[blane245]

Rather than dropping out of the VBA environment, why don't you let the VBA environment control the autofilter and sorting functions? That might be a little smoother GUI. You can always hide and show the spreadsheet from VBA so that the user can see when necessary.

[Chris1201]

That's an interesting idea. I also ran into another problem when I started experimenting with data entry and found out that I can't use the VBA to enter data into a protected sheet. Is there also a way around that?

[royUK]

You need to unprotect the sheet first

Sheet1.Unprotect "your password"
'your code
Sheet1.Protect "your password"

[Chris1201]

You need to unprotect the sheet first

Sheet1.Unprotect "your password"
'your code
Sheet1.Protect "your password"

I tried this and got the same thing. Here's the code I used. Name was still Sheet1, but I don't yet have a password to unprotect it yet since right now I'm just trying to get it to work.

Private Sub cmdSubmit1_Click()

Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("MAIN")

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
  .End(xlUp).Offset(1, 0).Row
  
'check for a name
If Trim(Me.txtName1.Value) = "" Then
  Me.txtName1.SetFocus
  MsgBox "Please enter member's name"
  Exit Sub
End If

Sheet1.Unprotect ""

'copy data to database
ws.Cells(iRow, 1).Value = Me.txtRate1.Value
ws.Cells(iRow, 2).Value = Me.txtName1.Value
ws.Cells(iRow, 3).Value = Me.txtDivision1.Value
ws.Cells(iRow, 4).Value = Me.txtRcvd1.Value
ws.Cells(iRow, 5).Value = Me.txtLoss1.Value

Sheet1.Protect ""


'clear the data
Me.txtName1.Value = ""
Me.txtRate1.Value = ""
Me.txtDivision1.Value = ""
Me.txtRcvd1.Value = ""
Me.txtLoss1.Value = ""
Me.txtName1.SetFocus

End Sub