Thread: User form
View Single Post
  #5   Report Post  
Posted to microsoft.public.excel.newusers
Mike Rogers
 
Posts: n/a
Default User form

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

"Dave Peterson" wrote:

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




Mike Rogers wrote:

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?

"Dave Peterson" wrote:

You mean inputbox?

If you have lots of data to retrieve all at once, a userform seems better to me.

If it's just one per row, maybe inputbox would suffice.

Debra Dalgleish has some getstarted notes at:
http://contextures.com/xlUserForm01.html

Mike Rogers wrote:

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 Peterson


--

Dave Peterson