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

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

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

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

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

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