Making Excel generate Access-Like Reports

[VJ7777]

Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership system using
Excel. I probably should have used Access but I don't
want to redo the system yet. Everything works swimmingly
except:

1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"), etc.

I have created reports which have a row for each customer
and ten or more columns of data for each customer. I get
the info for each column from the customer workbook and
worksheet using formulas like the one illustrated below
(ignore the complexity of the formula; I merely want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):

Prospective Business Report (Sample Column Headings):
Name Product PurchaseDate Cost Sell etc., etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and replace
(i.e. replace "Jones" with "Smith") to add a new customer
row to the report. I cannot find a way to use a master
list of customer names to automatically change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list of
customer names) except that there would be multiple rows
for each customer. The source, Buyer Progress Record
worksheet, contains up to 40 lines of contacts in Rows 28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row 29;
the remaining rows will be used as time marches on). The
desired report would look like this and only print rows
for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17, 2004

Customer Name "Jones"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Smith"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Green"
Date of Contact Date of Next Contact Description

3. It would be nice to be able to add to the master list
of customer names automatically by having the computer
look to see if any new customer workbooks have been added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook is
handled but this date is never changed no matter how many
times the worksheet is modified?

Thank you in advance for any help you can render.

 

[JulieD]

Hi

can't help (yet) with the other questions but

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook is
handled but this date is never changed no matter how many
times the worksheet is modified?

you can put code against each sheet (itself) in the vbe window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD

Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership system using
Excel. I probably should have used Access but I don't
want to redo the system yet. Everything works swimmingly
except:

1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"), etc.

I have created reports which have a row for each customer
and ten or more columns of data for each customer. I get
the info for each column from the customer workbook and
worksheet using formulas like the one illustrated below
(ignore the complexity of the formula; I merely want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):

Prospective Business Report (Sample Column Headings):
Name Product PurchaseDate Cost Sell etc., etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and replace
(i.e. replace "Jones" with "Smith") to add a new customer
row to the report. I cannot find a way to use a master
list of customer names to automatically change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list of
customer names) except that there would be multiple rows
for each customer. The source, Buyer Progress Record
worksheet, contains up to 40 lines of contacts in Rows 28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row 29;
the remaining rows will be used as time marches on). The
desired report would look like this and only print rows
for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17, 2004

Customer Name "Jones"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Smith"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Green"
Date of Contact Date of Next Contact Description

3. It would be nice to be able to add to the master list
of customer names automatically by having the computer
look to see if any new customer workbooks have been added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook is
handled but this date is never changed no matter how many
times the worksheet is modified?

Thank you in advance for any help you can render.

 

[Guest]

-----Original Message-----
Hi

can't help (yet) with the other questions but

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook is
handled but this date is never changed no matter how many
times the worksheet is modified?

you can put code against each sheet (itself) in the vbe window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD

Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership system using
Excel. I probably should have used Access but I don't
want to redo the system yet. Everything works swimmingly
except:

1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"), etc.

I have created reports which have a row for each customer
and ten or more columns of data for each customer. I get
the info for each column from the customer workbook and
worksheet using formulas like the one illustrated below
(ignore the complexity of the formula; I merely want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):

Prospective Business Report (Sample Column Headings):
Name Product PurchaseDate Cost Sell etc., etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and replace
(i.e. replace "Jones" with "Smith") to add a new customer
row to the report. I cannot find a way to use a master
list of customer names to automatically change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list of
customer names) except that there would be multiple rows
for each customer. The source, Buyer Progress Record
worksheet, contains up to 40 lines of contacts in Rows 28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row 29;
the remaining rows will be used as time marches on). The
desired report would look like this and only print rows
for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17, 2004

Customer Name "Jones"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Smith"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Green"
Date of Contact Date of Next Contact Description

3. It would be nice to be able to add to the master list
of customer names automatically by having the computer
look to see if any new customer workbooks have been added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook is
handled but this date is never changed no matter how many
times the worksheet is modified?

Thank you in advance for any help you can render.

Hi, Julie:

Thank you , very much for your response. I must confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about the time of Eniac (sp?). I taught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems to
explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can get
the concept of how to begin to use VB - certainly I have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will be
a major part of your answer. If so, I will need to know
how to intergrate them into Excel; your response
regarding to how to implement this will be very necessary
to understanding your other solutions.
Thanks again,
Vince

 

[JulieD]

Hi Vince

to place the code in the workbook, right mouse click on any sheet tab and
choose view code. This will display the VB Editor ... on the top left there
should be a little area with folders & sheets & the name of your workbook in
bold - if not, choose View / Project Explorer from the menu.

under the project with your workbook name in bold, you will see "NewInput"
and the rest of your sheets - double click on the NewInput one, and a white
piece of paper should appear on the right hand side of the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two lines.
Change the A1 to B2
You can "switch" between the code window & your workbook by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has nothing in it, switch
to another sheet, come back to the NewInput sheet the date should be filled
in. Now you can test out both bits of code i gave you to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup testing any code etc)

With regards to your other questions, i'm still having a look at them, but
i've got a few other things on at the moment and havent' really had time to
sit down & think about them properly. If you haven't done so already you
might like to do a search of google (groups.google.com - advanced search:
search string changing workbook names formulas ... search groups
microsoft.public.excel* ), i would do it for you and see if i come up with
anything but my internet explorer's not working at the moment

Hope this helps
Cheers
JulieD

-----Original Message-----
Hi

