User form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi All:

Would it be better to use code with a msg box to enter data into a range of
cells or would a UserForm be better? Is it possible to use a UserForm to
place data in only a range of cells? If that can be done can it place the
data in the selected cell within that range?

Mike Rogers
 
Dave,

Yes an input box! I am trying to input information into a range of cells
that I can have locked. Thereby disallowing anyone from copy and pasting the
information in one cell to another. Would the input box be called by
selecting a cell in the desired range? Or would it be called by a command
button?
 
Either way is ok with me.

If you want it based on selecting a cell, you could use a worksheet event:

Rightclick on the worksheet tab that should have the behavior. Select view
code. Paste this in:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a2:a99")) Is Nothing Then
Exit Sub
End If

UserForm1.Show

End Sub

You'll have to adjust the range and the userform name.

Or something like this to use an inputbox:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myStr As String

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("a2:a99")) Is Nothing Then
Exit Sub
End If

myStr = InputBox(Prompt:="what do you want to enter in " _
& Target.Address(0, 0) & "?")

If Trim(myStr) = "" Then
'do nothing
Else
Me.Unprotect Password:="hi"
Application.EnableEvents = False
Target.Value = myStr
Application.EnableEvents = True
Me.Protect Password:="hi"
End If

End Sub
 
Dave

Wow thanks for the complete response!!!! I really do appreiciate it. I
think the simplest solution is the imput box. But I have a problem with
either way..... I already have a Private Sub Worksheet_SelectionChange(ByVal
Target As Range) procedure in place and, as little as I know, I know two of
them will not work well together. What changes would I need to make in the
input box code to call it from a command button, if that will work at all???
and then place the data in the active cell.
Thanks again

Mike Rogers
 
You'll have to combine them:

For instance:

Option Explicit
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim myStr As String

If Target.Cells.Count > 1 Then Exit Sub
If Not (Intersect(Target, Me.Range("a2:a99")) Is Nothing) Then
myStr = InputBox(Prompt:="what do you want to enter in " _
& Target.Address(0, 0) & "?")

If Trim(myStr) = "" Then
'do nothing
Else
Me.Unprotect Password:="hi"
Application.EnableEvents = False
Target.Value = myStr
Application.EnableEvents = True
Me.Protect Password:="hi"
End If
End If

'your other code

End Sub





Mike said:
Dave

Wow thanks for the complete response!!!! I really do appreiciate it. I
think the simplest solution is the imput box. But I have a problem with
either way..... I already have a Private Sub Worksheet_SelectionChange(ByVal
Target As Range) procedure in place and, as little as I know, I know two of
them will not work well together. What changes would I need to make in the
input box code to call it from a command button, if that will work at all???
and then place the data in the active cell.
Thanks again

Mike Rogers
Click to expand...
 
Dave,

Thanks for all the help!!!! Everything works perfect. I had a couple
different Worksheet_SelectionChange codes in some of the worksheets and
figured out how to combine them to work!!!!! Did not know that could be
done!!! Thanks for the help and the education.

Mike Rogers
 
It's nice when a plan comes together!

Glad you got it working.

Mike said:
Dave,

Thanks for all the help!!!! Everything works perfect. I had a couple
different Worksheet_SelectionChange codes in some of the worksheets and
figured out how to combine them to work!!!!! Did not know that could be
done!!! Thanks for the help and the education.

Mike Rogers
Click to expand...
 
Back
Top