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

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, wrote:
On Sep 15, 12:58 pm, Dave 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

--
Dave Peterson

On Sep 15, 2:27*pm, Dave Peterson 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, *wrote:
On Sep 15, 12:58 pm, Dave *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

--
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 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, wrote:
On Sep 15, 12:58 pm, Dave 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

--
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 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 *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, * *wrote:
On Sep 15, 12:58 pm, Dave * *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

--
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

I would just save the filename that the user specified in the getopenfilename
(in a module level variable or in a hidden label on the userform), then insert
the picture using that filename.

If you use the module level variable:

Option Explicit
'this is moved out of the subroutine!
Dim fName As Variant 'could be boolean

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

=======
Then you can use that filename in the other procedu

....

Dim myPict as picture
dim myAspectRatio as double
....

with ws
.unprotect password:="xxx"
Set myPict = .Pictures.Insert(fname)

with .Cells(iRow, 29)
myPict.Top = .Top
myPict.Left = .Left

myPict.Height = .Height
myPict.Width = myAspectRatio * .Height

If myPict.Width .Width Then
'too wide for the cell
'With the aspectratio locked, the
'reducing the width will reduce the height
myPict.Width = .Width
End If
End With
'other code to clear contents???
.protect password:="xxx"
end with





On 09/16/2010 12:55, AccessDB wrote:
On Sep 15, 5:37 pm, Dave 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 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, wrote:
On Sep 15, 12:58 pm, Dave 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

--
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

--
Dave Peterson

ps. Watch out for typos!

That code wasn't tested or compiled.

I copied|pasted the Dim statement. I should have Cut|Pasted:

Option Explicit
Dim fName As Variant 'could be boolean

Private Sub CommandButton2_Click()

'don't declare fName in this procedure

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

If fName = False Then
Exit Sub
End If

Me.Image1.Picture = loadpicture(fName)

End Sub

On 09/16/2010 13:32, Dave Peterson wrote:
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

--
Dave Peterson

On Sep 16, 1:35*pm, Dave Peterson wrote:
I copied|pasted the Dim statement. *I should have Cut|Pasted:

Option Explicit
Dim fName As Variant 'could be boolean

Private Sub CommandButton2_Click()

* * * *'don't declare fName in this procedure

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

* * * *If fName = False Then
* * * * * *Exit Sub
* * * *End If

* * * *Me.Image1.Picture = loadpicture(fName)

End Sub

On 09/16/2010 13:32, Dave Peterson wrote:

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

--
Dave Peterson

Code not working. There is not picture in my worksheet, the cell
(iRow, 29) is empty. I have no clue why it is not working, can you
please review my code:
Option Explicit
Dim fName As Variant 'could be boolean

Private Sub cmdBrowse_Click()
'don't declare fName in this procedure


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


If fName = False Then
Exit Sub
End If


Me.Image1.Picture = LoadPicture(fName)


End Sub


Private Sub cmdRealSubmit_Click()
Dim myPict As Picture
Dim myAspectRatio As Double
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Inquiry")
With ws
.Unprotect Password:="xxxx"
Set myPict = .Pictures.Insert(fName)


'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'Code like this one for 1-28
'ws.Cells(iRow, 1).Value = Me.txtCustomerName.Value
With .Cells(iRow, 29)
myPict.Top = .Top
myPict.Left = .Left


myPict.Height = .Height
myPict.Width = myAspectRatio * .Height


If myPict.Width .Width Then
'too wide for the cell
'With the aspectratio locked, the
'reducing the width will reduce the height
myPict.Width = .Width
End If
End With
'Codes like this one that clears out my textboxes/combo boxes etc...
'Me.txtCustomerName.Value = ""
Unload Me
.Protect Password:="mrsl"
End With
End Sub

Check your passwords. You used xxxx and mrsl. Did you get an error when you
tried it or did it just fail quietly?