can't help (yet) with the other questions but

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook is
handled but this date is never changed no matter how many
times the worksheet is modified?

you can put code against each sheet (itself) in the vbe window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD

Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership system using
Excel. I probably should have used Access but I don't
want to redo the system yet. Everything works swimmingly
except:

1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"), etc.

I have created reports which have a row for each customer
and ten or more columns of data for each customer. I get
the info for each column from the customer workbook and
worksheet using formulas like the one illustrated below
(ignore the complexity of the formula; I merely want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):

Prospective Business Report (Sample Column Headings):
Name Product PurchaseDate Cost Sell etc., etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and replace
(i.e. replace "Jones" with "Smith") to add a new customer
row to the report. I cannot find a way to use a master
list of customer names to automatically change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list of
customer names) except that there would be multiple rows
for each customer. The source, Buyer Progress Record
worksheet, contains up to 40 lines of contacts in Rows 28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row 29;
the remaining rows will be used as time marches on). The
desired report would look like this and only print rows
for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17, 2004

Customer Name "Jones"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Smith"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Green"
Date of Contact Date of Next Contact Description

3. It would be nice to be able to add to the master list
of customer names automatically by having the computer
look to see if any new customer workbooks have been added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook is
handled but this date is never changed no matter how many
times the worksheet is modified?

Thank you in advance for any help you can render.

Hi, Julie:

Thank you , very much for your response. I must confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about th etimeofEniac.Itaught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems to
explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can get
the concept of how to begin to use VB - certainly I have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will be
a major part of your answer. If so, I will need to know
how to intergrate them into Excel; your response
regarding to how to implement this will be very necessary
to understanding your other solutions.
Thanks again,
Vince

 

[VJ7777]

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click on any sheet tab and
choose view code. This will display the VB Editor ... on the top left there
should be a little area with folders & sheets & the name of your workbook in
bold - if not, choose View / Project Explorer from the menu.

under the project with your workbook name in bold, you will see "NewInput"
and the rest of your sheets - double click on the NewInput one, and a white
piece of paper should appear on the right hand side of the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two lines.
Change the A1 to B2
You can "switch" between the code window & your workbook by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has nothing in it, switch
to another sheet, come back to the NewInput sheet the date should be filled
in. Now you can test out both bits of code i gave you to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup testing any code etc)

With regards to your other questions, i'm still having a look at them, but
i've got a few other things on at the moment and havent' really had time to
sit down & think about them properly. If you haven't done so already you
might like to do a search of google (groups.google.com - advanced search:
search string changing workbook names formulas ... search groups
microsoft.public.excel* ), i would do it for you and see if i come up with
anything but my internet explorer's not working at the moment

Hope this helps
Cheers
JulieD

-----Original Message-----
Hi

can't help (yet) with the other questions but
4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a

workbook
is

handled but this date is never changed no matter how many
times the worksheet is modified?

you can put code against each sheet (itself) in the

vbe
window similar to

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address

as
required

Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777" <[email protected]> wrote

in
message

Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership system using
Excel. I probably should have used Access but I don't
want to redo the system yet. Everything works swimmingly
except:

1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"), etc.

I have created reports which have a row for each customer
and ten or more columns of data for each customer.

I
get

the info for each column from the customer workbook and
worksheet using formulas like the one illustrated below
(ignore the complexity of the formula; I merely want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):

Prospective Business Report (Sample Column Headings):
Name Product PurchaseDate Cost Sell etc., etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and replace
(i.e. replace "Jones" with "Smith") to add a new customer
row to the report. I cannot find a way to use a master
list of customer names to automatically change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list of
customer names) except that there would be multiple rows
for each customer. The source, Buyer Progress Record
worksheet, contains up to 40 lines of contacts in

Rows
28

through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row 29;
the remaining rows will be used as time marches on). The
desired report would look like this and only print rows
for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17, 2004

Customer Name "Jones"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Smith"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Green"
Date of Contact Date of Next Contact Description

3. It would be nice to be able to add to the master list
of customer names automatically by having the computer
look to see if any new customer workbooks have been added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a

workbook
is

handled but this date is never changed no matter how many
times the worksheet is modified?

Thank you in advance for any help you can render.



Hi, Julie:

Thank you , very much for your response. I must confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about th etimeofEniac.Itaught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems to
explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can get
the concept of how to begin to use VB - certainly I have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will be
a major part of your answer. If so, I will need to know
how to intergrate them into Excel; your response
regarding to how to implement this will be very necessary
to understanding your other solutions.
Thanks again,
Vince

.Julie, thanks a million. I'll try the google search

later today. One quick question: The date appears OK
after I switch to another worksheet and back again, but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re-opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince

 

[JulieD]

Hi Vince

you can also put code against the THISWORKBOOK object on the workbook_open
event (double click on ThisWorkbook in the VBE window, choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click on any sheet tab and
choose view code. This will display the VB Editor ... on the top left there
should be a little area with folders & sheets & the name of your workbook in
bold - if not, choose View / Project Explorer from the menu.

under the project with your workbook name in bold, you will see "NewInput"
and the rest of your sheets - double click on the NewInput one, and a white
piece of paper should appear on the right hand side of the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two lines.
Change the A1 to B2
You can "switch" between the code window & your workbook by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has nothing in it, switch
to another sheet, come back to the NewInput sheet the date should be filled
in. Now you can test out both bits of code i gave you to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup testing any code etc)

