Hi

I am a novice with excel at this level so any advise would be great. It may
be people can also suggest a better way to solve my problem than struggling
with excel and i would really appreciate that also.

my task:

I am to produce an easy-to-use spreadsheet to record in-coming messages to
my office. The spreadsheet should be usable over a small network. Ideally
there would be an easy way to archive messages at the end of each day or
possibly week. Most of the people who will be using this spreadsheet will
have minimal expertise with excel.

So far:

I have created a simple - to my mind any way :) - spreadsheet with several
colums and some drop down menu's for common matters - eg, who it is for,
query type etc. and i have conditionally formated some sells to indicate what
stage an enquirey is at.

My problems:

I would like to create macros to do the following:

a) merge the day's (or week's) messages to a single ongoing archive worksheet

b) reset/recreate an empty worksheet that preserves all my original
formatting for the new days messages.

c) have an easy way to populate the date and time as each entry is made.

Sorry if I am asking a lot in one post but i thought it better to just
explain the whole thing and see what suggestions i get. Thanks in advance.

Yuo may want to consider a Form for your entry. the form can have a button
after all the entries are made which will add the time. The button will move
the data from the form to the worksheet.

A templete sheet can be included in the workbook which is hiden that can be
used for the blank worksheet. A Copy of this sheet can be made on a daily
basis. I would include the data and time in each worksheet name so you can
tell when a blank worksheet need to be created.

See this microsoft webpage for more detains.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

"mattesse" wrote:

Hi

I am a novice with excel at this level so any advise would be great. It may
be people can also suggest a better way to solve my problem than struggling
with excel and i would really appreciate that also.

my task:

I am to produce an easy-to-use spreadsheet to record in-coming messages to
my office. The spreadsheet should be usable over a small network. Ideally
there would be an easy way to archive messages at the end of each day or
possibly week. Most of the people who will be using this spreadsheet will
have minimal expertise with excel.

So far:

I have created a simple - to my mind any way :) - spreadsheet with several
colums and some drop down menu's for common matters - eg, who it is for,
query type etc. and i have conditionally formated some sells to indicate what
stage an enquirey is at.

My problems:

I would like to create macros to do the following:

a) merge the day's (or week's) messages to a single ongoing archive worksheet

b) reset/recreate an empty worksheet that preserves all my original
formatting for the new days messages.

c) have an easy way to populate the date and time as each entry is made.

Sorry if I am asking a lot in one post but i thought it better to just
explain the whole thing and see what suggestions i get. Thanks in advance.

thanks!

I think it will be ideal to do it that way.


"Joel" wrote:

Yuo may want to consider a Form for your entry. the form can have a button
after all the entries are made which will add the time. The button will move
the data from the form to the worksheet.

A templete sheet can be included in the workbook which is hiden that can be
used for the blank worksheet. A Copy of this sheet can be made on a daily
basis. I would include the data and time in each worksheet name so you can
tell when a blank worksheet need to be created.

See this microsoft webpage for more detains.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

"mattesse" wrote:

Hi

I am a novice with excel at this level so any advise would be great. It may
be people can also suggest a better way to solve my problem than struggling
with excel and i would really appreciate that also.

my task:

I am to produce an easy-to-use spreadsheet to record in-coming messages to
my office. The spreadsheet should be usable over a small network. Ideally
there would be an easy way to archive messages at the end of each day or
possibly week. Most of the people who will be using this spreadsheet will
have minimal expertise with excel.

So far:

I have created a simple - to my mind any way :) - spreadsheet with several
colums and some drop down menu's for common matters - eg, who it is for,
query type etc. and i have conditionally formated some sells to indicate what
stage an enquirey is at.

My problems:

I would like to create macros to do the following:

a) merge the day's (or week's) messages to a single ongoing archive worksheet

b) reset/recreate an empty worksheet that preserves all my original
formatting for the new days messages.

c) have an easy way to populate the date and time as each entry is made.

Sorry if I am asking a lot in one post but i thought it better to just
explain the whole thing and see what suggestions i get. Thanks in advance.

I have a further question.

I have created a userform that saves data to the worksheet as needed.

ty again!

I have just also added code to auto populate the userform with the current
date and time and to then save this along with the other inputed data to the
worksheet.

The userform appears to be displaying everything fine but when it saves it
to the spreadsheet it reverses the day and month.

i should point out i am in australia and need the format to be dd/mm/yyyy

changing the formating of the cell seems to make no difference as it simply
changes as soon as new data is entered. excel seems to really think the date
has become 10th of march when it should be the 3rd of october.

i use the following code to cenerate the date and time

Private Sub UserForm_Initialize()
txttime.Value = Now
txttime = Format(txttime.Value, "dd mm yyyy hh:mm")
End Sub


and the following to save the data to the spreadsheet.

ws.Cells(iRow, 6).Value = Me.txttime.Value

am i doing something wrong?


"mattesse" wrote:


thanks!

I think it will be ideal to do it that way.


