Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
User form
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
User form
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 |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
User form
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 |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
User form
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 |
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
User form
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 Rogers wrote: 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 -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Example User Form Required | Excel Discussion (Misc queries) | |||
Print scrollable user form. | Excel Discussion (Misc queries) | |||
user form | Excel Discussion (Misc queries) | |||
Data Entry Alert in User Form | Excel Discussion (Misc queries) | |||
A "previous" button on a user form | Excel Discussion (Misc queries) |