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 <peter...@XSPAMverizon.net> 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.

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 Peterson<peter...@XSPAMverizon.net> 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 <peter...@XSPAMverizon.net> 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 Peterson<peter...@XSPAMverizon.net> *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 <scl1...@gmail.com> wrote:
> On Sep 15, 12:58*pm, Dave Peterson <peter...@XSPAMverizon.net> 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 Peterson<peter...@XSPAMverizon.net> *wrote:
> > >> Have your code unprotect the sheet, write the data and reprotect thesheet.
>
> > >> 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

I would ask them to just enter the numbers (and decimal character) and then let
the formatting (use the _Exit event) to make it look pretty.

If you want to allow them to enter the $, then take it out before you do any check.

Option Explicit
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim myStr As String
Dim InvalidChars As String
Dim cCtr As Long

myStr = Me.TextBox1.Value

'allow the user to type $ or commas (or their 1000's separator)
InvalidChars = "$" & Application.ThousandsSeparator

For cCtr = 1 To Len(InvalidChars)
myStr = Replace(myStr, Mid(InvalidChars, cCtr, 1), "")
Next cCtr

If IsNumeric(myStr) Then
Me.TextBox1.Value = Format(myStr, "$#,##0.00")
Me.Label1.Caption = ""
Else
Cancel = True
Me.Label1.Caption = "Please enter a number!"
End If
End Sub

On 09/15/2010 14:15, AccessDB wrote:
> On Sep 15, 2:10 pm, AccessDB<scl1...@gmail.com> wrote:
>> On Sep 15, 12:58 pm, Dave Peterson<peter...@XSPAMverizon.net> 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 Peterson<peter...@XSPAMverizon.net> 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

--
Dave Peterson

On Sep 15, 2:27*pm, Dave Peterson <peter...@XSPAMverizon.net> wrote:
> I would ask them to just enter the numbers (and decimal character) and then let
> the formatting (use the _Exit event) to make it look pretty.
>
> If you want to allow them to enter the $, then take it out before you do any check.
>
> Option Explicit
> Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> * * *Dim myStr As String
> * * *Dim InvalidChars As String
> * * *Dim cCtr As Long
>
> * * *myStr = Me.TextBox1.Value
>
> * * *'allow the user to type $ or commas (or their 1000's separator)
> * * *InvalidChars = "$" & Application.ThousandsSeparator
>
> * * *For cCtr = 1 To Len(InvalidChars)
> * * * * *myStr = Replace(myStr, Mid(InvalidChars, cCtr, 1), "")
> * * *Next cCtr
>
> * * *If IsNumeric(myStr) Then
> * * * * *Me.TextBox1.Value = Format(myStr, "$#,##0.00")
> * * * * *Me.Label1.Caption = ""
> * * *Else
> * * * * *Cancel = True
> * * * * *Me.Label1.Caption = "Please enter a number!"
> * * *End If
> End Sub
>
> On 09/15/2010 14:15, AccessDB wrote:
>
>
>
>
>
> > On Sep 15, 2:10 pm, AccessDB<scl1...@gmail.com> *wrote:
> >> On Sep 15, 12:58 pm, Dave Peterson<peter...@XSPAMverizon.net> *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 Peterson<peter...@XSPAMverizon.net> * *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
>
> --
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Awsome. Code worked again, GREATLY appreciated.
Three more question for you.
1. I have combo box called cboNewSpec. With this combo box I want the
restrict to user to be able to type any text at all. All the user
should be able to do is pick from my dropdown list. Code that I have:
Private Sub UserForm_Initialize()
With cboNewSpec
.AddItem "yes"
.AddItem "no"
End With
End Sub

2. I have a command button called cmdBrowse. I want to let the user
load a picture or attachment to the userform. With the cmdBrowse, I
have textbox (txtPic) to get the link. However, all it is doing is
copying the link/location of the picture/file. It is not copying the
picture to the userform and "inquiry worksheet". Here's my code for
this:
Private Sub cmdBrowse_Click()
Dim fName As String
fName = Application.GetOpenFilename()
If Not fName = "False" Then
txtPic.Value = fName
End If
End Sub

3. In the userform, for some text boxes I want to allow the user to
cut and paste data into my text box(es) on my userform. I do not have
any code for this one.
Please see if you can help me again. And thank you in advance for all
that you've helped.

#1. First, I'd use a checkbox to get a yes/no (on/off, include/don't include,
any boolean) value.

But you can change the combobox's .Style to fmStyleDropDownList.