"Joel" wrote:

Yuo may want to consider a Form for your entry. the form can have a button
after all the entries are made which will add the time. The button will move
the data from the form to the worksheet.

A templete sheet can be included in the workbook which is hiden that can be
used for the blank worksheet. A Copy of this sheet can be made on a daily
basis. I would include the data and time in each worksheet name so you can
tell when a blank worksheet need to be created.

See this microsoft webpage for more detains.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

"mattesse" wrote:

Hi

I am a novice with excel at this level so any advise would be great. It may
be people can also suggest a better way to solve my problem than struggling
with excel and i would really appreciate that also.

my task:

I am to produce an easy-to-use spreadsheet to record in-coming messages to
my office. The spreadsheet should be usable over a small network. Ideally
there would be an easy way to archive messages at the end of each day or
possibly week. Most of the people who will be using this spreadsheet will
have minimal expertise with excel.

So far:

I have created a simple - to my mind any way :) - spreadsheet with several
colums and some drop down menu's for common matters - eg, who it is for,
query type etc. and i have conditionally formated some sells to indicate what
stage an enquirey is at.

My problems:

I would like to create macros to do the following:

a) merge the day's (or week's) messages to a single ongoing archive worksheet

b) reset/recreate an empty worksheet that preserves all my original
formatting for the new days messages.

c) have an easy way to populate the date and time as each entry is made.

Sorry if I am asking a lot in one post but i thought it better to just
explain the whole thing and see what suggestions i get. Thanks in advance.

Whe you put the data into the worksheet it is re-interpreting the date date.
The statement

ws.Cells(iRow, 6).Value = Me.txttime.Value

Is n't changing the format of the cell ws.Cells(iRow, 6).Value. You need to
format the cells like this

ws.Cells(iRow, 6).Value = Me.txttime.Value
ws.Cells(iRow, 6).numberformat = "dd mm yyyy hh:mm"

I would also make a slight change to your initialize code. The 2nd line is
really a duplication of the first line.

from
Private Sub UserForm_Initialize()
txttime.Value = Now
txttime = Format(txttime.Value, "dd mm yyyy hh:mm")
End Sub

to
Private Sub UserForm_Initialize()
txttime.Value = Format(Now, "dd mm yyyy hh:mm")
End Sub

Format(0 is just formatting the string and not changing the format of the
cell. Numberformat is the same as going to the worksheet and using the
Format - Cells - Number - Date and specifying the format you want.
"mattesse" wrote:

I have a further question.

I have created a userform that saves data to the worksheet as needed.

ty again!

I have just also added code to auto populate the userform with the current
date and time and to then save this along with the other inputed data to the
worksheet.

The userform appears to be displaying everything fine but when it saves it
to the spreadsheet it reverses the day and month.

i should point out i am in australia and need the format to be dd/mm/yyyy

changing the formating of the cell seems to make no difference as it simply
changes as soon as new data is entered. excel seems to really think the date
has become 10th of march when it should be the 3rd of october.

i use the following code to cenerate the date and time

Private Sub UserForm_Initialize()
txttime.Value = Now
txttime = Format(txttime.Value, "dd mm yyyy hh:mm")
End Sub


and the following to save the data to the spreadsheet.

ws.Cells(iRow, 6).Value = Me.txttime.Value

am i doing something wrong?


"mattesse" wrote:


thanks!

I think it will be ideal to do it that way.


"Joel" wrote:

Yuo may want to consider a Form for your entry. the form can have a button
after all the entries are made which will add the time. The button will move
the data from the form to the worksheet.

A templete sheet can be included in the workbook which is hiden that can be
used for the blank worksheet. A Copy of this sheet can be made on a daily
basis. I would include the data and time in each worksheet name so you can
tell when a blank worksheet need to be created.

See this microsoft webpage for more detains.

http://msdn.microsoft.com/en-us/libr...ffice.11).aspx

"mattesse" wrote:

Hi

I am a novice with excel at this level so any advise would be great. It may
be people can also suggest a better way to solve my problem than struggling
with excel and i would really appreciate that also.

my task:

I am to produce an easy-to-use spreadsheet to record in-coming messages to
my office. The spreadsheet should be usable over a small network. Ideally
there would be an easy way to archive messages at the end of each day or
possibly week. Most of the people who will be using this spreadsheet will
have minimal expertise with excel.

So far:

I have created a simple - to my mind any way :) - spreadsheet with several
colums and some drop down menu's for common matters - eg, who it is for,
query type etc. and i have conditionally formated some sells to indicate what
stage an enquirey is at.

My problems:

I would like to create macros to do the following:

a) merge the day's (or week's) messages to a single ongoing archive worksheet

b) reset/recreate an empty worksheet that preserves all my original
formatting for the new days messages.

c) have an easy way to populate the date and time as each entry is made.

Sorry if I am asking a lot in one post but i thought it better to just
explain the whole thing and see what suggestions i get. Thanks in advance.