| 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 |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
User form
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 "Dave Peterson" wrote: 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 |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
User form
It's nice when a plan comes together!
Glad you got it working. Mike Rogers wrote: 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 "Dave Peterson" wrote: 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 -- 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) | |||
Linear Mode
