UserForm / Protection??? :(
 

Hello All,

I have a UserForm that inputs an employees name and wage into select
rows and columns. The problem I am running into is that some employees
iare inputing the data manually and not using the userform, which 90%
of the time result in inaccurate wage and spelling of names. Is there a
way to lock certain cells for userform use only?? Some info has to be
entered manually but, the names and wages don't.

If I need to explain more please let me know

Thanks

BTW, thanks for previous help

Rockee


---
Message posted from http://www.ExcelForum.com/

one username with multiple passwords
 

Hi! I'm new here.

Is it possible to have one "username" with multiple set of passwords? For
example, the username "APPLE" can be open with a password such as orange and
as well as grapes.

Thanks.

POL

UserForm / Protection??? :(
 

On Sat, 24 Jan 2004 21:04:36 -0600, Rockee052
wrote:

Hello All,

I have a UserForm that inputs an employees name and wage into select
rows and columns. The problem I am running into is that some employees
iare inputing the data manually and not using the userform, which 90%
of the time result in inaccurate wage and spelling of names. Is there a
way to lock certain cells for userform use only?? Some info has to be
entered manually but, the names and wages don't.

The only way that I can think of is to password protect the data entry
sheet (after unlocking the cells that will need manual entry), then
have the user form unprotect the worksheet as it needs to enter data.

Ensure that the form's ShowModal property is True (so that the users
can't get at the sheet while the form is open), and put code to relock
the sheet in the UserForm_QueryClose event procedure.

Post again if you aren't sure how to do any of this.

---------------------------------------------------------
Hank Scorpio
- Yes, yes, I know I've been missing for a while. Long story. And don't
ask what the size of my inbox is at the moment either... 8^
scorpionet who hates spam is at iprimus.com.au (You know what to do.)

UserForm / Protection??? :(
 

Hank,

Thanks for responding!!!!

I'm a newbie to vba so, i'm not sure how to place the coding.
Infact i'm not sure how to protect and unprotect with the user form. I
I understand your post correctly you were talking about protecting th
cells that the userform uses and leaving the cells that need to b
entered manually unprotected. So the user cant enter any data into th
cells unless they use the userform.
Here is my code for the userform, if you could lead me in the righ
direction I would really appreciate it.


Private Sub OKButton_Click()
On Error Resume Next
Dim activeRow As Integer
Dim activeCol As Integer
Dim errMsg As VbMsgBoxResult
Dim lindex As Integer
Dim ntoins As String
Dim wtoins As String
Dim nfound As Boolean

Dim rrow As Integer

activeRow = ActiveCell.Row
activeCol = ActiveCell.Column

lindex = ListBox.ListIndex
ntoins = ListBox.Column(0, lindex)
wtoins = ListBox.Column(1, lindex)
nfound = False

'has to start in bay 1
If activeCol < 2 Then
errMsg = MsgBox("Please select a cell in bay 1", vbExclamation
"Wrong Cell")
GoTo end_handler
End If

If frameBay.radio_1 = True Then
With ActiveSheet
.Cells(activeRow, 2) = ntoins
.Cells(activeRow, 7) = wtoins
End With
End If

If frameBay.radio_1 = True Then
rrow = 0
ElseIf frameBay.radio_2 = True Then
rrow = 6
ElseIf frameBay.radio_3 = True Then
rrow = 21
ElseIf frameBay.radio_4 = True Then
rrow = 40
End If

With ActiveSheet
If rrow < 0 Then
While nfound = False
If .Cells(rrow, 12).Value = "" Then
nfound = True
'insert the data
.Cells(rrow, 12).Value = ntoins
.Cells(rrow, 17).Value = wtoins
Else
rrow = rrow + 1
End If
Wend
End If
End With

end_handler:
'do nothing
End Su

--
Message posted from http://www.ExcelForum.com

UserForm / Protection??? :(
 

On Sat, 24 Jan 2004 22:50:38 -0600, Rockee052
wrote:

I'm a newbie to vba so, i'm not sure how to place the coding.
Infact i'm not sure how to protect and unprotect with the user form. If
I understand your post correctly you were talking about protecting the
cells that the userform uses and leaving the cells that need to be
entered manually unprotected. So the user cant enter any data into the
cells unless they use the userform.

That's correct.
- Select the range of cells that the user CAN enter directly into.
- Use the menu Format - Cells... - Protection and uncheck the Locked
box.
- Go to the menu Tools - Protection - Protect Sheet, and enter a
password in. Let's say it's 12345.

Right at this moment, the user will only be able to enter data into
the cells that you've unlocked.

Here is my code for the userform, if you could lead me in the right
direction I would really appreciate it.

Your code below is triggered whenever the OK button is clicked. One of
the things it does is to write data into the sheet. Except that now
you'd get an error when it tries to do that because the sheet is now
protected. The solution is to simply unprotect the sheet before you
first try to write to it:

Private Sub OKButton_Click()
On Error Resume Next
Dim activeRow As Integer
Dim activeCol As Integer
Dim errMsg As VbMsgBoxResult
Dim lindex As Integer
Dim ntoins As String
Dim wtoins As String
Dim nfound As Boolean

Dim rrow As Integer

activeRow = ActiveCell.Row
activeCol = ActiveCell.Column

lindex = ListBox.ListIndex
ntoins = ListBox.Column(0, lindex)
wtoins = ListBox.Column(1, lindex)
nfound = False

'has to start in bay 1
If activeCol < 2 Then
errMsg = MsgBox("Please select a cell in bay 1", vbExclamation,
"Wrong Cell")
GoTo end_handler
End If

Here's where you first write to the sheet, so at this point simply add
the code:

'Unprotect the sheet before writing to it.
'We check the current protection status simply as
'a precaution.
If ActiveSheet.ProtectContents = True Then
ActiveSheet.Unprotect Password:="12345"
End If


If frameBay.radio_1 = True Then
With ActiveSheet
Cells(activeRow, 2) = ntoins
Cells(activeRow, 7) = wtoins
End With
End If

If frameBay.radio_1 = True Then
rrow = 0
ElseIf frameBay.radio_2 = True Then
rrow = 6
ElseIf frameBay.radio_3 = True Then
rrow = 21
ElseIf frameBay.radio_4 = True Then
rrow = 40
End If

With ActiveSheet
If rrow < 0 Then
While nfound = False
If .Cells(rrow, 12).Value = "" Then
nfound = True
'insert the data
Cells(rrow, 12).Value = ntoins
Cells(rrow, 17).Value = wtoins
Else
rrow = rrow + 1
End If
Wend
End If
End With

end_handler:
'do nothing

Instead of doing nothing, here we'd turn protection back on:

'Reenable protection
If ActiveSheet.ProtectContents = False Then
ActiveSheet.Protect Password:="12345"
End If

End Sub

But just to be extra cautious, I'd also add protection enablement to a
procedure called QueryClose. This is triggered whenever the user form
is closed, whether by clicking the X in the top right corner, shutting
down Excel or shutting down Windows.

You can just copy the following straight in:

Private Sub UserForm_QueryClose(Cancel As Integer, _
CloseMode As Integer)

'Reenable protection
If ActiveSheet.ProtectContents = False Then
ActiveSheet.Protect Password:="12345"
End If

That's all there is to it. Hope this helps.

---------------------------------------------------------
Hank Scorpio
- Yes, yes, I know I've been missing for a while. Long story. And don't
ask what the size of my inbox is at the moment either... 8^
scorpionet who hates spam is at iprimus.com.au (You know what to do.)

UserForm / Protection??? :(
 

Hanks,

That's exactly what I needed.

Thank you very much!!!!!!!!!


;)
:)
;)
:)

Rockee


---
Message posted from http://www.ExcelForum.com/

UserForm / Protection??? :(
 

*Han

--
Message posted from http://www.ExcelForum.com

one username with multiple passwords
 

In what context - generally, excel passwords don't require user
identification. This has changed somewhat in xl2002 and later for sheet
level protection. Perhaps you could explain what you are trying to do (and
even if this is actually a question about Excel).

--
Regards,
Tom Ogilvy

act wrote in message
...
Hi! I'm new here.

Is it possible to have one "username" with multiple set of passwords? For
example, the username "APPLE" can be open with a password such as orange
and
as well as grapes.

Thanks.

POL