> With cboNewSpec
.Style = fmStyleDropDownList
> .AddItem "yes"
> .AddItem "no"
> End With

#2. Open excel, open your workbook. Open the VBE.
With your project selected, click on Tools|References.
Make sure that "OLE Automation" is checked.

Then use code like:

Option Explicit
Private Sub CommandButton2_Click()

Dim fName As Variant 'could be boolean

fName = Application.GetOpenFilename _
(filefilter:="Picture files, *.bmp;*.jpg;*.gif")

If fName = False Then
Exit Sub
End If

Me.Image1.Picture = loadpicture(fName)

End Sub

#3. They can copy the text from almost(?) anywhere by selecting that text and
rightclicking and choosing Copy (or hit ctrl-c).

Then go to where it should be pasted and use ctrl-v (or rightclick|paste).

I'm not sure what you're really asking with this one.

On 09/15/2010 15:08, AccessDB wrote:
> On Sep 15, 2:27 pm, Dave Peterson<peter...@XSPAMverizon.net> wrote:
>> I would ask them to just enter the numbers (and decimal character) and then let
>> the formatting (use the _Exit event) to make it look pretty.
>>
>> If you want to allow them to enter the $, then take it out before you do any check.
>>
>> Option Explicit
>> Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
>> Dim myStr As String
>> Dim InvalidChars As String
>> Dim cCtr As Long
>>
>> myStr = Me.TextBox1.Value
>>
>> 'allow the user to type $ or commas (or their 1000's separator)
>> InvalidChars = "$"& Application.ThousandsSeparator
>>
>> For cCtr = 1 To Len(InvalidChars)
>> myStr = Replace(myStr, Mid(InvalidChars, cCtr, 1), "")
>> Next cCtr
>>
>> If IsNumeric(myStr) Then
>> Me.TextBox1.Value = Format(myStr, "$#,##0.00")
>> Me.Label1.Caption = ""
>> Else
>> Cancel = True
>> Me.Label1.Caption = "Please enter a number!"
>> End If
>> End Sub
>>
>> On 09/15/2010 14:15, AccessDB wrote:
>>
>>
>>
>>
>>
>>> On Sep 15, 2:10 pm, AccessDB<scl1...@gmail.com> wrote:
>>>> On Sep 15, 12:58 pm, Dave Peterson<peter...@XSPAMverizon.net> 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 Peterson<peter...@XSPAMverizon.net> 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
>>
>> --
>> Dave Peterson- Hide quoted text -
>>
>> - Show quoted text -
>
> Awsome. Code worked again, GREATLY appreciated.
> Three more question for you.
> 1. I have combo box called cboNewSpec. With this combo box I want the
> restrict to user to be able to type any text at all. All the user
> should be able to do is pick from my dropdown list. Code that I have:
> Private Sub UserForm_Initialize()
> With cboNewSpec
> .AddItem "yes"
> .AddItem "no"
> End With
> End Sub
>
> 2. I have a command button called cmdBrowse. I want to let the user
> load a picture or attachment to the userform. With the cmdBrowse, I
> have textbox (txtPic) to get the link. However, all it is doing is
> copying the link/location of the picture/file. It is not copying the
> picture to the userform and "inquiry worksheet". Here's my code for
> this:
> Private Sub cmdBrowse_Click()
> Dim fName As String
> fName = Application.GetOpenFilename()
> If Not fName = "False" Then
> txtPic.Value = fName
> End If
> End Sub
>
> 3. In the userform, for some text boxes I want to allow the user to
> cut and paste data into my text box(es) on my userform. I do not have
> any code for this one.
> Please see if you can help me again. And thank you in advance for all
> that you've helped.
>

--
Dave Peterson