With regards to your other questions, i'm still having a look at them, but
i've got a few other things on at the moment and havent' really had time to
sit down & think about them properly. If you haven't done so already you
might like to do a search of google (groups.google.com - advanced search:
search string changing workbook names formulas ... search groups
microsoft.public.excel* ), i would do it for you and see if i come up with
anything but my internet explorer's not working at the moment

Hope this helps
Cheers
JulieD

-----Original Message-----
Hi

can't help (yet) with the other questions but
4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook
is
handled but this date is never changed no matter how
many
times the worksheet is modified?

you can put code against each sheet (itself) in the vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



message
Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership system
using
Excel. I probably should have used Access but I don't
want to redo the system yet. Everything works
swimmingly
except:

1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data
pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"), etc.

I have created reports which have a row for each
customer
and ten or more columns of data for each customer. I
get
the info for each column from the customer workbook and
worksheet using formulas like the one illustrated below
(ignore the complexity of the formula; I merely want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):

Prospective Business Report (Sample Column Headings):
Name Product PurchaseDate Cost Sell etc., etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress
Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and
replace
(i.e. replace "Jones" with "Smith") to add a new
customer
row to the report. I cannot find a way to use a master
list of customer names to automatically change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list of
customer names) except that there would be multiple
rows
for each customer. The source, Buyer Progress Record
worksheet, contains up to 40 lines of contacts in Rows
28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row 29;
the remaining rows will be used as time marches on).
The
desired report would look like this and only print rows
for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17, 2004

Customer Name "Jones"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Smith"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Green"
Date of Contact Date of Next Contact Description

3. It would be nice to be able to add to the master
list
of customer names automatically by having the computer
look to see if any new customer workbooks have been
added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook
is
handled but this date is never changed no matter how
many
times the worksheet is modified?

Thank you in advance for any help you can render.



Hi, Julie:
Thank you , very much for your response. I must confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about th etimeofEniac.Itaught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems to
explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can get
the concept of how to begin to use VB - certainly I have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will be
a major part of your answer. If so, I will need to know
how to intergrate them into Excel; your response
regarding to how to implement this will be very necessary
to understanding your other solutions.
Thanks again,
Vince

.Julie, thanks a million. I'll try the google search

later today. One quick question: The date appears OK
after I switch to another worksheet and back again, but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re-opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince

 

[VJ7777]

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK object on the workbook_open
event (double click on ThisWorkbook in the VBE window, choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format (Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click

on
any sheet tab and

choose view code. This will display the VB Editor ... on the top left there
should be a little area with folders & sheets & the

name
of your workbook in

bold - if not, choose View / Project Explorer from the menu.

under the project with your workbook name in bold, you will see "NewInput"
and the rest of your sheets - double click on the NewInput one, and a white
piece of paper should appear on the right hand side of the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two lines.
Change the A1 to B2
You can "switch" between the code window & your

workbook
by using Alt & F1l

.... switch back, ensure B2 on the NewInput sheet has nothing in it, switch
to another sheet, come back to the NewInput sheet the date should be filled
in. Now you can test out both bits of code i gave you to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup testing any code etc)

With regards to your other questions, i'm still

having a
look at them, but

i've got a few other things on at the moment and

havent'
really had time to

sit down & think about them properly. If you haven't done so already you
might like to do a search of google

(groups.google.com -
advanced search:

search string changing workbook names formulas ... search groups
microsoft.public.excel* ), i would do it for you and

see
if i come up with

anything but my internet explorer's not working at the moment

Hope this helps
Cheers
JulieD



-----Original Message-----
Hi

can't help (yet) with the other questions but
4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook
is
handled but this date is never changed no matter how
many
times the worksheet is modified?

you can put code against each sheet (itself) in the vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell

address
as

required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777" <[email protected]>

wrote
in

message
Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership system
using
Excel. I probably should have used Access but I don't
want to redo the system yet. Everything works
swimmingly
except:

1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data
pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"), etc.

I have created reports which have a row for each
customer
and ten or more columns of data for each

customer.
I

get
the info for each column from the customer

workbook
and

worksheet using formulas like the one illustrated below
(ignore the complexity of the formula; I merely want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):

Prospective Business Report (Sample Column Headings):
Name Product PurchaseDate Cost Sell

etc.,
etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress
Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and
replace
(i.e. replace "Jones" with "Smith") to add a new
customer
row to the report. I cannot find a way to use a master
list of customer names to automatically change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master

list
of

customer names) except that there would be multiple
rows
for each customer. The source, Buyer Progress Record
worksheet, contains up to 40 lines of contacts in Rows
28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and

Row
29;

the remaining rows will be used as time marches on).
The
desired report would look like this and only

print
rows

for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17, 2004

Customer Name "Jones"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Smith"
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description
Date of Contact Date of Next Contact Description

Customer Name "Green"
Date of Contact Date of Next Contact Description

3. It would be nice to be able to add to the master
list
of customer names automatically by having the computer
look to see if any new customer workbooks have been
added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a workbook
is
handled but this date is never changed no matter how
many
times the worksheet is modified?

Thank you in advance for any help you can render.



Hi, Julie:
Thank you , very much for your response. I must confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about th etimeofEniac.Itaught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never

seems
to

explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can get
the concept of how to begin to use VB - certainly I have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB

will
be

a major part of your answer. If so, I will need to know
how to intergrate them into Excel; your response
regarding to how to implement this will be very necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google search

later today. One quick question: The date appears OK
after I switch to another worksheet and back again, but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re-opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince

