Tracking data

[Pat Rice]

I have a form that my sales guys use to figure out commission break down. I
want that form to go into a tracking sheet to figure out averages. The form
is never saved, just printed out, so the tracking will have to be once it is
printed. I know this is a lot but is it doable. If so can someone point me in
the right direction.

 

[JLatham]

Pat,
Basically you'll want some code associated with the [print it] button on the
form that not only prints the form, but then transfers the data from it into
a worksheet somewhere. I can give you some rough ideas here, you'll have to
adapt this "pseudo" code to your real world form's control names and the
worksheet name that you want to record the information on in the workbook.

Dim reportWS As Worksheet
Dim baseCell As Range

'get a reference to the worksheet in the workbook
Set reportWS = ThisWorkbook.Worksheets("some sheet name")
'get a reference to the next empty cell in column A on that sheet
Set baseCell = reportWS.Range("A" & Rows.Count).End(xlUp).Offset(1,0)
'now we start transfering data from the form to the worksheet
'I will just fake some possible information from the form and tell
'where it will go on the sheet
'
'transfer salesperson's id to column A
baseCell = Me.SalesID.Value
'transfer some dollar value to column B
baseCell.Offset(0,1) = Me.SalesAmount.Value
'transfer commission rate to column C
baseCell.Offset(0,2) = Me.CommissionRate.Value
'calculate the commission & put it into column D
baseCell.Offset(0,3) = baseCell.Offset(0,1) * baseCell.Offset(0,2)
'record the time of this entry
baseCell.Offset(0,4) = Now()

hopefully that will head you in a direction you can live with.

 

[Pat Rice]

J,

Thanks, now that you have given me the idea that it is possible. I can now
look up each code so I can figure out how to do it. I have no idea how to do
any of this but you gave me a starting base to do research. If after 8 hours
tomorrow I don't find a clue I will come back to this spot. maybe you can
crab walk me thru it.

Thanks yet again.
--
We all need a little help

Pat,
Basically you'll want some code associated with the [print it] button on the
form that not only prints the form, but then transfers the data from it into
a worksheet somewhere. I can give you some rough ideas here, you'll have to
adapt this "pseudo" code to your real world form's control names and the
worksheet name that you want to record the information on in the workbook.

Dim reportWS As Worksheet
Dim baseCell As Range

'get a reference to the worksheet in the workbook
Set reportWS = ThisWorkbook.Worksheets("some sheet name")
'get a reference to the next empty cell in column A on that sheet
Set baseCell = reportWS.Range("A" & Rows.Count).End(xlUp).Offset(1,0)
'now we start transfering data from the form to the worksheet
'I will just fake some possible information from the form and tell
'where it will go on the sheet
'
'transfer salesperson's id to column A
baseCell = Me.SalesID.Value
'transfer some dollar value to column B
baseCell.Offset(0,1) = Me.SalesAmount.Value
'transfer commission rate to column C
baseCell.Offset(0,2) = Me.CommissionRate.Value
'calculate the commission & put it into column D
baseCell.Offset(0,3) = baseCell.Offset(0,1) * baseCell.Offset(0,2)
'record the time of this entry
baseCell.Offset(0,4) = Now()

hopefully that will head you in a direction you can live with.

I have a form that my sales guys use to figure out commission break down. I
want that form to go into a tracking sheet to figure out averages. The form
is never saved, just printed out, so the tracking will have to be once it is
printed. I know this is a lot but is it doable. If so can someone point me in
the right direction.

 

[JLatham]

If you designed the form, you're already off to a good start. You can go
into the VB Editor and start editing on the form and as you select the
controls on it, the Properties window will tell you their names. If you
don't see a properties window, check out the View option of the VB editor
menu.

I've made an assumption here that by "Form" you mean an actual form that
pops up on the display "on top of" your worksheets. If that is incorrect,
and you mean that you have a worksheet laid out to be used as a form, then we
have to look at things differently.

If you like, you can get in touch direct if you just figure out the mystery
of this email address:
HelpFrom at jlathamsite dot com
If you send a copy of the file with any email you might send, that makes it
even better and easier to assist you.

J,

Thanks, now that you have given me the idea that it is possible. I can now
look up each code so I can figure out how to do it. I have no idea how to do
any of this but you gave me a starting base to do research. If after 8 hours
tomorrow I don't find a clue I will come back to this spot. maybe you can
crab walk me thru it.

Thanks yet again.
--
We all need a little help

Pat,
Basically you'll want some code associated with the [print it] button on the
form that not only prints the form, but then transfers the data from it into
a worksheet somewhere. I can give you some rough ideas here, you'll have to
adapt this "pseudo" code to your real world form's control names and the
worksheet name that you want to record the information on in the workbook.

Dim reportWS As Worksheet
Dim baseCell As Range

'get a reference to the worksheet in the workbook
Set reportWS = ThisWorkbook.Worksheets("some sheet name")
'get a reference to the next empty cell in column A on that sheet
Set baseCell = reportWS.Range("A" & Rows.Count).End(xlUp).Offset(1,0)
'now we start transfering data from the form to the worksheet
'I will just fake some possible information from the form and tell
'where it will go on the sheet
'
'transfer salesperson's id to column A
baseCell = Me.SalesID.Value
'transfer some dollar value to column B
baseCell.Offset(0,1) = Me.SalesAmount.Value
'transfer commission rate to column C
baseCell.Offset(0,2) = Me.CommissionRate.Value
'calculate the commission & put it into column D
baseCell.Offset(0,3) = baseCell.Offset(0,1) * baseCell.Offset(0,2)
'record the time of this entry
baseCell.Offset(0,4) = Now()

hopefully that will head you in a direction you can live with.

