I have a userform that is set up when the user hits the submit button
it automatically loads the textboxes that the user filled out in the
userform to a worksheet. The problem I'm having is that I want to lock
the worksheet that the data is loading from the userfom, but if I lock
the worksheet then the submit button will not work. The code crashes
because I locked the worksheet.
Is there any way let the users submit the data gathered on the
userform but only give the users read access only on the worksheet?

Have your code unprotect the sheet, write the data and reprotect the sheet.

On 09/15/2010 09:59, AccessDB wrote:
I have a userform that is set up when the user hits the submit button
it automatically loads the textboxes that the user filled out in the
userform to a worksheet. The problem I'm having is that I want to lock
the worksheet that the data is loading from the userfom, but if I lock
the worksheet then the submit button will not work. The code crashes
because I locked the worksheet.
Is there any way let the users submit the data gathered on the
userform but only give the users read access only on the worksheet?

--
Dave Peterson

On Sep 15, 12:49*pm, Dave Peterson wrote:
Have your code unprotect the sheet, write the data and reprotect the sheet.

In your code that does the writing:

Private Sub cmdRealSubmit_Click()

.... your declarations here

Dim wks as worksheet

set wks = worksheets("inquiry")
with wks
.unprotect password:="topsecretpasswordhere")
'your existing code to write the stuff
.protect password:="topsecretpasswordhere")
end with

....any other code you need here

End Sub

On 09/15/2010 12:53, AccessDB wrote:
On Sep 15, 12:49 pm, Dave wrote:
Have your code unprotect the sheet, write the data and reprotect the sheet.

On 09/15/2010 09:59, AccessDB wrote:

I have a userform that is set up when the user hits the submit button
it automatically loads the textboxes that the user filled out in the
userform to a worksheet. The problem I'm having is that I want to lock
the worksheet that the data is loading from the userfom, but if I lock
the worksheet then the submit button will not work. The code crashes
because I locked the worksheet.
Is there any way let the users submit the data gathered on the
userform but only give the users read access only on the worksheet?

--
Dave Peterson

Can you please help supply that code? Worksheet or tab that data is
getting dumped is called "Inquiry" and the userform is called
"frmInquiry" and the command button is called cmdRealSubmit.

--
Dave Peterson

On Sep 15, 12:58*pm, Dave Peterson wrote:
In your code that does the writing:

Private Sub cmdRealSubmit_Click()

... your declarations here

Dim wks as worksheet

set wks = worksheets("inquiry")
with wks
* * .unprotect password:="topsecretpasswordhere")
* * 'your existing code to write the stuff
* * .protect password:="topsecretpasswordhere")
end with

...any other code you need here

End Sub

On 09/15/2010 12:53, AccessDB wrote:





On Sep 15, 12:49 pm, Dave *wrote:
Have your code unprotect the sheet, write the data and reprotect the sheet.

On 09/15/2010 09:59, AccessDB wrote:

I have a userform that is set up when the user hits the submit button
it automatically loads the textboxes that the user filled out in the
userform to a worksheet. The problem I'm having is that I want to lock
the worksheet that the data is loading from the userfom, but if I lock
the worksheet then the submit button will not work. The code crashes
because I locked the worksheet.
Is there any way let the users submit the data gathered on the
userform but only give the users read access only on the worksheet?

--
Dave Peterson

Can you please help supply that code? Worksheet or tab that data is
getting dumped is called "Inquiry" and the userform is called
"frmInquiry" and the command button is called cmdRealSubmit.

--
Dave Peterson- Hide quoted text -

- Show quoted text -

The code works, thank you very much.

On Sep 15, 2:10*pm, AccessDB wrote:
On Sep 15, 12:58*pm, Dave Peterson wrote:





In your code that does the writing:

Private Sub cmdRealSubmit_Click()

... your declarations here

Dim wks as worksheet

set wks = worksheets("inquiry")
with wks
* * .unprotect password:="topsecretpasswordhere")
* * 'your existing code to write the stuff
* * .protect password:="topsecretpasswordhere")
end with

...any other code you need here

End Sub

On 09/15/2010 12:53, AccessDB wrote:

On Sep 15, 12:49 pm, Dave *wrote:
Have your code unprotect the sheet, write the data and reprotect the sheet.

On 09/15/2010 09:59, AccessDB wrote:

I have a userform that is set up when the user hits the submit button
it automatically loads the textboxes that the user filled out in the
userform to a worksheet. The problem I'm having is that I want to lock
the worksheet that the data is loading from the userfom, but if I lock
the worksheet then the submit button will not work. The code crashes
because I locked the worksheet.
Is there any way let the users submit the data gathered on the
userform but only give the users read access only on the worksheet?

--
Dave Peterson

Can you please help supply that code? Worksheet or tab that data is
getting dumped is called "Inquiry" and the userform is called
"frmInquiry" and the command button is called cmdRealSubmit.

--
Dave Peterson- Hide quoted text -

- Show quoted text -

The code works, thank you very much.- Hide quoted text -

- Show quoted text -

Now I have another issue for you. I have a textbox called
txtTargetPrice. In this textbox I would like the user to be able to
type in a US dollar amount with two decimal point (example: $4.63).
Below is the code I have. Notice that the ' codes I tried do not work
that's why I have ' the code. See if you can help me with this code.

Private Sub txtTargetPrice_Change()
'Private Sub txtTargetPrice_Exit(ByVal Cancel As
MSForms.ReturnBoolean)
If TypeName(Me.ActiveControl) = "TextBox" Then
With Me.ActiveControl
If Not IsNumeric(.Value) And .Value < vbNullString Then
MsgBox "Sorry, only numbers allowed"
.Value = vbNullString
End If
End With
End If

'txtTargetPrice = Format(txtTargetPrice, "$###.##")
'txtTargetPrice = Format(txtTargetPrice, "Currency")
End Sub