I need users to be able to put their cursor on a line in a spreadsheet, then
click a macro button, and have a form pop up, which will have certain cells
from that line that they're on populate into various fields.

Is there a way to get this done? I know there are forms, and I know there
are the types of fields that reference a particular cell. Is there anyone
that can give me the quick advice on which types of fields will accomplish
this? So the ideal is something that populates the fields, lets the user
change values, and then has some sort of OK or Cancel button to get out and
back to the regular sheet.

Thx.
--
Boris

You can do this with some very simple VB code, although for a fool proof
solution the coding can get a bit heavy for someone with little VB practice.

Here is some very simple code to do this.

From toolstoolbarscontroltoolbox.
Place a button on the work sheet.

Double click the button and type UserForm1.Show

Then on worksheets: Right click and select insert userform

Now you will see the userform.

Add 4 textboxes and a button
On the button type "OK"

double click the button and paste in the following code.

Dim Fname, Sname, Age, Addrs, answer

'****************************************
'****assign variables to form values*****
'****************************************
Fname = TextBox1.Value
Sname = TextBox2.Value
Age = TextBox3.Value
Addrs = TextBox4.Value
answer = vbYes

'************************************************* ********
'****Check active cell is empty as inform user if not*****
'************************************************* ********

If ActiveCell.Value < "" Then
answer = MsgBox("data already exists in this row! are you sure you want to
proceed?", vbYesNo)
End If


'If cell is empty, or user has clicked yes to overwrite
If answer = vbYes Then
ActiveCell.Value = Fname
ActiveCell.Offset(0, 1).Value = Sname
ActiveCell.Offset(0, 2).Value = Age
ActiveCell.Offset(0, 3).Value = Addrs
End If

UserForm1.Hide



That should give you a guide in the right direction, and open the very
powerful world of VB to you.


"BorisS" wrote:

I need users to be able to put their cursor on a line in a spreadsheet, then
click a macro button, and have a form pop up, which will have certain cells
from that line that they're on populate into various fields.

Is there a way to get this done? I know there are forms, and I know there
are the types of fields that reference a particular cell. Is there anyone
that can give me the quick advice on which types of fields will accomplish
this? So the ideal is something that populates the fields, lets the user
change values, and then has some sort of OK or Cancel button to get out and
back to the regular sheet.

Thx.
--
Boris

Scott, the sad thing is that I used to program fairly extensively in Excel
VB. Lost it all due to lack of use. This project crept up, and now have to
try again. Thanks for the start. I'll maybe ping back once I've tinkered,
if I cannot figure something out. Truly appreciative of the time for the
starter.

Thx.
--
Boris


"scottfoxall" wrote:

You can do this with some very simple VB code, although for a fool proof
solution the coding can get a bit heavy for someone with little VB practice.

Here is some very simple code to do this.

From toolstoolbarscontroltoolbox.
Place a button on the work sheet.

Double click the button and type UserForm1.Show

Then on worksheets: Right click and select insert userform

Now you will see the userform.

Add 4 textboxes and a button
On the button type "OK"

double click the button and paste in the following code.

Dim Fname, Sname, Age, Addrs, answer

'****************************************
'****assign variables to form values*****
'****************************************
Fname = TextBox1.Value
Sname = TextBox2.Value
Age = TextBox3.Value
Addrs = TextBox4.Value
answer = vbYes

'************************************************* ********
'****Check active cell is empty as inform user if not*****
'************************************************* ********

If ActiveCell.Value < "" Then
answer = MsgBox("data already exists in this row! are you sure you want to
proceed?", vbYesNo)
End If


'If cell is empty, or user has clicked yes to overwrite
If answer = vbYes Then
ActiveCell.Value = Fname
ActiveCell.Offset(0, 1).Value = Sname
ActiveCell.Offset(0, 2).Value = Age
ActiveCell.Offset(0, 3).Value = Addrs
End If

UserForm1.Hide



That should give you a guide in the right direction, and open the very
powerful world of VB to you.


"BorisS" wrote:

I need users to be able to put their cursor on a line in a spreadsheet, then
click a macro button, and have a form pop up, which will have certain cells
from that line that they're on populate into various fields.

Is there a way to get this done? I know there are forms, and I know there
are the types of fields that reference a particular cell. Is there anyone
that can give me the quick advice on which types of fields will accomplish
this? So the ideal is something that populates the fields, lets the user
change values, and then has some sort of OK or Cancel button to get out and
back to the regular sheet.

Thx.
--
Boris

Scott, thanks for the code. Whereas I'd initially looked at it and thought
that I understood some of the nuances of making it work, I'm finding in
application I am failing.

A few comments. When I go to insert in worksheets, I only have an "Excel
5.0 dialogue", as opposed to userform. I assumed it was the same thing, but
am not sure.

Also, once I have this userform setup, I am uncertain how to get the values
from the line that the user was on, into those boxes. If I understand what
the macro is doing in plain English, it is the following:

1) prepares the named variables to be linked to the boxes in the form
2) confirms the user understands they are going to overwrite values if they
proceed (assuming that the dialog gets launched, which it should be, only
from a line that already has data)
3) assigns the values in the dialogue boxes to the spaces in the line from
which the dialogue was launched (with the appropriate offsets)

The thing I am not clear on is how the dialogue initially shows the values
that were in the line to begin with. The intention of this dialogue is to
show some values that are in a line, and then let the user edit those values.

Finally, although I see what you indicate about the form.show command
button, the button does not actually do anything once I've entered this,
exited, saved, and then click the button. Not sure what I'm doing wrong, but
it's not calling up the dialogue. I went into the dialogue sheet, and saw
that when I right clicked the dialogue box, it said something like
"dialogueframe", so I substituted the word "userform1" with that name, but it
still didn't do anything.

Any further help would be greatly appreciated.

Thanks.
--
Boris


"scottfoxall" wrote:

You can do this with some very simple VB code, although for a fool proof
solution the coding can get a bit heavy for someone with little VB practice.

Here is some very simple code to do this.

From toolstoolbarscontroltoolbox.
Place a button on the work sheet.

Double click the button and type UserForm1.Show

Then on worksheets: Right click and select insert userform

Now you will see the userform.

Add 4 textboxes and a button
On the button type "OK"

double click the button and paste in the following code.

Dim Fname, Sname, Age, Addrs, answer

'****************************************
'****assign variables to form values*****
'****************************************
Fname = TextBox1.Value
Sname = TextBox2.Value
Age = TextBox3.Value
Addrs = TextBox4.Value
answer = vbYes

'************************************************* ********
'****Check active cell is empty as inform user if not*****
'************************************************* ********

If ActiveCell.Value < "" Then
answer = MsgBox("data already exists in this row! are you sure you want to
proceed?", vbYesNo)
End If


'If cell is empty, or user has clicked yes to overwrite
If answer = vbYes Then
ActiveCell.Value = Fname
ActiveCell.Offset(0, 1).Value = Sname
ActiveCell.Offset(0, 2).Value = Age
ActiveCell.Offset(0, 3).Value = Addrs
End If

UserForm1.Hide



That should give you a guide in the right direction, and open the very
powerful world of VB to you.


"BorisS" wrote:

I need users to be able to put their cursor on a line in a spreadsheet, then
click a macro button, and have a form pop up, which will have certain cells
from that line that they're on populate into various fields.

Is there a way to get this done? I know there are forms, and I know there
are the types of fields that reference a particular cell. Is there anyone
that can give me the quick advice on which types of fields will accomplish
this? So the ideal is something that populates the fields, lets the user
change values, and then has some sort of OK or Cancel button to get out and
back to the regular sheet.

Thx.
--
Boris