But nothing jumps out at me (but I didn't set up a test workbook).

Try stepping through the code to make sure that the picture is inserted correctly.

Then continue stepping through the code to see if the picture is positioned
correctly.


On 09/16/2010 14:07, AccessDB wrote:
On Sep 16, 1:35 pm, Dave wrote:
I copied|pasted the Dim statement. I should have Cut|Pasted:

Option Explicit
Dim fName As Variant 'could be boolean

Private Sub CommandButton2_Click()

'don't declare fName in this procedure

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

If fName = False Then
Exit Sub
End If

Me.Image1.Picture = loadpicture(fName)

End Sub

On 09/16/2010 13:32, Dave Peterson wrote:

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

--
Dave Peterson

Code not working. There is not picture in my worksheet, the cell
(iRow, 29) is empty. I have no clue why it is not working, can you
please review my code:
Option Explicit
Dim fName As Variant 'could be boolean

Private Sub cmdBrowse_Click()
'don't declare fName in this procedure


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


If fName = False Then
Exit Sub
End If


Me.Image1.Picture = LoadPicture(fName)


End Sub


Private Sub cmdRealSubmit_Click()
Dim myPict As Picture
Dim myAspectRatio As Double
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Inquiry")
With ws
.Unprotect Password:="xxxx"
Set myPict = .Pictures.Insert(fName)


'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'Code like this one for 1-28
'ws.Cells(iRow, 1).Value = Me.txtCustomerName.Value
With .Cells(iRow, 29)
myPict.Top = .Top
myPict.Left = .Left


myPict.Height = .Height
myPict.Width = myAspectRatio * .Height


If myPict.Width .Width Then
'too wide for the cell
'With the aspectratio locked, the
'reducing the width will reduce the height
myPict.Width = .Width
End If
End With
'Codes like this one that clears out my textboxes/combo boxes etc...
'Me.txtCustomerName.Value = ""
Unload Me
.Protect Password:="mrsl"
End With
End Sub

--
Dave Peterson

On Sep 16, 6:17*pm, Dave Peterson wrote:
Check your passwords. *You used xxxx and mrsl. *Did you get an error when you
tried it or did it just fail quietly?

But nothing jumps out at me (but I didn't set up a test workbook).

Try stepping through the code to make sure that the picture is inserted correctly.

Then continue stepping through the code to see if the picture is positioned
correctly.

On 09/16/2010 14:07, AccessDB wrote:





On Sep 16, 1:35 pm, Dave *wrote:
I copied|pasted the Dim statement. *I should have Cut|Pasted:

Option Explicit
Dim fName As Variant 'could be boolean

Private Sub CommandButton2_Click()

* * * * 'don't declare fName in this procedure

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

* * * * If fName = False Then
* * * * * * Exit Sub
* * * * End If

* * * * Me.Image1.Picture = loadpicture(fName)

End Sub

On 09/16/2010 13:32, Dave Peterson wrote:

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

--
Dave Peterson

Code not working. There is not picture in my worksheet, the cell
(iRow, 29) is empty. I have no clue why it is not working, can you
please review my code:
Option Explicit
Dim fName As Variant 'could be boolean

Private Sub cmdBrowse_Click()
* * * * 'don't declare fName in this procedure

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

* * * * If fName = False Then
* * * * * * Exit Sub
* * * * End If

* * * * Me.Image1.Picture = LoadPicture(fName)

End Sub

Private Sub cmdRealSubmit_Click()
Dim myPict As Picture
Dim myAspectRatio As Double
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Inquiry")
With ws
* * *.Unprotect Password:="xxxx"
* * *Set myPict = .Pictures.Insert(fName)

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
* *.End(xlUp).Offset(1, 0).Row
'Code like this one for 1-28
'ws.Cells(iRow, 1).Value = Me.txtCustomerName.Value
With .Cells(iRow, 29)
* * * * *myPict.Top = .Top
* * * * *myPict.Left = .Left

* * * * *myPict.Height = .Height
* * * * *myPict.Width = myAspectRatio * .Height

* If myPict.Width *.Width Then
* * * * * * * 'too wide for the cell
* * * * * * * 'With the aspectratio locked, the
* * * * * * * 'reducing the width will reduce the height
* * *myPict.Width = .Width
* *End If
End With
'Codes like this one that clears out my textboxes/combo boxes etc...
'Me.txtCustomerName.Value = ""
* * *Unload Me
* * *.Protect Password:="mrsl"
End With
End Sub

--
Dave Peterson- Hide quoted text -

- Show quoted text -

Yes to password is wrong but that was my fault. I just didn't use
"xxxx" on this post. The real password on my code is "mrsl" for both
locations. When I press the cmdRealSubmit button everything is fine
except for the picture, it does not load to my "Inquiry" worksheet.
Any suggestions?

Just the same suggestion that I gave last time.

On 09/17/2010 07:43, AccessDB wrote:
On Sep 16, 6:17 pm, Dave wrote:
Check your passwords. You used xxxx and mrsl. Did you get an error when you
tried it or did it just fail quietly?

But nothing jumps out at me (but I didn't set up a test workbook).

Try stepping through the code to make sure that the picture is inserted correctly.

Then continue stepping through the code to see if the picture is positioned
correctly.

On 09/16/2010 14:07, AccessDB wrote:





On Sep 16, 1:35 pm, Dave wrote:
I copied|pasted the Dim statement. I should have Cut|Pasted:

Option Explicit
Dim fName As Variant 'could be boolean

Private Sub CommandButton2_Click()

'don't declare fName in this procedure

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

If fName = False Then
Exit Sub
End If

Me.Image1.Picture = loadpicture(fName)

End Sub

On 09/16/2010 13:32, Dave Peterson wrote:

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

--
Dave Peterson

Code not working. There is not picture in my worksheet, the cell
(iRow, 29) is empty. I have no clue why it is not working, can you
please review my code:
Option Explicit
Dim fName As Variant 'could be boolean

Private Sub cmdBrowse_Click()
'don't declare fName in this procedure

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

If fName = False Then
Exit Sub
End If

Me.Image1.Picture = LoadPicture(fName)

End Sub

Private Sub cmdRealSubmit_Click()
Dim myPict As Picture
Dim myAspectRatio As Double
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Inquiry")
With ws
.Unprotect Password:="xxxx"
Set myPict = .Pictures.Insert(fName)

'find first empty row in database
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'Code like this one for 1-28
'ws.Cells(iRow, 1).Value = Me.txtCustomerName.Value
With .Cells(iRow, 29)
myPict.Top = .Top
myPict.Left = .Left

myPict.Height = .Height
myPict.Width = myAspectRatio * .Height

If myPict.Width .Width Then
'too wide for the cell
'With the aspectratio locked, the
'reducing the width will reduce the height
myPict.Width = .Width
End If
End With
'Codes like this one that clears out my textboxes/combo boxes etc...
'Me.txtCustomerName.Value = ""
Unload Me
.Protect Password:="mrsl"
End With
End Sub

--
Dave Peterson- Hide quoted text -

- Show quoted text -

Yes to password is wrong but that was my fault. I just didn't use
"xxxx" on this post. The real password on my code is "mrsl" for both
locations. When I press the cmdRealSubmit button everything is fine
except for the picture, it does not load to my "Inquiry" worksheet.
Any suggestions?

--
Dave Peterson