On Sep 15, 5:37*pm, Dave Peterson <peter...@XSPAMverizon.net> wrote:
> #1. *First, I'd use a checkbox to get a yes/no (on/off, include/don't include,
> any boolean) value.
>
> But you can change the combobox's .Style to fmStyleDropDownList.
>
> *> * * *With cboNewSpec
> * * * * * * .Style = fmStyleDropDownList
> *> * * * * *.AddItem "yes"
> *> * * * * *.AddItem "no"
> *> * * *End With
>
> #2. *Open excel, open your workbook. *Open the VBE.
> With your project selected, click on Tools|References.
> Make sure that "OLE Automation" is checked.
>
> Then use code like:
>
> Option Explicit
> Private Sub CommandButton2_Click()
>
> * * *Dim fName As Variant 'could be boolean
>
> * * *fName = Application.GetOpenFilename _
> * * * * *(filefilter:="Picture files, *.bmp;*.jpg;*.gif")
>
> * * *If fName = False Then
> * * * * *Exit Sub
> * * *End If
>
> * * *Me.Image1.Picture = loadpicture(fName)
>
> End Sub
>
> #3. *They can copy the text from almost(?) anywhere by selecting that text and
> rightclicking and choosing Copy (or hit ctrl-c).
>
> Then go to where it should be pasted and use ctrl-v (or rightclick|paste)..
>
> I'm not sure what you're really asking with this one.
>
> On 09/15/2010 15:08, AccessDB wrote:
>
>
>
>
>
> > On Sep 15, 2:27 pm, Dave Peterson<peter...@XSPAMverizon.net> *wrote:
> >> I would ask them to just enter the numbers (and decimal character) andthen let
> >> the formatting (use the _Exit event) to make it look pretty.
>
> >> If you want to allow them to enter the $, then take it out before you do any check.
>
> >> Option Explicit
> >> Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
> >> * * * Dim myStr As String
> >> * * * Dim InvalidChars As String
> >> * * * Dim cCtr As Long
>
> >> * * * myStr = Me.TextBox1.Value
>
> >> * * * 'allow the user to type $ or commas (or their 1000's separator)
> >> * * * InvalidChars = "$"& *Application.ThousandsSeparator
>
> >> * * * For cCtr = 1 To Len(InvalidChars)
> >> * * * * * myStr = Replace(myStr, Mid(InvalidChars, cCtr, 1), "")
> >> * * * Next cCtr
>
> >> * * * If IsNumeric(myStr) Then
> >> * * * * * Me.TextBox1.Value = Format(myStr, "$#,##0.00")
> >> * * * * * Me.Label1.Caption = ""
> >> * * * Else
> >> * * * * * Cancel = True
> >> * * * * * Me.Label1.Caption = "Please enter a number!"
> >> * * * End If
> >> End Sub
>
> >> On 09/15/2010 14:15, AccessDB wrote:
>
> >>> On Sep 15, 2:10 pm, AccessDB<scl1...@gmail.com> * *wrote:
> >>>> On Sep 15, 12:58 pm, Dave Peterson<peter...@XSPAMverizon.net> * *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 Peterson<peter...@XSPAMverizon.net> * * *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 inthe
> >>>>>>>> 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
>
> >> --
> >> Dave Peterson- Hide quoted text -
>
> >> - Show quoted text -
>
> > Awsome. Code worked again, GREATLY appreciated.
> > Three more question for you.
> > 1. I have combo box called cboNewSpec. With this combo box I want the
> > restrict to user to be able to type any text at all. All the user
> > should be able to do is pick from my dropdown list. Code that I have:
> > Private Sub UserForm_Initialize()
> > * * *With cboNewSpec
> > * * * * *.AddItem "yes"
> > * * * * *.AddItem "no"
> > * * *End With
> > End Sub
>
> > 2. I have a command button called cmdBrowse. I want to let the user
> > load a picture or attachment to the userform. With the cmdBrowse, I
> > have textbox (txtPic) to get the link. However, all it is doing is
> > copying the link/location of the picture/file. It is not copying the
> > picture to the userform and "inquiry worksheet". Here's my code for
> > this:
> > Private Sub cmdBrowse_Click()
> > Dim fName As String
> > fName = Application.GetOpenFilename()
> > If Not fName = "False" Then
> > txtPic.Value = fName
> > End If
> > End Sub
>
> > 3. In the userform, for some text boxes I want to allow the user to
> > cut and paste data into my text box(es) on my userform. I do not have
> > any code for this one.
> > Please see if you can help me again. And thank you in advance for all
> > that you've helped.
>
> --
> Dave Peterson- Hide quoted text -
>
> - Show quoted text -

Thanks for all the code. I don't know what I was thinking about #3, I
can cut and paste.
In your code for #2, it does let me load a picture to the userform.
But I also need it to load on to the "inquiry" worksheet when I click
my cmdRealSubmit button. Now I need the a code in the cmdRealSubmit
are to load it to the "inquiry" worksheet. Do you think you can help
on this one too? Here's what I have and it does not work:

Private Sub cmdRealSubmit_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Inquiry")
With ws
.Unprotect Password:="xxxx"

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

'I have codes for row 1-28 above here that I didn't cut and paste to
save you the agony

ws.Cells(iRow, 29).Value = Me.Image1.Picture
' Hid codes to make all fields to value of ""
Unload Me
.Protect Password:="xxxx"
End With
End Sub