Please help

I am trying to automate a spreadsheet and have come across
the following problems:

1. I want to enter dates and times in one column,
whenever the corresponding entry is altered in another
column. How do I do this Visual Basic?

2. I want to convert data, on entry, in particular cells
from lower case to upper case. I can use validation rules
to force upper case data entry, but this is not the
complete user-friendly solution. How can I 'format' the
cells so that any data entries are automatically converted
to upper case?

3. And finally, I wish to trap the 'Cancel' and 'No'
button returns from the SaveAs dialog box. The SaveAs
command does not appear to return a response code in the
same waythat the GetSaveAsFilename command does. How do I
get round this.

Many thanks for any light you can shed on these problems.

Hello Nigel
1) Right click on worksheet tab, View code and paste and amend accordingly
the following example:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Target.Offset(, 2).Value = Date
End If
End Sub
2)Right click on worksheet tab, View code and paste and amend accordingly
the following example:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Target.Value = Ucase(Target.Value)
End If
End Sub

3) in the following exemplae response will return False if Save As operation
is cancelled
Sub testit()
response = Application.Dialogs(xlDialogSaveAs).Show
MsgBox response
End Sub

HTH
Regards
Pascal



"Nigel Stevens" a écrit dans le
message de ...
Please help

I am trying to automate a spreadsheet and have come across
the following problems:

1. I want to enter dates and times in one column,
whenever the corresponding entry is altered in another
column. How do I do this Visual Basic?

2. I want to convert data, on entry, in particular cells
from lower case to upper case. I can use validation rules
to force upper case data entry, but this is not the
complete user-friendly solution. How can I 'format' the
cells so that any data entries are automatically converted
to upper case?

3. And finally, I wish to trap the 'Cancel' and 'No'
button returns from the SaveAs dialog box. The SaveAs
command does not appear to return a response code in the
same waythat the GetSaveAsFilename command does. How do I
get round this.

Many thanks for any light you can shed on these problems.

Hi
just some minor additions (also combining 1+2 in one
macro):

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
on error goto errhandler
with target
application.enableevents=false
..Offset(, 2).Value = Date
..value=ucase(.value)
end with
End If

errhandler:
application.enableevents=true
End Sub

-----Original Message-----
Hello Nigel
1) Right click on worksheet tab, View code and paste and
amend accordingly
the following example:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Target.Offset(, 2).Value = Date
End If
End Sub
2)Right click on worksheet tab, View code and paste and
amend accordingly
the following example:
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A1:A20")) Is Nothing Then
Target.Value = Ucase(Target.Value)
End If
End Sub

3) in the following exemplae response will return False
if Save As operation
is cancelled
Sub testit()
response = Application.Dialogs(xlDialogSaveAs).Show
MsgBox response
End Sub

HTH
Regards
Pascal



"Nigel Stevens" a
écrit dans le
message de ...
Please help

I am trying to automate a spreadsheet and have come
across
the following problems:

1. I want to enter dates and times in one column,
whenever the corresponding entry is altered in another
column. How do I do this Visual Basic?

2. I want to convert data, on entry, in particular
cells
from lower case to upper case. I can use validation
rules
to force upper case data entry, but this is not the
complete user-friendly solution. How can I 'format' the
cells so that any data entries are automatically
converted
to upper case?

3. And finally, I wish to trap the 'Cancel' and 'No'
button returns from the SaveAs dialog box. The SaveAs
command does not appear to return a response code in the
same waythat the GetSaveAsFilename command does. How
do I
get round this.

Many thanks for any light you can shed on these
problems.


.