.Hi Julie:

I'm afraid I am asking too much of you - it isn't your
task in life to train me in VB. I scan the book and it
seems to avoid simple tasks. I've tried Google for a
list of commands (for example: goto - or moveto, etc. -
so I can say 'if whatever, goto "a1")' and cannot find
such a list. If you point me in the right direction I'll
go there and study. Having started life as a bookkeeping
machine salesperson I am strong on applications and look
to programming as a list of simple commands to copy into
a program to get my job done.

Having said that, the code you gave me generated "Run-
time error '9': Subscript out of range." Here is the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above questions
I'll go it alone from that point. But will hope you can
solve the first three questions in my original posting
when you get the time. (I'll do the Google search you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince

 

[JulieD]

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK object on the workbook_open
event (double click on ThisWorkbook in the VBE window, choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format (Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click on
any sheet tab and
choose view code. This will display the VB Editor ...
on the top left there
should be a little area with folders & sheets & the name
of your workbook in
bold - if not, choose View / Project Explorer from the
menu.

under the project with your workbook name in bold, you
will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two lines.
Change the A1 to B2
You can "switch" between the code window & your workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has
nothing in it, switch
to another sheet, come back to the NewInput sheet the
date should be filled
in. Now you can test out both bits of code i gave you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup
testing any code etc)

With regards to your other questions, i'm still having a
look at them, but
i've got a few other things on at the moment and havent'
really had time to
sit down & think about them properly. If you haven't
done so already you
might like to do a search of google (groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you and see
if i come up with
anything but my internet explorer's not working at the
moment

Hope this helps
Cheers
JulieD



-----Original Message-----
Hi

can't help (yet) with the other questions but
4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a
workbook
is
handled but this date is never changed no matter how
many
times the worksheet is modified?

you can put code against each sheet (itself) in the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



in
message
Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership system
using
Excel. I probably should have used Access but I
don't
want to redo the system yet. Everything works
swimmingly
except:

1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data
pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"),
etc.

I have created reports which have a row for each
customer
and ten or more columns of data for each customer.
I
get
the info for each column from the customer workbook
and
worksheet using formulas like the one illustrated
below
(ignore the complexity of the formula; I merely
want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):

Prospective Business Report (Sample Column
Headings):
Name Product PurchaseDate Cost Sell etc.,
etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress
Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer
Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and
replace
(i.e. replace "Jones" with "Smith") to add a new
customer
row to the report. I cannot find a way to use a
master
list of customer names to automatically
change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list
of
customer names) except that there would be multiple
rows
for each customer. The source, Buyer Progress
Record
worksheet, contains up to 40 lines of contacts in
Rows
28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row
29;
the remaining rows will be used as time marches on).
The
desired report would look like this and only print
rows
for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17,
2004

Customer Name "Jones"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Smith"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Green"
Date of Contact Date of Next Contact
Description

3. It would be nice to be able to add to the master
list
of customer names automatically by having the
computer
look to see if any new customer workbooks have been
added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a
workbook
is
handled but this date is never changed no matter how
many
times the worksheet is modified?

Thank you in advance for any help you can render.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation
model
for Fortune 500 companies using Computer Sciences' time
sharing system. I taught myself how to program in
Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems
to
explain a place to begin.

If it isn't asking too much, could you please tell me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can
get
the concept of how to begin to use VB - certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will
be
a major part of your answer. If so, I will need to
know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google search
later today. One quick question: The date appears OK
after I switch to another worksheet and back again, but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re-opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince

.Hi Julie:

I'm afraid I am asking too much of you - it isn't your
task in life to train me in VB. I scan the book and it
seems to avoid simple tasks. I've tried Google for a
list of commands (for example: goto - or moveto, etc. -
so I can say 'if whatever, goto "a1")' and cannot find
such a list. If you point me in the right direction I'll
go there and study. Having started life as a bookkeeping
machine salesperson I am strong on applications and look
to programming as a list of simple commands to copy into
a program to get my job done.

Having said that, the code you gave me generated "Run-
time error '9': Subscript out of range." Here is the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above questions
I'll go it alone from that point. But will hope you can
solve the first three questions in my original posting
when you get the time. (I'll do the Google search you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince

Hi Vince

i'm happy to help so don't worry about asking questions ..

have you got a sheet in the workbook called "NewInput" as the code runs fine
when i copy & paste it into a new workbook with a sheet called NewInput (no
spaces) ...
is the code in the THISWORKBOOK object of the project explorer and not in a
module sheet
is the format stuff all on one line?

as to the other 3 questions i will get to them when i get a chance.

Looking forward to hearing from you.

Cheers
JulieD

 

[Guest]

-----Original Message-----

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK object

on
the workbook_open

event (double click on ThisWorkbook in the VBE window, choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format (Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD


"VJ7777" <[email protected]> wrote

in
message

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse

click
on

any sheet tab and
choose view code. This will display the VB Editor ...
on the top left there
should be a little area with folders & sheets & the name
of your workbook in
bold - if not, choose View / Project Explorer from the
menu.

under the project with your workbook name in bold, you
will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two lines.
Change the A1 to B2
You can "switch" between the code window & your workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has
nothing in it, switch
to another sheet, come back to the NewInput sheet the
date should be filled
in. Now you can test out both bits of code i gave you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup
testing any code etc)

With regards to your other questions, i'm still having a
look at them, but
i've got a few other things on at the moment and havent'
really had time to
sit down & think about them properly. If you haven't
done so already you
might like to do a search of google (groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you

and
see

if i come up with
anything but my internet explorer's not working at the
moment

Hope this helps
Cheers
JulieD



-----Original Message-----
Hi

can't help (yet) with the other questions but
4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a
workbook
is
handled but this date is never changed no

matter
how

many
times the worksheet is modified?

you can put code against each sheet (itself) in the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell

address
as

required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



in
message
Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership system
using
Excel. I probably should have used Access but I
don't
want to redo the system yet. Everything works
swimmingly
except:

1. I use customer name to name each workbook (which
contains multiple worksheets of forms and data
pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"),
etc.

I have created reports which have a row for each
customer
and ten or more columns of data for each customer.
I
get
the info for each column from the customer workbook
and
worksheet using formulas like the one illustrated
below
(ignore the complexity of the formula; I merely
want to
automatically substitute "Smith" for "Jones" from a
separate master list of customer names):

Prospective Business Report (Sample Column
Headings):
Name Product PurchaseDate Cost Sell etc.,
etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress
Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer
Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls] Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and
replace
(i.e. replace "Jones" with "Smith") to add a new
customer
row to the report. I cannot find a way to use a
master
list of customer names to automatically
change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master list
of
customer names) except that there would be multiple
rows
for each customer. The source, Buyer Progress
Record
worksheet, contains up to 40 lines of contacts in
Rows
28
through 68 and, perhaps, only one or two rows might
contain data at any moment in time (Row 28 and Row
29;
the remaining rows will be used as time

marches
on).

The
desired report would look like this and only print
rows
for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17,
2004

Customer Name "Jones"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Smith"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Green"
Date of Contact Date of Next Contact
Description

3. It would be nice to be able to add to the master
list
of customer names automatically by having the
computer
look to see if any new customer workbooks have been
added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a
workbook
is
handled but this date is never changed no

matter
how

many
times the worksheet is modified?

Thank you in advance for any help you can render.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start implementing
your code. I have been in the computer industry since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I was
product manager of a capital investment evaluation
model
for Fortune 500 companies using Computer

Sciences'
time

sharing system. I taught myself how to program in
Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book multiple
times and, as I did tonight, asked myself why I should
punish myself when Microsoft's explanation never seems
to
explain a place to begin.

If it isn't asking too much, could you please

tell
me

what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can
get
the concept of how to begin to use VB - certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB will
be
a major part of your answer. If so, I will need to
know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google search
later today. One quick question: The date appears OK
after I switch to another worksheet and back again, but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re- opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince




.Hi Julie:

I'm afraid I am asking too much of you - it isn't your
task in life to train me in VB. I scan the book and it
seems to avoid simple tasks. I've tried Google for a
list of commands (for example: goto - or moveto, etc. -
so I can say 'if whatever, goto "a1")' and cannot find
such a list. If you point me in the right direction I'll
go there and study. Having started life as a bookkeeping
machine salesperson I am strong on applications and look
to programming as a list of simple commands to copy into
a program to get my job done.

Having said that, the code you gave me generated "Run-
time error '9': Subscript out of range." Here is the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above questions
I'll go it alone from that point. But will hope you can
solve the first three questions in my original posting
when you get the time. (I'll do the Google search you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince

Hi Vince

i'm happy to help so don't worry about asking questions ..

have you got a sheet in the workbook called "NewInput" as the code runs fine
when i copy & paste it into a new workbook with a sheet called NewInput (no
spaces) ...
is the code in the THISWORKBOOK object of the project explorer and not in a
module sheet
is the format stuff all on one line?

as to the other 3 questions i will get to them when i get a chance.

Looking forward to hearing from you.

Cheers
JulieD


.Hi Julie

Please forgive my stupidity. While shaving I realized my
worksheet is New_Input; not, NewInput. I made the change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince

 

[JulieD]

-----Original Message-----

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK object on
the workbook_open
event (double click on ThisWorkbook in the VBE window,
choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format
(Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD


message

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click
on
any sheet tab and
choose view code. This will display the VB Editor ...
on the top left there
should be a little area with folders & sheets & the
name
of your workbook in
bold - if not, choose View / Project Explorer from the
menu.

under the project with your workbook name in bold, you
will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two
lines.
Change the A1 to B2
You can "switch" between the code window & your
workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput sheet has
nothing in it, switch
to another sheet, come back to the NewInput sheet the
date should be filled
in. Now you can test out both bits of code i gave you
to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup
testing any code etc)

With regards to your other questions, i'm still
having a
look at them, but
i've got a few other things on at the moment and
havent'
really had time to
sit down & think about them properly. If you haven't
done so already you
might like to do a search of google
(groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you and
see
if i come up with
anything but my internet explorer's not working at the
moment

Hope this helps
Cheers
JulieD



-----Original Message-----
Hi

can't help (yet) with the other questions but
4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a
workbook
is
handled but this date is never changed no matter
how
many
times the worksheet is modified?

you can put code against each sheet (itself) in the
vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address
as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777" <[email protected]>
wrote
in
message
Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership
system
using
Excel. I probably should have used Access but I
don't
want to redo the system yet. Everything works
swimmingly
except:

1. I use customer name to name each workbook
(which
contains multiple worksheets of forms and data
pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"),
etc.

I have created reports which have a row for each
customer
and ten or more columns of data for each
customer.
I
get
the info for each column from the customer
workbook
and
worksheet using formulas like the one illustrated
below
(ignore the complexity of the formula; I merely
want to
automatically substitute "Smith" for "Jones"
from a
separate master list of customer names):

Prospective Business Report (Sample Column
Headings):
Name Product PurchaseDate Cost Sell
etc.,
etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress
Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress
Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer
Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress
Record'!
$B$10=" ?","?",IF('[Jones.xls]Buyer
Progress
Record'!$B$10<$AA$2,"! ! ! ! !",'[Jones.xls] Buyer
Progress Record'!$B$10)))))

Everything works well so long as I copy down and
replace
(i.e. replace "Jones" with "Smith") to add a new
customer
row to the report. I cannot find a way to use a
master
list of customer names to automatically
change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact Report by
Salesperson (using the above mentioned master
list
of
customer names) except that there would be
multiple
rows
for each customer. The source, Buyer Progress
Record
worksheet, contains up to 40 lines of contacts in
Rows
28
through 68 and, perhaps, only one or two rows
might
contain data at any moment in time (Row 28 and
Row
29;
the remaining rows will be used as time marches
on).
The
desired report would look like this and only
print
rows
for each customer that contain data:

Contact Report - Salesperson Murphy - Aug 17,
2004

Customer Name "Jones"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Smith"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Green"
Date of Contact Date of Next Contact
Description

3. It would be nice to be able to add to the
master
list
of customer names automatically by having the
computer
look to see if any new customer workbooks have
been
added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter today's date
automatically the first time a worksheet in a
workbook
is
handled but this date is never changed no matter
how
many
times the worksheet is modified?

Thank you in advance for any help you can render.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start
implementing
your code. I have been in the computer industry
since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I
was
product manager of a capital investment evaluation
model
for Fortune 500 companies using Computer Sciences'
time
sharing system. I taught myself how to program in
Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book
multiple
times and, as I did tonight, asked myself why I
should
punish myself when Microsoft's explanation never
seems
to
explain a place to begin.

If it isn't asking too much, could you please tell
me
what steps to take to simply make your code work in
cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do that, I can
get
the concept of how to begin to use VB - certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB
will
be
a major part of your answer. If so, I will need to
know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google search
later today. One quick question: The date appears OK
after I switch to another worksheet and back again,
but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get into the
cell. I've tried saving the workbook and re- opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince




.Hi Julie:
I'm afraid I am asking too much of you - it isn't your
task in life to train me in VB. I scan the book and it
seems to avoid simple tasks. I've tried Google for a
list of commands (for example: goto - or moveto, etc. -
so I can say 'if whatever, goto "a1")' and cannot find
such a list. If you point me in the right direction I'll
go there and study. Having started life as a bookkeeping
machine salesperson I am strong on applications and look
to programming as a list of simple commands to copy into
a program to get my job done.

Having said that, the code you gave me generated "Run-
time error '9': Subscript out of range." Here is the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format(Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above questions
I'll go it alone from that point. But will hope you can
solve the first three questions in my original posting
when you get the time. (I'll do the Google search you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince

Hi Vince

i'm happy to help so don't worry about asking questions ..

have you got a sheet in the workbook called "NewInput" as the code runs fine
when i copy & paste it into a new workbook with a sheet called NewInput (no
spaces) ...
is the code in the THISWORKBOOK object of the project explorer and not in a
module sheet
is the format stuff all on one line?

as to the other 3 questions i will get to them when i get a chance.

Looking forward to hearing from you.

Cheers
JulieD


.Hi Julie

Please forgive my stupidity. While shaving I realized my
worksheet is New_Input; not, NewInput. I made the change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince

Hi Vince

glad its working .. basically the way you need to approach VBA is to ask
yourself what object am i looking at and what do i want to do with it, so if
it's a cell (e.g. A1), then you need to know that cells are part of the
range object and that range objects have a select method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the Object Browser in VBA (view
/ object browser) or type "microsoft excel object model" into Help in the
VBE .. this will give you all the objects you can then click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD

 

[VJ7777]

Hi Julie
For some unknown reason I cannot find your last message
to me or my last message to you. My problem with the
code was using "NewInput" instead of "New Input" which
was the correct name of my worksheet.
Perhaps those messages go somewhere else after so many
entries in a newsgroup regarding the same subject?
Hope this gets to you.
Vince

-----Original Message-----

-----Original Message-----

"VJ7777" <[email protected]> wrote

in
message

-----Original Message-----
Hi Vince

you can also put code against the THISWORKBOOK

object
on

the workbook_open
event (double click on ThisWorkbook in the VBE window,
choose Workbook from
the lhs drop down & Open from the right),
you'll need to edit the code to:

If IsEmpty(Sheets("NewInput").Range("B2")) then
Sheets("NewInput").Range("B2").Value = Format
(Now, "dd mmmm yyyy")
End If

hope this helps
Cheers
JulieD


"VJ7777" <[email protected]>

wrote
in

message

-----Original Message-----
Hi Vince

to place the code in the workbook, right mouse click
on
any sheet tab and
choose view code. This will display the VB Editor ...
on the top left there
should be a little area with folders & sheets & the
name
of your workbook in
bold - if not, choose View / Project Explorer

from
the

menu.

under the project with your workbook name in

bold,
you

will see "NewInput"
and the rest of your sheets - double click on the
NewInput one, and a white
piece of paper should appear on the right hand side of
the screen , up the
top are two drop down boxes, choose
worksheet
from the lhs one and Activate from the right
then the words

Private Sub Worksheet_Activate()
End Sub

should appear .. place my code between these two
lines.
Change the A1 to B2
You can "switch" between the code window & your
workbook
by using Alt & F1l
.... switch back, ensure B2 on the NewInput

sheet
has

nothing in it, switch
to another sheet, come back to the NewInput

sheet
the

date should be filled
in. Now you can test out both bits of code i

gave
you

to decide which one
is the one you want.

(by the way, it is always a good idea to make a backup
testing any code etc)

With regards to your other questions, i'm still
having a
look at them, but
i've got a few other things on at the moment and
havent'
really had time to
sit down & think about them properly. If you haven't
done so already you
might like to do a search of google
(groups.google.com -
advanced search:
search string changing workbook names formulas ...
search groups
microsoft.public.excel* ), i would do it for you and
see
if i come up with
anything but my internet explorer's not working

at
the

moment

Hope this helps
Cheers
JulieD



-----Original Message-----
Hi

can't help (yet) with the other questions but
4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter

today's
date

automatically the first time a worksheet in a
workbook
is
handled but this date is never changed no matter
how
many
times the worksheet is modified?

you can put code against each sheet (itself)

in
the

vbe
window similar to
Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell
address
as
required
Range("A1").Value = Now
End If
End Sub

or

Private Sub Worksheet_Activate()
If IsEmpty(Range("A1")) Then 'change cell address
as
required
Range("A1").Value = Format(Now, "dd mmmm yyyy")
End If
End Sub

Hope this helps
Cheers
JulieD



"VJ7777"

wrote
in
message
[email protected]...
Please forgive me if I don't use proper Newsgroup
protocol. This is my first time.

I have developed a comprehensive dealership
system
using
Excel. I probably should have used Access but I
don't
want to redo the system yet. Everything works
swimmingly
except:

1. I use customer name to name each workbook
(which
contains multiple worksheets of forms and data
pertaining
to that customer):

For example: "Jones," "Smith" (or "Smith.John"),
etc.

I have created reports which have a row for each
customer
and ten or more columns of data for each
customer.
I
get
the info for each column from the customer
workbook
and
worksheet using formulas like the one illustrated
below
(ignore the complexity of the formula; I merely
want to
automatically substitute "Smith" for "Jones"
from a
separate master list of customer names):

Prospective Business Report (Sample Column
Headings):
Name Product PurchaseDate Cost Sell
etc.,
etc.

Example of one of many formulas:

IF('[Jones.xls]Buyer Progress Record'!
$B$12="failed","F",IF('[Jones.xls]Buyer Progress
Record'!
$B$8="Cash Sale",'[Jones.xls]Buyer Progress
Record'!
$B$8,IF('[Jones.xls]Buyer Progress Record'!
$B$11<>" ?","$"&'[Jones.xls]Buyer
Progress
Record'!$B$11,IF('[Jones.xls]Buyer Progress
Record'!
$B$10=" ?","?",IF('[Jones.xls] Buyer
Progress
Record'!

$B$10<$AA$2,"! ! ! ! !",'[Jones.xls]
Buyer

Progress Record'!$B$10)))))

Everything works well so long as I copy

down
and

replace
(i.e. replace "Jones" with "Smith") to add

a
new

customer
row to the report. I cannot find a way to use a
master
list of customer names to automatically
change "Jones"
to "Smith" to "Green," etc.

2. I would like to produce a Contact

Report
by

Salesperson (using the above mentioned master
list
of
customer names) except that there would be
multiple
rows
for each customer. The source, Buyer Progress
Record
worksheet, contains up to 40 lines of contacts in
Rows
28
through 68 and, perhaps, only one or two rows
might
contain data at any moment in time (Row 28 and
Row
29;
the remaining rows will be used as time marches
on).
The
desired report would look like this and only
print
rows
for each customer that contain data:

Contact Report - Salesperson Murphy -

Aug
17,

2004

Customer Name "Jones"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Smith"
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description
Date of Contact Date of Next Contact
Description

Customer Name "Green"
Date of Contact Date of Next Contact
Description

3. It would be nice to be able to add to the
master
list
of customer names automatically by having the
computer
look to see if any new customer workbooks have
been
added
to the "Customers 2004" folder each day.

4. Finally, on another subject, in a cell, the
formula "=Today()" enters today's date in a cell
automatically. Is there a way enter

today's
date

automatically the first time a worksheet in a
workbook
is
handled but this date is never changed no matter
how
many
times the worksheet is modified?

Thank you in advance for any help you can render.



Hi, Julie:
Thank you , very much for your response. I must
confess
complete lack of knowledge on how to start
implementing
your code. I have been in the computer industry
since
the beginning (about th
etimeofEniac.Itaught
myself how to read and modify Fortran code when I
was
product manager of a capital investment evaluation
model
for Fortune 500 companies using Computer Sciences'
time
sharing system. I taught myself how to

program
in

Basic
when I had the first Apple computer and, for fun,
designed my own General Ledger system. On the other
hand, I have pulled out the Visual Basic book
multiple
times and, as I did tonight, asked myself why I
should
punish myself when Microsoft's explanation never
seems
to
explain a place to begin.

If it isn't asking too much, could you please tell
me
what steps to take to simply make your code

work
in

cell "B2" of worksheet "New Input" of
workbook "aaCustomer." Maybe, if you do

that, I
can

get
the concept of how to begin to use VB - certainly I
have
never gotten a clue from the Microsoft VB book.

Your message seemed to indicate that you might have
answers to my other questions - and I suspect VB
will
be
a major part of your answer. If so, I will

need
to

know
how to intergrate them into Excel; your response
regarding to how to implement this will be very
necessary
to understanding your other solutions.
Thanks again,
Vince



.Julie, thanks a million. I'll try the google search
later today. One quick question: The date

appears
OK

after I switch to another worksheet and back again,
but,
suppose one doesn't go to another worksheet in today's
data entry session; how does today's date get

into
the

cell. I've tried saving the workbook and re- opening it
but the cell remains blank unless I switch to another
worksheet and back again
Again, I really appreciate your help!!!
Vince




.Hi Julie:
I'm afraid I am asking too much of you - it isn't your
task in life to train me in VB. I scan the book and it
seems to avoid simple tasks. I've tried Google for a
list of commands (for example: goto - or moveto, etc. -
so I can say 'if whatever, goto "a1")' and cannot find
such a list. If you point me in the right direction I'll
go there and study. Having started life as a bookkeeping
machine salesperson I am strong on applications and look
to programming as a list of simple commands to copy into
a program to get my job done.

Having said that, the code you gave me generated "Run-
time error '9': Subscript out of range." Here is the
code I entered:
Private Sub Workbook_Open()
If IsEmpty(Sheets("NewInput").Range("B2")) Then
Sheets("NewInput").Range("B2").Value = Format (Now, "dd
mmmm yyyy")
End If
End Sub

If you will help one more time with the above questions
I'll go it alone from that point. But will hope you can
solve the first three questions in my original posting
when you get the time. (I'll do the Google search you
recommended before or during the Labor Day weekend.)

Again, thank you very much.
Vince



Hi Vince

i'm happy to help so don't worry about asking questions ..

have you got a sheet in the workbook called "NewInput" as the code runs fine
when i copy & paste it into a new workbook with a

sheet
called NewInput (no

spaces) ...
is the code in the THISWORKBOOK object of the project explorer and not in a
module sheet
is the format stuff all on one line?

as to the other 3 questions i will get to them when i get a chance.

Looking forward to hearing from you.

Cheers
JulieD


.Hi Julie

Please forgive my stupidity. While shaving I realized my
worksheet is New_Input; not, NewInput. I made the change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince

Hi Vince

glad its working .. basically the way you need to approach VBA is to ask
yourself what object am i looking at and what do i want to do with it, so if
it's a cell (e.g. A1), then you need to know that cells are part of the
range object and that range objects have a select method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the Object Browser in VBA (view
/ object browser) or type "microsoft excel object model" into Help in the
VBE .. this will give you all the objects you can then click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD


.

 

[JulieD]

Hi Julie
For some unknown reason I cannot find your last message
to me or my last message to you. My problem with the
code was using "NewInput" instead of "New Input" which
was the correct name of my worksheet.
Perhaps those messages go somewhere else after so many
entries in a newsgroup regarding the same subject?
Hope this gets to you.
Vince

i can still see them, here's my response to your last post:

Please forgive my stupidity. While shaving I realized my
worksheet is New_Input; not, NewInput. I made the change
and it worked fine.

Do you know a source for VB commands such as "goto"?

Thanks again.
Vince

Hi Vince

glad its working .. basically the way you need to approach VBA is to ask
yourself what object am i looking at and what do i want to do with it, so if
it's a cell (e.g. A1), then you need to know that cells are part of the
range object and that range objects have a select method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the Object Browser in VBA (view
/ object browser) or type "microsoft excel object model" into Help in the
VBE .. this will give you all the objects you can then click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD

 

[VJ7777]

-----Original Message-----



i can still see them, here's my response to your last post:

Hi Vince

glad its working .. basically the way you need to approach VBA is to ask
yourself what object am i looking at and what do i want to do with it, so if
it's a cell (e.g. A1), then you need to know that cells are part of the
range object and that range objects have a select method, therefore to
select a cell you can use
Range("A1").select

however, if you want to select a cell on another sheet to need to make the
sheet active & then select the cell
Sheets("Sheet1").activate
Range("A1").select

however, you can populate a cell without selecting it
Sheets("Sheet1").Range("A1").value = 5
will put the number 5 into cell A1 on Sheet1

the best place to find all this out is using the Object Browser in VBA (view
/ object browser) or type "microsoft excel object model" into Help in the
VBE .. this will give you all the objects you can then click on them to find
out their properties & methods.

Hope this helps
Cheers
JulieD



.
Hi Julie

I haven't tried your last advice because I got a pinched
nerve and couldn't sit down for a number of days. I
could use my laptop by standing up at the kitchen counter
which killed my back but I don't have Internet access in
the kitchen. I won't be able to get back to the Excel
project for several more days.
I don't want to lose touch with you. Is there a way to
move our correspondence forward or is there a quick way
to get to what is now page 40 or so? And, is there a
quick way to know when you respond to my questions?
I do appreciate your help.
Vince

 

[JulieD]

Hi Vince

ouch .. .had one of those myself a few years back - not good ...

it might be easier if you email just me direct - julied_ng at hcts dot net
dot au

Cheers
JulieD

 

[VJ7777]

Hi Julie
Color me stupid. I've tried the following e-mail
addresses and they don't work:
(e-mail address removed)
(e-mail address removed)

Where did I go wrong?
Thanks again,
Vince

 

[JulieD]

Hi Vince

both of those should have worked fine ... try again ... if they still don't
work try
hartley at techie dot com

Cheers
JulieD

--snip -- as i would like to limit the number of posts with my email address
on them due to spam