Hi All,

I've searched the threads and read through a heap, but haven't been
able (so far!) to find anything to help me through what I'm trying to
do. Here is a basic overview:

I have a "cost calculator" for a client that allows them to entre the
dimensions of an advert, select from drop down lists what paper the ad
is to go into, section, colour/black & white, etc to get a cost for the
advert. All of the drop down lists use Data Validation, not combo
boxes.

What I'm looking to do is this: Once the user has entered in the
details and got the cost, I'd like them to be able to hit a "Add to
List" button (I sense VB code here!) to copy some of the details from
the form to another worksheet - that is, copy the name of the paper to
cell A1 on Sheet2, then the cost to cell B1 on Sheet2.

THEN, a reset button (if necessary), so that client can select a
different paper/size, and once they have the cost for that, hit the
"Add to List" button, which will then copy the same information to cell
A2 and B2 respectively, on Sheet2.

Does this make ANY sense to anyone?

I've looked into accumulators and from what I can tell by reading and
testing, they aren't going to be suitable.

Any suggestions?

Cheers in advance!!

(Desperate) Rob.


--
Rob Moyle
------------------------------------------------------------------------
Rob Moyle's Profile: http://www.excelforum.com/member.php...o&userid=30432
View this thread: http://www.excelforum.com/showthread...hreadid=545756

You can do this, but it's gonna become a lot more difficult when you realize you
want a way to delete bad entries and fix wrong entries.

But this may get you started.

Put a button from the Forms toolbar on the worksheet. Assign this code to the
button:

Option Explicit
Sub testme()

Dim ActWks As Worksheet
Dim RptWks As Worksheet
Dim DestCell As Range
Dim myCell As Range
Dim myRng As Range
Dim Resp As Long
Dim oCol As Long

Set ActWks = ActiveSheet
Set RptWks = Worksheets("List Sheet")

With ActWks
'some range of cells that should be copied
'to the rptwks
Set myRng = .Range("b9,c12,e14,d7,a7")

If Application.CountA(myRng) < myRng.Cells.Count Then
MsgBox "Please fill in all the cells in: " _
& vbLf & myRng.Address(0, 0)
Exit Sub
End If

Resp = MsgBox(Prompt:="Are you sure you want to copy the cells?", _
Buttons:=vbYesNo)

If Resp = vbNo Then
Exit Sub
End If

With RptWks
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

oCol = 0
For Each myCell In myRng.Cells
DestCell.Offset(0, oCol).Value = myCell.Value
oCol = oCol + 1
Next myCell

Resp = MsgBox(Prompt:="Do you want to clear the cells?", _
Buttons:=vbYesNo)

If Resp = vbYes Then
myRng.ClearContents
End If
End With

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Be aware that things that start small can get very complex pretty quickly.

Rob Moyle wrote:

Hi All,

I've searched the threads and read through a heap, but haven't been
able (so far!) to find anything to help me through what I'm trying to
do. Here is a basic overview:

I have a "cost calculator" for a client that allows them to entre the
dimensions of an advert, select from drop down lists what paper the ad
is to go into, section, colour/black & white, etc to get a cost for the
advert. All of the drop down lists use Data Validation, not combo
boxes.

What I'm looking to do is this: Once the user has entered in the
details and got the cost, I'd like them to be able to hit a "Add to
List" button (I sense VB code here!) to copy some of the details from
the form to another worksheet - that is, copy the name of the paper to
cell A1 on Sheet2, then the cost to cell B1 on Sheet2.

THEN, a reset button (if necessary), so that client can select a
different paper/size, and once they have the cost for that, hit the
"Add to List" button, which will then copy the same information to cell
A2 and B2 respectively, on Sheet2.

Does this make ANY sense to anyone?

I've looked into accumulators and from what I can tell by reading and
testing, they aren't going to be suitable.

Any suggestions?

Cheers in advance!!

(Desperate) Rob.

--
Rob Moyle
------------------------------------------------------------------------
Rob Moyle's Profile: http://www.excelforum.com/member.php...o&userid=30432
View this thread: http://www.excelforum.com/showthread...hreadid=545756

--

Dave Peterson

Rob,

Add code like this to the "Add to List" button's click event, one for every data item that you want
to sto

'Name of paper is in D5
Worksheets("Sheet2").Range("A65536").End(xlUp)(2). Value = Worksheets("Sheet1").Range("D5").Value
'Cost is in cell H5
Worksheets("Sheet2").Range("B65536").End(xlUp)(2). Value = Worksheets("Sheet1").Range("H5").Value
'etc, for other value
Worksheets("Sheet2").Range("C65536").End(xlUp)(2). Value = Worksheets("Sheet1").Range("G8").Value

Note the pattern is: column to store = single cell from your form, from which to read value

The reset button could use code like

Worksheets("Sheet1").Range("D5").ClearContents
Worksheets("Sheet1").Range("H5").ClearContents
Worksheets("Sheet1").Range("G8").ClearContents

HTH,
Bernie
MS Excel MVP


"Rob Moyle" wrote in message
...

Hi All,

I've searched the threads and read through a heap, but haven't been
able (so far!) to find anything to help me through what I'm trying to
do. Here is a basic overview:

I have a "cost calculator" for a client that allows them to entre the
dimensions of an advert, select from drop down lists what paper the ad
is to go into, section, colour/black & white, etc to get a cost for the
advert. All of the drop down lists use Data Validation, not combo
boxes.

What I'm looking to do is this: Once the user has entered in the
details and got the cost, I'd like them to be able to hit a "Add to
List" button (I sense VB code here!) to copy some of the details from
the form to another worksheet - that is, copy the name of the paper to
cell A1 on Sheet2, then the cost to cell B1 on Sheet2.

THEN, a reset button (if necessary), so that client can select a
different paper/size, and once they have the cost for that, hit the
"Add to List" button, which will then copy the same information to cell
A2 and B2 respectively, on Sheet2.

Does this make ANY sense to anyone?

I've looked into accumulators and from what I can tell by reading and
testing, they aren't going to be suitable.

Any suggestions?

Cheers in advance!!

(Desperate) Rob.


--
Rob Moyle
------------------------------------------------------------------------
Rob Moyle's Profile: http://www.excelforum.com/member.php...o&userid=30432
View this thread: http://www.excelforum.com/showthread...hreadid=545756

Bernie & Dave,

Sorry it's taken so long to post back - wanted to thank both of your
for your help and suggestions... I've now got my 'cost calculator'
working specatularly, with a couple of other functions added on as
well.

Cheers!!

Rob.

PS. The Boss was impressed to, so thanks for that also!! ;-)


--
Rob Moyle
------------------------------------------------------------------------
Rob Moyle's Profile: http://www.excelforum.com/member.php...o&userid=30432
View this thread: http://www.excelforum.com/showthread...hreadid=545756