Allow text in locked cells on a worksheet from a userform

[AccessDB]

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]

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

 

[AccessDB]

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

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]

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

 

[AccessDB]

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

The code works, thank you very much.

 

[AccessDB]

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]

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

 

[AccessDB]

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

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]

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

 

[AccessDB]

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

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]

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

....

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

 

[Dave Peterson]

ps. Watch out for typos!

That code wasn't tested or compiled.

 

[Dave Peterson]

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

 

[AccessDB]

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

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]

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.

 

[AccessDB]

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.

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]

Just the same suggestion that I gave last time.

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?