| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Form, or seperate text boxes?
Hi Guys, I have a list of names in Column A, with various numbers which represent a date difference, in columns C:F... I need a button to activate some form of input field so that the user can select the name from a list, and then record some dates for that name, which I can then grab and play with, in the rest of my Macro... Can I do it with a form, or do I need to use seperate input boxes if I want to capture the fields? Input mediums are something totally new to me, so I'd really appreciate some code or explicit instruction for this one! I've had a look at creating a new form to call from the button_click sub() but I haven't found how to link a combo box to a name range, or record the date fields once entered either... Thank you, Gareth -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
Gareth,
You could use this approach Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row = 1 Then 'you can now get other fields MsgBox .Offset(0, 1) Value 'column B MsgBox .Offset(0, 2) Value 'column C MsgBox .Offset(0, 3) Value 'column D 'etc. End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. --- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Hi Guys, I have a list of names in Column A, with various numbers which represent a date difference, in columns C:F... I need a button to activate some form of input field so that the user can select the name from a list, and then record some dates for that name, which I can then grab and play with, in the rest of my Macro... Can I do it with a form, or do I need to use seperate input boxes if I want to capture the fields? Input mediums are something totally new to me, so I'd really appreciate some code or explicit instruction for this one! I've had a look at creating a new form to call from the button_click sub() but I haven't found how to link a combo box to a name range, or record the date fields once entered either... Thank you, Gareth -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
Hi Bob, Thanks very much for the quick reply... I'm getting aerrors when I paste this in, I'm sure I'm missing something! When I paste in as in your message, I get "Invalid or Unqualified reference" and when I remove the space between "msgbox" and ".offset", it says it expects an "=" instead of the "" What should this code do? Bring up a box with the values for those columns in editable text fields or something? If so, it's not these fields that I need to edit, they are formula based, working on fields in a different sheet, and it's those fields that I need to affect with the new 'variables'. There are a number of things I need to do with those fileds, so ideally, the form would just capture them, to use later. something like... Name <select from list Annual Date <Please enter completed review Date here New Annual Date <Please enter New Annual Date Here Thanks Bob, Gareth Bob Phillips Wrote: Gareth, You could use this approach Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row = 1 Then 'you can now get other fields MsgBox .Offset(0, 1) Value 'column B MsgBox .Offset(0, 2) Value 'column C MsgBox .Offset(0, 3) Value 'column D 'etc. End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. --- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Hi Guys, I have a list of names in Column A, with various numbers which represent a date difference, in columns C:F... I need a button to activate some form of input field so that the user can select the name from a list, and then record some dates for that name, which I can then grab and play with, in the rest of my Macro... Can I do it with a form, or do I need to use seperate input boxes if I want to capture the fields? Input mediums are something totally new to me, so I'd really appreciate some code or explicit instruction for this one! I've had a look at creating a new form to call from the button_click sub() but I haven't found how to link a combo box to a name range, or record the date fields once entered either... Thank you, Gareth -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
Sorry, rubbish code, should have been
Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Column = 1 Then 'you can now get other fields MsgBox Target.Offset(0, 1) 'column B MsgBox Target.Offset(0, 2) 'column C MsgBox Target.Offset(0, 3) 'column D 'etc. End If End Sub All it will do is show you what is in columns B, C, D in that row, It is only doing that to show you how you can access it with your code. -- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Hi Bob, Thanks very much for the quick reply... I'm getting aerrors when I paste this in, I'm sure I'm missing something! When I paste in as in your message, I get "Invalid or Unqualified reference" and when I remove the space between "msgbox" and ".offset", it says it expects an "=" instead of the "" What should this code do? Bring up a box with the values for those columns in editable text fields or something? If so, it's not these fields that I need to edit, they are formula based, working on fields in a different sheet, and it's those fields that I need to affect with the new 'variables'. There are a number of things I need to do with those fileds, so ideally, the form would just capture them, to use later. something like... Name <select from list Annual Date <Please enter completed review Date here New Annual Date <Please enter New Annual Date Here Thanks Bob, Gareth Bob Phillips Wrote: Gareth, You could use this approach Private Sub Worksheet_SelectionChange(ByVal Target As Range) If Target.Row = 1 Then 'you can now get other fields MsgBox .Offset(0, 1) Value 'column B MsgBox .Offset(0, 2) Value 'column C MsgBox .Offset(0, 3) Value 'column D 'etc. End If End Sub 'This is worksheet event code, which means that it needs to be 'placed in the appropriate worksheet code module, not a standard 'code module. To do this, right-click on the sheet tab, select 'the View Code option from the menu, and paste the code in. --- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Hi Guys, I have a list of names in Column A, with various numbers which represent a date difference, in columns C:F... I need a button to activate some form of input field so that the user can select the name from a list, and then record some dates for that name, which I can then grab and play with, in the rest of my Macro... Can I do it with a form, or do I need to use seperate input boxes if I want to capture the fields? Input mediums are something totally new to me, so I'd really appreciate some code or explicit instruction for this one! I've had a look at creating a new form to call from the button_click sub() but I haven't found how to link a combo box to a name range, or record the date fields once entered either... Thank you, Gareth -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
Hi Bob, Thanks again, that works fine when the data is there already... However I need it to do what ever it's going to do on a button click really. I run code to refresh the sheet with data from several sources, and at the moment, the Worksheet_selectionchange part is kicking in before the data is refreshed, which crashes the macro. Also, I'm not sure this is going to solve my problem is it? let me re-iterate in case I wasn't clear first time round! :) I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
You could attach a similar bit of code to a button, and use it like so when
you click the button Sub MyMacro() With Activecell If .Column = 1 Then 'you can now get other fields MsgBox .Offset(0, 1) 'column B MsgBox .Offset(0, 2) 'column C MsgBox .Offset(0, 3) 'column D 'etc. End If End With End Sub -- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Hi Bob, Thanks again, that works fine when the data is there already... However I need it to do what ever it's going to do on a button click really. I run code to refresh the sheet with data from several sources, and at the moment, the Worksheet_selectionchange part is kicking in before the data is refreshed, which crashes the macro. Also, I'm not sure this is going to solve my problem is it? let me re-iterate in case I wasn't clear first time round! :) I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
Ok, cool so I can run that with a button click, but it's still not doing what I need... how about the input fields bit referring to the other sheet? I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks Bob, appreciate your help. Gareth Bob Phillips Wrote: You could attach a similar bit of code to a button, and use it like so when you click the button Sub MyMacro() With Activecell If .Column = 1 Then 'you can now get other fields MsgBox .Offset(0, 1) 'column B MsgBox .Offset(0, 2) 'column C MsgBox .Offset(0, 3) 'column D 'etc. End If End With End Sub -- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Hi Bob, Thanks again, that works fine when the data is there already... However I need it to do what ever it's going to do on a button click really. I run code to refresh the sheet with data from several sources, and at the moment, the Worksheet_selectionchange part is kicking in before the data is refreshed, which crashes the macro. Also, I'm not sure this is going to solve my problem is it? let me re-iterate in case I wasn't clear first time round! :) I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
Gareth,
The code that I gave you assumed that you would select the name in question, and then click the button to run the macro. Therefore the activecell would contain your selected name, and code like ACtivecell.Offset(0,1).Value gets the value from column B (1 column right of activecell) in that same row. Likewise, you can set a value with Activecell.Offset(0,1).Value = date which puts today's date in there. You mention inserting dates, do you need to get those dates from the user, somewhere else, or is it today's date. Moving onto to the master sheet, you mention relevant columns. We can do that with something like With Activecell Worksheets("Master").Cells(.Row,"H").Value = .Offset(0,1).Value End With What this does is copy the value in column B of the selected name to column H of the Master sheet. Is this any better? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Ok, cool so I can run that with a button click, but it's still not doing what I need... how about the input fields bit referring to the other sheet? I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks Bob, appreciate your help. Gareth Bob Phillips Wrote: You could attach a similar bit of code to a button, and use it like so when you click the button Sub MyMacro() With Activecell If .Column = 1 Then 'you can now get other fields MsgBox .Offset(0, 1) 'column B MsgBox .Offset(0, 2) 'column C MsgBox .Offset(0, 3) 'column D 'etc. End If End With End Sub -- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Hi Bob, Thanks again, that works fine when the data is there already... However I need it to do what ever it's going to do on a button click really. I run code to refresh the sheet with data from several sources, and at the moment, the Worksheet_selectionchange part is kicking in before the data is refreshed, which crashes the macro. Also, I'm not sure this is going to solve my problem is it? let me re-iterate in case I wasn't clear first time round! :) I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
Ah, i see what you mean... Selecting the name that way... A list, or combobox or something would be better, as the end users will expect to have everything laid in front of them, all in the same place when they click the update button, but I see what you mean now... that would do the job for the name part. The dates will need to come from the user, they will need to input two dates, after they've selected the name, and then the dates that they enter, stored and assigned to variables, so that I can use them later. It's really only this Input part that I'm struggling with. Once I've got the Name.row, Date1 and Date2 variables defined from the User Input, I'm good to go! Thanks Bob, Bob Phillips Wrote: Gareth, The code that I gave you assumed that you would select the name in question, and then click the button to run the macro. Therefore the activecell would contain your selected name, and code like ACtivecell.Offset(0,1).Value gets the value from column B (1 column right of activecell) in that same row. Likewise, you can set a value with Activecell.Offset(0,1).Value = date which puts today's date in there. You mention inserting dates, do you need to get those dates from the user, somewhere else, or is it today's date. Moving onto to the master sheet, you mention relevant columns. We can do that with something like With Activecell Worksheets("Master").Cells(.Row,"H").Value = .Offset(0,1).Value End With What this does is copy the value in column B of the selected name to column H of the Master sheet. Is this any better? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Ok, cool so I can run that with a button click, but it's still not doing what I need... how about the input fields bit referring to the other sheet? I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks Bob, appreciate your help. Gareth Bob Phillips Wrote: You could attach a similar bit of code to a button, and use it like so when you click the button Sub MyMacro() With Activecell If .Column = 1 Then 'you can now get other fields MsgBox .Offset(0, 1) 'column B MsgBox .Offset(0, 2) 'column C MsgBox .Offset(0, 3) 'column D 'etc. End If End With End Sub -- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Hi Bob, Thanks again, that works fine when the data is there already... However I need it to do what ever it's going to do on a button click really. I run code to refresh the sheet with data from several sources, and at the moment, the Worksheet_selectionchange part is kicking in before the data is refreshed, which crashes the macro. Also, I'm not sure this is going to solve my problem is it? let me re-iterate in case I wasn't clear first time round! :) I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
Then use something like
Dim ans Dim dteFirst As Date, dteSecond As Date Dim IsValidDate As Boolean IsValidDate = False Do ans = InputBox("Please supply first date") If ans = "" Then IsValidDate = True Else On Error Resume Next dteFirst = CDate(ans) On Error GoTo 0 If dteFirst < 0 Then IsValidDate = True Else MsgBox "Invalid date", vbInformation + vbOK End If End If Loop Until IsValidDate IsValidDate = False Do ans = InputBox("Please supply second date") If ans = "" Then IsValidDate = True Else On Error Resume Next dteSecond = CDate(ans) On Error GoTo 0 If dteSecond < 0 Then IsValidDate = True Else MsgBox "Invalid date", vbInformation + vbOK End If End If Loop Until IsValidDate 'now use those values -- HTH Bob Phillips (replace xxxx in email address with googlemail if mailing direct) "pianoman" wrote in message ... Ah, i see what you mean... Selecting the name that way... A list, or combobox or something would be better, as the end users will expect to have everything laid in front of them, all in the same place when they click the update button, but I see what you mean now... that would do the job for the name part. The dates will need to come from the user, they will need to input two dates, after they've selected the name, and then the dates that they enter, stored and assigned to variables, so that I can use them later. It's really only this Input part that I'm struggling with. Once I've got the Name.row, Date1 and Date2 variables defined from the User Input, I'm good to go! Thanks Bob, Bob Phillips Wrote: Gareth, The code that I gave you assumed that you would select the name in question, and then click the button to run the macro. Therefore the activecell would contain your selected name, and code like ACtivecell.Offset(0,1).Value gets the value from column B (1 column right of activecell) in that same row. Likewise, you can set a value with Activecell.Offset(0,1).Value = date which puts today's date in there. You mention inserting dates, do you need to get those dates from the user, somewhere else, or is it today's date. Moving onto to the master sheet, you mention relevant columns. We can do that with something like With Activecell Worksheets("Master").Cells(.Row,"H").Value = .Offset(0,1).Value End With What this does is copy the value in column B of the selected name to column H of the Master sheet. Is this any better? -- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Ok, cool so I can run that with a button click, but it's still not doing what I need... how about the input fields bit referring to the other sheet? I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks Bob, appreciate your help. Gareth Bob Phillips Wrote: You could attach a similar bit of code to a button, and use it like so when you click the button Sub MyMacro() With Activecell If .Column = 1 Then 'you can now get other fields MsgBox .Offset(0, 1) 'column B MsgBox .Offset(0, 2) 'column C MsgBox .Offset(0, 3) 'column D 'etc. End If End With End Sub -- HTH Bob Phillips (remove xxx from email address if mailing direct) "pianoman" wrote in message ... Hi Bob, Thanks again, that works fine when the data is there already... However I need it to do what ever it's going to do on a button click really. I run code to refresh the sheet with data from several sources, and at the moment, the Worksheet_selectionchange part is kicking in before the data is refreshed, which crashes the macro. Also, I'm not sure this is going to solve my problem is it? let me re-iterate in case I wasn't clear first time round! :) I need to be able to choose a name from a list (sourced from column a), presumably record the row number for that name or something, then input dates into two different input fields, record those values and then run seperate code to jump to the master data sheet, enter those values into the relevant columns in the row number recorded from the form earlier. Does the code you've given me get me closer to that goal? Thanks -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Form, or seperate text boxes?
Bob, thanks a million. I need to impliment it and work it into my macros, but it looks spot on. Many thanks for your time. Cheers, Gareth -- pianoman ------------------------------------------------------------------------ pianoman's Profile: http://www.excelforum.com/member.php...o&userid=33712 View this thread: http://www.excelforum.com/showthread...hreadid=544994 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Link Text Boxes on Form? | Excel Discussion (Misc queries) | |||
| User Form Text Boxes - Copy format of text boxes | Excel Discussion (Misc queries) | |||
| Form Text Boxes moving | Excel Worksheet Functions | |||
| How to iterate through all Text Boxes on a Form? | Excel Programming | |||
| Numerical accuracy in Form Text-boxes | Excel Programming | |||
![pianoman[_17_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