I have a form that my sales guys use to figure out commission break down. I
want that form to go into a tracking sheet to figure out averages. The form
is never saved, just printed out, so the tracking will have to be once it is
printed. I know this is a lot but is it doable. If so can someone point me in
the right direction.

 

[Pat Rice]

J,

I think I emailed you right. I don't have a form, I just have a spreadsheet
that I need to automatically move data to another spreadsheet. I am new to
advance excel stuff and would love to make it a form. Any directions would be
greatly appreciated.
--
We all need a little help

If you designed the form, you're already off to a good start. You can go
into the VB Editor and start editing on the form and as you select the
controls on it, the Properties window will tell you their names. If you
don't see a properties window, check out the View option of the VB editor
menu.

I've made an assumption here that by "Form" you mean an actual form that
pops up on the display "on top of" your worksheets. If that is incorrect,
and you mean that you have a worksheet laid out to be used as a form, then we
have to look at things differently.

If you like, you can get in touch direct if you just figure out the mystery
of this email address:
HelpFrom at jlathamsite dot com
If you send a copy of the file with any email you might send, that makes it
even better and easier to assist you.

J,

Thanks, now that you have given me the idea that it is possible. I can now
look up each code so I can figure out how to do it. I have no idea how to do
any of this but you gave me a starting base to do research. If after 8 hours
tomorrow I don't find a clue I will come back to this spot. maybe you can
crab walk me thru it.

Thanks yet again.
--
We all need a little help

Pat,
Basically you'll want some code associated with the [print it] button on the
form that not only prints the form, but then transfers the data from it into
a worksheet somewhere. I can give you some rough ideas here, you'll have to
adapt this "pseudo" code to your real world form's control names and the
worksheet name that you want to record the information on in the workbook.

Dim reportWS As Worksheet
Dim baseCell As Range

'get a reference to the worksheet in the workbook
Set reportWS = ThisWorkbook.Worksheets("some sheet name")
'get a reference to the next empty cell in column A on that sheet
Set baseCell = reportWS.Range("A" & Rows.Count).End(xlUp).Offset(1,0)
'now we start transfering data from the form to the worksheet
'I will just fake some possible information from the form and tell
'where it will go on the sheet
'
'transfer salesperson's id to column A
baseCell = Me.SalesID.Value
'transfer some dollar value to column B
baseCell.Offset(0,1) = Me.SalesAmount.Value
'transfer commission rate to column C
baseCell.Offset(0,2) = Me.CommissionRate.Value
'calculate the commission & put it into column D
baseCell.Offset(0,3) = baseCell.Offset(0,1) * baseCell.Offset(0,2)
'record the time of this entry
baseCell.Offset(0,4) = Now()

hopefully that will head you in a direction you can live with.


:

I have a form that my sales guys use to figure out commission break down. I
want that form to go into a tracking sheet to figure out averages. The form
is never saved, just printed out, so the tracking will have to be once it is
printed. I know this is a lot but is it doable. If so can someone point me in
the right direction.

 

[JLatham]

I believe I've received your email, I won't echo the name/addy here in case
it was someone else with a similar request but the 1st name seems to match up.

J,

I think I emailed you right. I don't have a form, I just have a spreadsheet
that I need to automatically move data to another spreadsheet. I am new to
advance excel stuff and would love to make it a form. Any directions would be
greatly appreciated.
--
We all need a little help

If you designed the form, you're already off to a good start. You can go
into the VB Editor and start editing on the form and as you select the
controls on it, the Properties window will tell you their names. If you
don't see a properties window, check out the View option of the VB editor
menu.

I've made an assumption here that by "Form" you mean an actual form that
pops up on the display "on top of" your worksheets. If that is incorrect,
and you mean that you have a worksheet laid out to be used as a form, then we
have to look at things differently.

If you like, you can get in touch direct if you just figure out the mystery
of this email address:
HelpFrom at jlathamsite dot com
If you send a copy of the file with any email you might send, that makes it
even better and easier to assist you.

J,

Thanks, now that you have given me the idea that it is possible. I can now
look up each code so I can figure out how to do it. I have no idea how to do
any of this but you gave me a starting base to do research. If after 8 hours
tomorrow I don't find a clue I will come back to this spot. maybe you can
crab walk me thru it.

Thanks yet again.
--
We all need a little help


:

Pat,
Basically you'll want some code associated with the [print it] button on the
form that not only prints the form, but then transfers the data from it into
a worksheet somewhere. I can give you some rough ideas here, you'll have to
adapt this "pseudo" code to your real world form's control names and the
worksheet name that you want to record the information on in the workbook.

Dim reportWS As Worksheet
Dim baseCell As Range

'get a reference to the worksheet in the workbook
Set reportWS = ThisWorkbook.Worksheets("some sheet name")
'get a reference to the next empty cell in column A on that sheet
Set baseCell = reportWS.Range("A" & Rows.Count).End(xlUp).Offset(1,0)
'now we start transfering data from the form to the worksheet
'I will just fake some possible information from the form and tell
'where it will go on the sheet
'
'transfer salesperson's id to column A
baseCell = Me.SalesID.Value
'transfer some dollar value to column B
baseCell.Offset(0,1) = Me.SalesAmount.Value
'transfer commission rate to column C
baseCell.Offset(0,2) = Me.CommissionRate.Value
'calculate the commission & put it into column D
baseCell.Offset(0,3) = baseCell.Offset(0,1) * baseCell.Offset(0,2)
'record the time of this entry
baseCell.Offset(0,4) = Now()

hopefully that will head you in a direction you can live with.


:

I have a form that my sales guys use to figure out commission break down. I
want that form to go into a tracking sheet to figure out averages. The form
is never saved, just printed out, so the tracking will have to be once it is
printed. I know this is a lot but is it doable. If so can someone point me in
the right direction.